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 <(E-Mail Removed)> 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
news(E-Mail Removed)...
> 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 <(E-Mail Removed)> 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\)"
<(E-Mail Removed)> 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
>news(E-Mail Removed)...
>> 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 <(E-Mail Removed)> 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\)"
>
>
>
> <rickNOSPAMn...@NOSPAMcomcast.net> 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
> >news(E-Mail Removed)...
> >> 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 <arwolkow...@shaw.ca> 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 <(E-Mail Removed)> 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

This seems to do the trick without select case.

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
Me.Range("G20:G30").NumberFormat = "[$" & Target.Value & "] #,##0" & _
IIf(Target.Value = "INR", "", ".00")
endit:
Application.EnableEvents = True
End Sub

Thanks for the tip.


Gord

On Sat, 15 Sep 2007 12:07:54 -0400, "Rick Rothstein \(MVP - VB\)"
<(E-Mail Removed)> wrote:

>>> 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

On Sep 15, 11:21 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> This seems to do the trick without select case.
>
> 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
> Me.Range("G20:G30").NumberFormat = "[$" & Target.Value & "] #,##0" & _
> IIf(Target.Value = "INR", "", ".00")
> endit:
> Application.EnableEvents = True
> End Sub
>
> Thanks for the tip.
>
> Gord
>
> On Sat, 15 Sep 2007 12:07:54 -0400, "Rick Rothstein \(MVP - VB\)"
>
>
>
> <rickNOSPAMn...@NOSPAMcomcast.net> wrote:
> >>> 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- Hide quoted text -
>
> - Show quoted text -

Sorry, Gord, I misinterpretted the instructions...........
After pasting in the code I realized that I need the format fine tuned
just a bit more. I hope you don't mind assisting?
I've added a few more currencies to H14. I'd like USD, CAD, IDR, GBP,
EURO and AUD as the choices. When one of these are choosen I want
Cells F20:G38 formatted to 2 decimal places for all but the IDR
currency. IDR currency will be shown without any decimal places. All
of these cells need not display a currency symbol, e.g., $.
When this is accomplished, I'd like Cells G39:G41 formatted the same
as above but in this case I'd like Cell G41 to display the currency
symbol before the value, i.e., $, € or £. In the case of the
Indonesian Rupiah currency, the symbol (Rp.) should be displayed
following the value.
Thanks for helping me out.