I wonder if anyone knows how to have a column in Excel 2003
automatically formatted to a particular currency when this particular
currency is selected from a drop down list in another cell?

i.e,;

Cell H14 has a drop down list to select USD, Canadian or Indonesian
Rupiah.

Cells G20 to G30 contain the Price column that I want to have
automatically formatted to either 2 decimal places for USD currency or
zero decimal places for the Indonesion Rupiah depending on what is
selected in H14.

Should I use a formula to start a macro or something like that.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

Select Case Target.Value
Case "USD"
Me.Range("G20:G30").NumberFormat = "[$USD] #,##0.00"

Case "INR"
Me.Range("G20:G30").NumberFormat = "[$INR] #,##0"

Case "CAD"
Me.Range("G20:G30").NumberFormat = "[$CAD] #,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View code".

Copy/paste the code into that sheet module.


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 16:23:34 -0700, Randy wrote:

I wonder if anyone knows how to have a column in Excel 2003
automatically formatted to a particular currency when this particular
currency is selected from a drop down list in another cell?

i.e,;

Cell H14 has a drop down list to select USD, Canadian or Indonesian
Rupiah.

Cells G20 to G30 contain the Price column that I want to have
automatically formatted to either 2 decimal places for USD currency or
zero decimal places for the Indonesion Rupiah depending on what is
selected in H14.

Should I use a formula to start a macro or something like that.

I'm guessing, given the values produced by the drop-down list, that you
could replace your entire Select Case block with this single line of code...

Me.Range("G20:G30").NumberFormat = "[$" & Target.Value &"] #,##0.00"

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

Select Case Target.Value
Case "USD"
Me.Range("G20:G30").NumberFormat = "[$USD] #,##0.00"

Case "INR"
Me.Range("G20:G30").NumberFormat = "[$INR] #,##0"

Case "CAD"
Me.Range("G20:G30").NumberFormat = "[$CAD] #,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View code".

Copy/paste the code into that sheet module.


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 16:23:34 -0700, Randy wrote:

I wonder if anyone knows how to have a column in Excel 2003
automatically formatted to a particular currency when this particular
currency is selected from a drop down list in another cell?

i.e,;

Cell H14 has a drop down list to select USD, Canadian or Indonesian
Rupiah.

Cells G20 to G30 contain the Price column that I want to have
automatically formatted to either 2 decimal places for USD currency or
zero decimal places for the Indonesion Rupiah depending on what is
selected in H14.

Should I use a formula to start a macro or something like that.

Probably but OP wants no decimals if the choice is INR

I started with two choices.......USD and INR then realized OP also wanted CAD so
the code just kinda grew.


Gord

On Thu, 13 Sep 2007 22:46:06 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

I'm guessing, given the values produced by the drop-down list, that you
could replace your entire Select Case block with this single line of code...

Me.Range("G20:G30").NumberFormat = "[$" & Target.Value &"] #,##0.00"

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

Select Case Target.Value
Case "USD"
Me.Range("G20:G30").NumberFormat = "[$USD] #,##0.00"

Case "INR"
Me.Range("G20:G30").NumberFormat = "[$INR] #,##0"

Case "CAD"
Me.Range("G20:G30").NumberFormat = "[$CAD] #,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View code".

Copy/paste the code into that sheet module.


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 16:23:34 -0700, Randy wrote:

I wonder if anyone knows how to have a column in Excel 2003
automatically formatted to a particular currency when this particular
currency is selected from a drop down list in another cell?

i.e,;

Cell H14 has a drop down list to select USD, Canadian or Indonesian
Rupiah.

Cells G20 to G30 contain the Price column that I want to have
automatically formatted to either 2 decimal places for USD currency or
zero decimal places for the Indonesion Rupiah depending on what is
selected in H14.

Should I use a formula to start a macro or something like that.

On Sep 14, 10:06 am, Gord Dibben <gorddibbATshawDOTca wrote:
Probably but OP wants no decimals if the choice is INR

I started with two choices.......USD and INR then realized OP also wanted CAD so
the code just kinda grew.

Gord

On Thu, 13 Sep 2007 22:46:06 -0400, "Rick Rothstein \(MVP - VB\)"



wrote:
I'm guessing, given the values produced by the drop-down list, that you
could replace your entire Select Case block with this single line of code...

Me.Range("G20:G30").NumberFormat = "[$" & Target.Value &"] #,##0.00"

Rick

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

Select Case Target.Value
Case "USD"
Me.Range("G20:G30").NumberFormat = "[$USD] #,##0.00"

Case "INR"
Me.Range("G20:G30").NumberFormat = "[$INR] #,##0"

Case "CAD"
Me.Range("G20:G30").NumberFormat = "[$CAD] #,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View code".

Copy/paste the code into that sheet module.

Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 16:23:34 -0700, Randy wrote:

I wonder if anyone knows how to have a column in Excel 2003
automatically formatted to a particular currency when this particular
currency is selected from a drop down list in another cell?

i.e,;

Cell H14 has a drop down list to select USD, Canadian or Indonesian
Rupiah.

Cells G20 to G30 contain the Price column that I want to have
automatically formatted to either 2 decimal places for USD currency or
zero decimal places for the Indonesion Rupiah depending on what is
selected in H14.

Should I use a formula to start a macro or something like that.- Hide quoted text -

- Show quoted text -

Excuse my ignorance, but could you provide a little more simple
description as I am not a programmer. I am merely an Excel user that
thought he knew a lot about Excel and constantly gets reminded how
little he actually does know! Thanks.

What do you need to know?

The code when pasted into the sheet module as directed will change the format of
the cells G20:G30 when you choose an item from your dropdown list in H14.

Your dropdown list will contain USD, CAD, INR(I'm guessing that INR is Indian
Rupiah)

The code does not convert the values from USD to CAD or INR, just changes the
format.

If you want an actual conversion you would need to have a list of conversion
factors to reference as well.


Gord

On Fri, 14 Sep 2007 20:09:10 -0700, Randy wrote:

Excuse my ignorance, but could you provide a little more simple
description as I am not a programmer. I am merely an Excel user that
thought he knew a lot about Excel and constantly gets reminded how
little he actually does know! Thanks.

Probably but OP wants no decimals if the choice is INR

Damn! I missed the "no decimals" part.

Rick

Probably but OP wants no decimals if the choice is INR

Damn! I missed the "no decimals" part.

Of course, we can still save the one-liner replacement for your Select Case
block...

Me.Range("G20:G30").NumberFormat = "[$" & target.Value & "] #,##0" & _
IIf(target.Value = "INR", "", ".00")


Rick