ActiveX Input Format as currency with 2dp

[JonReyno]

Hi All,

I have been trawling the web and Mr Excel for an answer, but everything I have tried doesn't work. I am creating a userform that users need to be able to add in monetary values. Below is how I have the textbox set up at the moment, but it's not doing what I want:

Private Sub TextBox18_Change()
    TextBox18.Value = Format(TextBox18.Value, "###,##")
End Sub

If I wanted to enter the value 1,234.56 into the cell I can't. It will give me 1,234 but doesn't all the .56. So I have tried different variations on it, commonly "###,##0.00" but that keep giving me 1.00 as a result.

Does anyone have a stroke of genius that will help me out? I don't mind if the filed has £ at the front or not. Currently I have that outside the box, but it can be part of the solution if needs be.

 

[Fluff]

Try putting it in the exit event rather than a change event

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

 

[JonReyno]

Try putting it in the exit event rather than a change event

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

That's worked for the 2dp, but I seem to have lost the auto "," in the thousands which I'd like to maintain as it's clearer to read for values like 100000. I thought the problem was with my format not the sub itself. I didn't think to alter that!

 

[Fluff]

This works for me

Private Sub TextBox18_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Me.TextBox18.Value = Format(Me.TextBox18.Value, "#,##0.00")
End Sub

 

[JonReyno]

This works for me

Private Sub TextBox18_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Me.TextBox18.Value = Format(Me.TextBox18.Value, "#,##0.00")
End Sub

That's very strange, it still doesn't put the comma back in. I have copied your script and pasted it in, so not sure why it wont format it in the same way.

 

[Fluff]

Have you removed the change event code?

 

[JonReyno]

Have you removed the change event code?

Yeah, I literally copy and pasted what you have there. Does it need to go into the worksheet code (which is where I have it) or in a workbook Module? I also have a few textboxes which are all separate Subs. Would that cause a problem?

 

[Fluff]

It needs to go in the UserForm module

 

[JonReyno]

Ah, I don't know I have called what I am doing the right thing! All I have done is entered a load of ActiveX textboxes into the worksheet in a form like fashion. It's not based on the UserForm that's under VBAProject. Does that make a difference?

 

[Fluff]

Yes that does make a difference, as ActiveX textboxes do not have an Exit event.
I don't use controls on sheets, so not sure how to do it I'm afraid.