Currency Number format VBA

[snjpverma]

Private Sub UserForm_Initialize()
Filmprofit.Value = 0  'setting the default value to 0 when form loads
End Sub

Private Sub AddFilmbutton_Click()
ActiveCell.Offset(0, 1).Value = Filmprofit
ActiveCell.Offset(0, 1).NumberFormat = Range("c3").NumberFormat   ' to change the format to Currency
End Sub

I am entering numbers through textbox named Filmprofit.
As shown in below picture, the Profit amounts are reflecting the Green error mark which says "Convert to Number", Ignore error, etc.
I can see that the number format is changed to Currency but the cells are still showing error.
I believe the numbers entered in text-box is getting entered in cells as text; due to this currency format is not working.

Any thoughts and solution on this please?

 

[JonXL]

Can you use XL2BB to show us your sheet, especially the cell C3 from which you're copying the formatting?

 

[Fluff]

Try

ActiveCell.Offset(0, 1).Value = Val(Filmprofit)

 

[snjpverma]

I just came back to the website to notify you guys that i found a solution. and I am so happy to see the quick responses by the members.

I changed this:

ActiveCell.Offset(0, 1).Value = Filmprofit

to this:

ActiveCell.Offset(0, 1).Value = Filmprofit.value

Fluff, your solution works as well. Happy to know 2 different ways of doing it now.
Thanks to both for responding.

 

[Fluff]

You're welcome & thanks for the feedback