data entry currency as cents if entering 2 numbers

[Nonie]

Hi there,

I want excel to return "$.59" if I type in "59" and "$1.59" if I type in
"159"

Is there a way to do this?

Thanks!

 

[Nonie]

Sweet! Thanks so much - it worked perfectly!

Tools -> Options Edit -> Check fixed decimal places (ensure the number is 2)

 

[Gord Dibben]

Just remember this is a global setting and will affect all workbooks and sheets.

You may want sheet event code which toggles this setting when you activate or
deactivate a particular sheet.

Private Sub Worksheet_Activate()
Application.FixedDecimal = True
End Sub

Private Sub Worksheet_Deactivate()
Application.FixedDecimal = False
End Sub

This is event code and goes into the sheet module.

Right-click on a sheet tab and "View Code"

Copy and paste the code into that module.

If you want the 2 DP on just a range of cells on one sheet you would need a
different event code tailored for a range.


Gord Dibben MS Excel MVP