Currency Number format VBA

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Private Sub UserForm_Initialize()
Filmprofit.Value = 0  'setting the default value to 0 when form loads
End Sub

VBA Code:
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.

1583006296717.png


Any thoughts and solution on this please?
 

Attachments

  • 1583005822082.png
    1583005822082.png
    4.6 KB · Views: 2

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

JonXL

Active Member
Joined
Feb 5, 2018
Messages
439
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Can you use XL2BB to show us your sheet, especially the cell C3 from which you're copying the formatting?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
ActiveCell.Offset(0, 1).Value = Val(Filmprofit)
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
ActiveCell.Offset(0, 1).Value = Filmprofit

to this:
Rich (BB code):
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,089
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top