Re: Formating string as "#,###.00"

On my MonthEndSummary *** cell D5:

=Day1!D6 <<< The Target Cell

On My Day1 *** cell D6

=2321-52.16+78.99

Thanks,

Gotta Rune To Work,
Will check back..

Tks,

Jim



"Bob Phillips" <bob.NGs@xxxxxxxxxxxxx> wrote in message news:#Opus6ETHHA.5068@xxxxxxxxxxxxxxxxxxxx:

What is the formula in the target cell?

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JMay" <jmay@xxxxxxx> wrote in message
news:u7_yh.15819$5q6.13801@xxxxxxxxxxxxxxx
> Sure Bob - Thanks for looking into - here's the full code:
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
> Dim str1 As String
> Dim str2 As String
> Dim str3 As String
> Dim str4 As String
> Dim str5 As String
> Dim str6 As String
> Cancel = True 'This line prevents Excel from Going into the EDIT MODE
> On Error Resume Next
> If Not Target.HasFormula Then
> MsgBox "Amt represents a single-cell", vbQuestion
> Exit Sub 'If the Target Cell does not have a formula Exit Macro
> End If
> If Target.Count > 1 Then Exit Sub 'If the target cell count is greater
> than 1 Exit Sub
> str1 = Right(Target.Formula, Len(Target.Formula) - 1) 'Captures the
> Formula without the initial "=" sign
> If InStr(str1, "!") = 0 Then Exit Sub 'If the formula DOES NOT refer
> to a *** other than the Current *** Exit Sub
> str2 = Left(str1, WorksheetFunction.Find("!", str1) - 1) 'Capture the
> SheetName Only
> str3 = Mid(str1, WorksheetFunction.Find("!", str1) + 1, 8) 'Capture the
> Cell reference Only
> str4 = Sheets(str2).Range(str3).Formula 'Capture the formula in the
> SheetName.Cellreference
> str5 = WorksheetFunction.Substitute(Right(str4, Len(str4) - 1), "+", ""
> & vbCr & "") 'If you find a + then
>
> 'drop what follows it down a row
> str6 = WorksheetFunction.Substitute(str5, "-", "" & vbCr & "" & "-") 'If
> you find a - then drop what follows it
>
> 'down a row and then appends a - in the first position
> UserForm1.TextBox1.Text = Format(str6, "#,##0.00;(#,##0.00)") '<< Latest
> chg
> UserForm1.Show vbModeless
> End Sub
>
> Does this help?
> Jim
>
>
> "Bob Phillips" <bob.NGs@xxxxxxxx> wrote in message
> news:OCzZiSETHHA.4076@xxxxxxxxxxxxxxxxxxxx:
>
> > Jim,
> >
> > Your original post was a bit confusing as you said
> >
> > ... Where str6 = "22(vbcr)33.18(vbcr)-125(vbcr)1191.56"
> >
> > and then said
> >
> > ... Which shows up (in Userform) as: 22 ...
> >
> > so I assumed there was some string manipulation going on to extract 22
from
> > str6. If you pass that original str6 value to the textbox, of course it
> > won't get formatted, it is not a number.
> >
> > Can you clarify a bit?
> >
> > --
> > ---
> > HTH
> >
> > Bob
> >
> > (there's no email, no snail mail, but somewhere should be gmail in my
addy)
> >
> >
> >
> > "JMay" <jmay@xxxxxxx> wrote in message
> > news:k5Yyh.15804$5q6.11273@xxxxxxxxxxxxxxx
> > > Now with:
> > >
> > > UserForm1.TextBox1.Text = Format(str6, "#,##0.00;(#,##0.00)")
> > > UserForm1.Show vbModeless
> > >
> > > I'm seeing "No Change" from before. hummm
> > > (No part is affected, from beginning to end of string)
> > >
> > > Jim
> > >
> > > "Bob Phillips" <bob.NGs@xxxxxxxxxxxxx> wrote in message
> > > news:uqHyDEDTHHA.996@xxxxxxxxxxxxxxxxxxxx:
> > >
> > >> UserForm1.TextBox1.Text = Format(str6,"#,##0.00;(#,##0.00)")
> > >> UserForm1.Show vbModeless
> > >>
> > >> --
> > >> HTH
> > >>
> > >> Bob Phillips
> > >>
> > >> (there's no email, no snail mail, but somewhere should be gmail in my
> > >> addy)
> > >>
> > >> "JMay" <jmay@xxxxxxx> wrote in message
> > >> news:iyXyh.15767$5q6.8749@xxxxxxxxxxxxxxx
> > >> > Presently in my code I have:
> > >> >
> > >> > UserForm1.TextBox1.Text = str6
> > >> > UserForm1.Show vbModeless
> > >> >
> > >> > Where str6 = "22(vbcr)33.18(vbcr)-125(vbcr)1191.56"
> > >> >
> > >> > Which shows up (in Userform) as:
> > >> >
> > >> > 22
> > >> > 33.18
> > >> > -125
> > >> > 1191.56
> > >> >
> > >> > What can I further do to format somewhere(?) to get (in the
userform)
> > >> > as:
> > >> >
> > >> > 22.00
> > >> > 33.18
> > >> > (125.00)
> > >> > 1,191.56
> > >> >
> > >> > ??
> > >> >
> > >> > Thanks in advance..
> > >> >
> > >> >
> > >
>

.

Loading