Currency format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Christina - 12 Jun 2005 15:09 GMT

Hi, I am trying to format cells such that when I input dollar amount it will
automatically insert a period. For example to input $15.45 I type 15 then
period then 45. I would like to know what to do so when I type 1545 it shows
up as $15.45 automatically.

Thanks for your help - Christina

Reply to this Message

Niek Otten - 12 Jun 2005 15:17 GMT

Hi Christina,

Tools>Options>Edit tab, check Fixed decimal and enter 2 into Places

Signature

Kind Regards,

Niek Otten

Microsoft MVP - Excel

> Hi, I am trying to format cells such that when I input dollar amount it
> will automatically insert a period. For example to input $15.45 I type 15
> then period then 45. I would like to know what to do so when I type 1545
> it shows up as $15.45 automatically.
>
> Thanks for your help - Christina

Reply to this Message

JE McGimpsey - 12 Jun 2005 15:17 GMT

Tools/Options/Edit, check the Fixed Decimals checkbox, and ensure that 2
is in the textbox.

Note that this is a global setting.

> Hi, I am trying to format cells such that when I input dollar amount it will
> automatically insert a period. For example to input $15.45 I type 15 then
> period then 45. I would like to know what to do so when I type 1545 it shows
> up as $15.45 automatically.
>
> Thanks for your help - Christina

Reply to this Message

Gord Dibben - 13 Jun 2005 19:34 GMT

Christina

If you just want the two decimal places for a range on one worksheet you can
use Worksheet event code........

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If .Value <> "" Then
.Value = .Value / 100
.NumberFormat = "$#,##0.00"
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Select your worksheet tab and "View Code". Copy/paste into that module.

Enter 1234 in A1 and see $12.34 returned.

If your range is not contiguous you can change the Me.Range like this

("A5,C1,C5,F1,F8,I6,H3,H13,D13,C15,F18,F13")

Gord Dibben Excel MVP

>Tools/Options/Edit, check the Fixed Decimals checkbox, and ensure that 2
>is in the textbox.
[quoted text clipped - 7 lines]
>>
>> Thanks for your help - Christina

Reply to this Message