Format Textbox in a userform as currency

[wtom0412]

Hi All,

I am trying to get a TextBox to display a numeric value formatted as currency.

The textbox (TextBox3) gets its value from a vlookup.

Here is the code I have tried, but I get a "Wrong number of arguments or invalid property assignment" error

Private Sub UserForm_Initialize()

Dim price As Double
price = TextBox3.Value
TextBox3 = Format(price, "Currency")


End Sub

All of the searching (on here and on Google) tell me that this is the way to do it?

I would appreciate any assistance you can give,

Thank you.

 

[SQUIDD]

Hi

Try this.

TextBox3.value = Format(textbox3.value, "Currency")

if you want it to say price in the box try putting this into the userform initialise

TextBox3.value = "price"

dave

 

[wtom0412]

Hi Dave, thanks for your reply.

Unfortunately, I still get the same error. It doesn't seem to like the word "Format"? When it stops, that word is highlighted.

Here is the code again with your amendments...

Dim price As Double
price = TextBox3.Value
TextBox3.Value = Format(TextBox3.Value, "Currency")

I have put this in a UserForm_Initialize module, and in a TextBox3_Change module, and neither work.

Can you please confirm where it should be placed and what type of Sub it should be?

Thanks again,

WT

 

[SQUIDD]

Hi

Ok, the code should be in the userform code.

try putting this into your userform, remove your other code to test this.
whatever you put something into textbox 3 should become currency when you click another textbox

Private Sub UserForm_Initialize()
TextBox3.Value = "price"
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox3.Value = Format(TextBox3.Value, "Currency")
End Sub

or this code will convert it to currency as soon as you move the mouse

Private Sub UserForm_Initialize()
TextBox3.Value = "price"
End Sub
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
TextBox3.Value = Format(TextBox3.Value, "Currency")
End Sub

lots of options, let me know how you get on.

dave

 

[SQUIDD]

Hi Again, i think i misundersttod what you wanted in regards to price,

so this code should do what you want

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim price As Double
price = TextBox3.Value
TextBox3.Value = Format(TextBox3.Value, "Currency")
End Sub

 

[SQUIDD]

Just lookin at your original post.

You say textbox3 gets its value from a vlookup, but where is the code to tell the textbox what value to be?

is price a named range?

If so should it not be textbox3 = price

dave

 

[SQUIDD]

If price is a named range.

your code should be.

Private Sub UserForm_Initialize()
TextBox3.Value = Format(Range("Price"), "currency")
End Sub

when you open the userform, the value of the named range will arrive in textbox3 as currency.

dave

 

[wtom0412]

Hi Dave,

Sorry for the delay in replying, I've been away for a week or so.

I just wanted to thank you for your solutions, they worked well.

Thanks again, the Mr Excel forum is, in my humble opinion, the best resource available on the net for us struggling amateurs.

Cheers

WT

 

[SQUIDD]

No problem, reading back, looks like i confused myself LOL.

Anyway glad it worked, well one of them.

Thanks

Dave