Formatting number to currency in a UserForm

[Jo Helge Rorvik]

Hi!
I'm new in this world, but have programmed for a while
The problem is that I want to format my TextBox such that it work for currency.
I've Tried this:

Private Sub UserForm_Activate()
Me.tb_Amount = Format(Me.tb_Amount, "$ #,##0.00")
End Sub

Private Sub tb_Amount_AfterUpdate()
If IsNumeric(Me.tb_Amount) Then
Me.tb_Amount = Format(Me.tb_Amount, "$ #,##0.00")
Else: Me.tb_Amount = ""
End If
End Sub

-The first I wonder is do I have to add something in the textbox to get .00 'The last 2 digits??
in UserForm_Activate
-The second AfterUpdate, the cursor jumps to the end of field, which means that I have to
use the arrows to get back to my focus point. Is there a way to select the position to cursor??
Do you get what I mean?
Thanks a lot for a great site..
Jo-Helge

 

[Micron]

Consider applying the format to the exit event, because AFAIK, the control doesn't have a text format property (in Access it does, so that's really dumb) but the contents do. It's like you're trying to format nothing. Be aware that Format function coerces values to string data type, so that may come into play. Maybe simpler to just format the cells where your values are going to be put?

 

[Jo Helge Rorvik]

Consider applying the format to the exit event, because AFAIK, the control doesn't have a text format property (in Access it does, so that's really dumb) but the contents do. It's like you're trying to format nothing. Be aware that Format function coerces values to string data type, so that may come into play. Maybe simpler to just format the cells where your values are going to be put?

Hello!
And thank you for your response!
I'm sorry for late answer, but I left the office for the evening.
Well in my worksheet I use currency, and I thought it was nice to have it like that in my UserForm as well. First I just wanted to get the right format in the UserForm, but then I realized at copy back to worksheet it became a text, not a digit format. In addititon it happens only the first time, later on it works fine.
For simplisity and better understanding, I have a workbook with a simple accounting, one for income and one for outgoing. Instead of the user could run around and add content in the worksheet, I made it protected. Use a button to open my UserForm and then at Add I fill in the fields in a row. To get the Formatting correctly, I have a 'blank' row with correct formatting, and this is copied and inserted at next row. This make sure that every new addition have the same format, but you do not have to format 100 rows downward just in case... The next row will always be empty but the last row (summary row) will be updated and includes all new added rows without bothering how many.
Dependent of what kind of incoming I store it in different Columns, one for each account
PS! Read back from tb_Amount is not complete due to converting between string and currency
If Not IsEmpty(Me.tb_Description) Then
If IsDate(Me.tb_Date) And _
IsNumeric(Me.tb_Annex) And _
Not IsEmpty(Me.cb_Account) And _
IsNumeric(Me.tb_Amount) Then
With Sheet2
lastrow = Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Rows(lastrow).EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Cells(lastrow, 1).Value = Me.tb_Date
.Cells(sisterad, 2).Value = Me.tb_Annex
.Cells(sisterad, 3).Value = Me.tb_Description
index = Me.cb_Account.ListIndex
amount =
.Cells(lastrow, 4 + indeks).Value = Me.tb_Amount
End With
Sheet1.Range("D1").Value = _
Int(Sheet1.Range("D1").Value + 1)
End If
End If
Unload Me

The currency, maybe it's only a small detail that doesen't have big impact on the total picture. I thought that there might be some fancy way to solve the problem