Excel VBA displaying currency with Afterupdate and Change events

Question

I created a userform data base where I can both add new data and search for current project details.

There are certain textboxes where I would like for the following:

1. When adding data, for amount to display as currency.

     I already used the Afterupdate() event and it works well,
   
       Private Sub txtPOAmount_Afterupdate()
           txtPOAmount.Value = Format(txtPOAmount.Value, "$#,###.##")
       End Sub
   

2. I would also like it to display currency when it pulls the data

    I used the Change() event, which also does the job
   
   
      Private Sub txtPOAmount_Change()
          txtPOAmount.Value = Format(txtPOAmount.Value, "$#,##0.00")
      End Sub
   

Current issues with using one or the other:

-When I use the Afterupdate() event if I were to pull data already in the data base it doesn't show the currency unless I update it.

-When I use the Change() event only, it displays the data as currency, but when I want to update the data only the fist number typed in works. (ex. Type 5337, it displays $5.00)

However, I would like for both of this options to work simultaneously. If I want to pull data then I would like the userform to display currency, and if I update it, I would like for it to enter the complete amount.

Answer 1

Try using TextBox1_Exit to update it instead of the Change() event, that means you get freedom to edit more than one character in the textbox string before the event gets fired and stops you having to use arrow keys to move left and right in your "currency" textbox:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Value = Format(TextBox1.Value, "£#,##0.00")
End Sub