Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
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.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
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" wrote in message ... 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.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
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" wrote in message : 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" wrote in message ... 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.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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.. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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.. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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.. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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.. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
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<CR78.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) "JMay" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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.. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
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 "Bob Phillips" wrote: 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<CR78.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) "JMay" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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.. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
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" wrote: 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<CR78.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) "JMay" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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.. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
I already had that Jim?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JMay" wrote in message ... 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" wrote: 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<CR78.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) "JMay" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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.. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formating string as "#,###.00"
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 "Bob Phillips" wrote: I already had that Jim? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JMay" wrote in message ... 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" wrote: 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<CR78.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) "JMay" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(C4,"~?#","") will this work to remove multiple string | Excel Worksheet Functions | |||
Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
Converting string "061123" into a date "23/11/06" | Excel Programming | |||
Making "examp le" become "examp_le" in a string | Excel Programming |