Accounting Format Text in a Textbox

[RyanH]

I have a Textbox on a Userform that I want its text to be in an Accounting
Format when data is entered into it. This is an example of what I am trying
to do.

Private Sub Textbox1_AfterUpdate()
Textbox1.Value = Format(Textbox1, Accounting)
End Sub

I also retrieve cell values (that are formated as Accounting) into Textbox1
with my Worksheet Double Click Event. But it does not display the dollar
sign, why and how can I do this?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

'autofills the user form with the data from global schedule worksheet
Cancel = True

With frmItemSummary
.Textbox1 = Cells(Target.Row, "A")
End With

End Sub

 

[Dave Peterson]

Maybe...

Private Sub Textbox1_AfterUpdate()
Dim AccountingFormat As String
AccountingFormat = "$* #,##0.00;$* (#,##0.00)"

Me.TextBox1.Value = Format(Me.TextBox1.Value, AccountingFormat)
End Sub

 

[RyanH]

When I load the cell value into Textbox1 when the userform is called it looks
just like a accounting format, plus if I don't change the text, when I click
my "Apply" button in applies the value to the cell just fine.

The problem happens when I change the Textbox1 value. I can only add a $
right next to the first number instead of it looking like it has a
"Accounting" format.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

frmItemSummary.Textbox1 = Cells(Target.Row, "A")
frmItemSummary.Show

End Sub

Private Sub btnApply_Click()
Sheets("Global Schedule").Cells(ActiveCell.Row, "A") = Textbox1
End Sub
--
Cheers,
Ryan

Maybe...

Private Sub Textbox1_AfterUpdate()
Dim AccountingFormat As String
AccountingFormat = "$* #,##0.00;$* (#,##0.00)"

Me.TextBox1.Value = Format(Me.TextBox1.Value, AccountingFormat)
End Sub

 

[Dave Peterson]

You could just add as many space characters as you want:

Private Sub Textbox1_AfterUpdate()
Dim AccountingFormat As String
Dim HowManyCharacters As Long
Dim myStr As String

HowManyChars = 12
AccountingFormat = "#,##0.00;(#,##0.00)"

myStr = "$" & Right(Space(HowManyChars) _
& Format(Me.TextBox1.Value, AccountingFormat), HowManyChars)

Me.TextBox1.Value = myStr
End Sub

When I load the cell value into Textbox1 when the userform is called it looks
just like a accounting format, plus if I don't change the text, when I click
my "Apply" button in applies the value to the cell just fine.

The problem happens when I change the Textbox1 value. I can only add a $
right next to the first number instead of it looking like it has a
"Accounting" format.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

frmItemSummary.Textbox1 = Cells(Target.Row, "A")
frmItemSummary.Show

End Sub

Private Sub btnApply_Click()
Sheets("Global Schedule").Cells(ActiveCell.Row, "A") = Textbox1
End Sub

 

[RyanH]

I guess that will have to do. Thanks for the help!
--
Cheers,
Ryan

You could just add as many space characters as you want:

Private Sub Textbox1_AfterUpdate()
Dim AccountingFormat As String
Dim HowManyCharacters As Long
Dim myStr As String

HowManyChars = 12
AccountingFormat = "#,##0.00;(#,##0.00)"

myStr = "$" & Right(Space(HowManyChars) _
& Format(Me.TextBox1.Value, AccountingFormat), HowManyChars)

Me.TextBox1.Value = myStr
End Sub