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

[JMay]

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..

 

[Bob Phillips]

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]

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]

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]

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 sheet other than the Current sheet 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]

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]

On my MonthEndSummary Sheet cell D5:

=Day1!D6 <<< The Target Cell

On My Day1 sheet cell D6

=2321-52.16+78.99

Thanks,

Gotta Rune To Work,
Will check back..

Tks,

Jim

 

[Bob Phillips]

Jim,

I am lost as to the rationale in this code

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

you replace a + with a CR, a - with a CR and -? Why? This changes
=2321-52.16+78.99 to =2321<CR>-52.16<CR>78.99, which you cannot format.

Perhaps replace those lines , and the textbox load line, with

Dim i As Long, iStart As Long, iEnd As Long
iStart = 2
For i = 2 To Len(str4 & " ")
If Mid(str4 & " ", i, 1) = "-" Or _
Mid(str4 & " ", i, 1) = "+" Or _
Mid(str4 & " ", i, 1) = " " Then
str5 = str5 & Format(Mid(str4, iStart, i - iStart),
"#,##0.00;(#,##0.00)") & vbNewLine
iStart = i
End If
Next i
UserForm1.TextBox1.Text = str5

Don't forget that the textbox has to be multiline.

--
HTH

Bob Phillips

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

 

[Guest]

Bob, Thanks for the modified code. I have incorporated it into my code and
the formatting is doing fine particularly if a pass-thru target cell is all
positives (=100+200.12+300.13) - Displaying in the Userform1 as:

100.00
200.12
300.13

But if any of the Pass-thru target cells have a Negative value - there is a
problem..
say like (=11.69+66.47+891.55-50) - It is displaying in the Userform1 as:

11.69
66.47
+891.55-50

any idea how this can be remedied?
I plan on working on over the weekend, but if you have any thought, let me
know.

Tks,
Jim

 

[Guest]

Got It !!!!!!!!!
Just entered an additional line:

Mid(str4 & " ", i, 1) = "-" Or _ << for the negatives !!

Everything seems to be working.. stay tuned...

Thanks a million Bob

 

[Bob Phillips]

I already had that Jim?


--
---
HTH

Bob

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

 

[Guest]

Bob,
Somehow in my copying your code over (and editing slightly) your first line
of the If Mid(str4... showing the "-" << got into my code (line) as "=",
so that is what caused my last response. I have gone back and corrected ("-"
for "=").

Thanks again for your help.

Jim May