Auto number formatting

R

Randy

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

Gord Dibben

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
 
R

Rick Rothstein \(MVP - VB\)

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

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.
Click to expand...
Click to expand...
 
G

Gord Dibben

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

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

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.
Click to expand...
Click to expand...
Click to expand...
 
R

Randy

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

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...
Click to expand...
Me.Range("G20:G30").NumberFormat = "[$" & Target.Value &"] #,##0.00"
Rick
Click to expand...

Gord Dibben said:
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
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

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

Gord Dibben

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
 
R

Rick Rothstein \(MVP - VB\)

Probably but OP wants no decimals if the choice is INR

Damn! I missed the "no decimals" part.

Rick
 
R

Rick Rothstein \(MVP - VB\)

Probably but OP wants no decimals if the choice is INR
Damn! I missed the "no decimals" part.
Click to expand...

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
 
G

Gord Dibben

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

Damn! I missed the "no decimals" part.
Click to expand...

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
Click to expand...
 
R

Randy

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

Of course, we can still save the one-liner replacement for your Select Case
block...
Click to expand...
Me.Range("G20:G30").NumberFormat = "[$" & target.Value & "] #,##0" & _
IIf(target.Value = "INR", "", ".00")
Click to expand...
Rick- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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

Gord Dibben

For the first range F20:G38 change the INR to IDR in the code posted below.

Also change the G20:G30 range to Me.Range("F20:G38, G39:G40")

No currency symbol will be displayed.

For the range G41, not so easy. Do you want $100.00 , €100.00, £100.00 and
100Rp?

I think you will have to go back to a Select Case code as per my first posting
and make a case for each format as USD, AUD etc. are selected from the DV
dropdown.

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("F20:G38, G39:G40").NumberFormat = "[$" & Target.Value & "] #,##0" & _
IIf(Target.Value = "IDR", "", ".00")
Select Case Target.Value
Case "USD", "AUD", "CAD"
Me.Range("G41").NumberFormat = "$#,##0.00"
Case "IDR"
Me.Range("G41").NumberFormat = "#,##0Rp"
Case "GBP"
Me.Range("G41").NumberFormat = "£#,##0.00"
Case "EURO"
Me.Range("G41").NumberFormat = "€#,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub


Gord


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

Probably but OP wants no decimals if the choice is INR
Click to expand...
Damn! I missed the "no decimals" part.
Click to expand...
Of course, we can still save the one-liner replacement for your Select Case
block...
Click to expand...
Me.Range("G20:G30").NumberFormat = "[$" & target.Value & "] #,##0" & _
IIf(target.Value = "INR", "", ".00")
Click to expand...
Rick- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
Click to expand...
 
R

Randy

For the first range F20:G38 change the INR to IDR in the code posted below.

Also change the G20:G30 range to Me.Range("F20:G38, G39:G40")

No currency symbol will be displayed.

For the range G41, not so easy. Do you want $100.00 , €100.00, £100.00 and
100Rp?

I think you will have to go back to a Select Case code as per my first posting
and make a case for each format as USD, AUD etc. are selected from the DV
dropdown.

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("F20:G38, G39:G40").NumberFormat = "[$" & Target.Value & "] #,##0" & _
IIf(Target.Value = "IDR", "", ".00")
Select Case Target.Value
Case "USD", "AUD", "CAD"
Me.Range("G41").NumberFormat = "$#,##0.00"
Case "IDR"
Me.Range("G41").NumberFormat = "#,##0Rp"
Case "GBP"
Me.Range("G41").NumberFormat = "£#,##0.00"
Case "EURO"
Me.Range("G41").NumberFormat = "€#,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub

Gord



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\)"
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 -
Click to expand...
Click to expand...
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.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Gord, I pasted in the code and made a few changes........ decided to
use full words rather than the acronyms. I had everything in Cells
F20:G40 working fine but then when I plugged in the remainder of the
code for G41 it all quit working! Now it seems like whatever I select
for currency, the number formatting is always to the second decimal
place and G41 gets no currency symbol of any kind or decimal place
formatting. I likely screwed something up. Could you please have a
look at what I've altered? Thanks.

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 "U.S. Dollar"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00"

Case "Indonesian Rupiah"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0"

Case "Canadian Dollar"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00"

Case "British Pound"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00"

Case "European Euro"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00"

Case "Australian Dollar"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00"

If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Me.Range("F20:G38, G39:G40").NumberFormat = "[$" & Target.Value & "]
#,##0" & _
IIf(Target.Value = "Indonesian Rupiah", "", ".
00")

Select Case Target.Value
Case "U.S. Dollar", "Australian Dollar", "Canadian Dollar"
Me.Range("G41").NumberFormat = "$#,##0.00"
Case "Indonesian Rupiah"
Me.Range("G41").NumberFormat = "#,##0 Rp"
Case "British Pound"
Me.Range("G41").NumberFormat = "£#,##0.00"
Case "European Euro"
Me.Range("G41").NumberFormat = "€#,##0.00"
End Select
endit:
Application.EnableEvents = True
End Sub
 
G

Gord Dibben

Yes, the code is messed up royally.

I'm having a hard time keeping up with your ever-changing formatting and
requirements.

For starters..............

You have a DV list in H14 with these items. Right?

Australian Dollar
U.S. Dollar
Canadian Dollar
European Euro
Indonesian Rupiah
British Pound

What do you want to see in F20:G38 and G39:40 if you pick say U.S. Dollar from
the list? How about European Euro?

What do you want to see in G41 with a pick?


Gord
 
R

Randy

Yes, the code is messed up royally.

I'm having a hard time keeping up with your ever-changing formatting and
requirements.

For starters..............

You have a DV list in H14 with these items. Right?

Australian Dollar
U.S. Dollar
Canadian Dollar
European Euro
Indonesian Rupiah
British Pound

What do you want to see in F20:G38 and G39:40 if you pick say U.S. Dollar from
the list? How about European Euro?

What do you want to see in G41 with a pick?

Gord





- Show quoted text -
Click to expand...

Sorry about the ever changing format. I promise not to make anymore
changes!
H14 has the correct choices.
In Cells F20:G38 and G39:G40 I only want a number that is formatted to
the appropriate decimal place for the choosen currency. I do not want
to display any currency symbols in these cells.
In Cell G41 I would like the number to be formatted according to the
currency selected but this time with a currency symbol either before
or after the number as in the case of Rupiah.
Thanks for your assistance and your patience.
 
G

Gord Dibben

Sorry about the ever changing format. I promise not to make anymore
changes!
H14 has the correct choices.
In Cells F20:G38 and G39:G40 I only want a number that is formatted to
the appropriate decimal place for the choosen currency. I do not want
to display any currency symbols in these cells.
Click to expand...

1,000.00 for dollars, pounds and euros. 1000 for Rupiah
In Cell G41 I would like the number to be formatted according to the
currency selected but this time with a currency symbol either before
Click to expand...

$1,000.00, £1,000.00 and €1,000.00 dor dollars, pounds and euros.
or after the number as in the case of Rupiah.
Click to expand...

1000.00Rp for Rupiah
Thanks for your assistance and your patience.
Click to expand...

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("F20:G38, G39:G40").NumberFormat = "#,##0" & _
IIf(Target.Value = "Indonesian Rupiah", "", ".00")

Select Case Target.Value
Case "U.S. Dollar", "Australian Dollar", "Canadian Dollar"
Me.Range("G41").NumberFormat = "$#,##0.00"
Case "Indonesian Rupiah"
Me.Range("G41").NumberFormat = "#,##0.00Rp"
Case "British Pound"
Me.Range("G41").NumberFormat = "£#,##0.00"
Case "European Euro"
Me.Range("G41").NumberFormat = "€#,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub


Gord
 
R

Randy

1,000.00 for dollars, pounds and euros. 1000 for Rupiah


$1,000.00, £1,000.00 and €1,000.00 dor dollars, pounds and euros.


1000.00Rp for Rupiah


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("F20:G38, G39:G40").NumberFormat = "#,##0" & _
IIf(Target.Value = "Indonesian Rupiah", "", ".00")

Select Case Target.Value
Case "U.S. Dollar", "Australian Dollar", "Canadian Dollar"
Me.Range("G41").NumberFormat = "$#,##0.00"
Case "Indonesian Rupiah"
Me.Range("G41").NumberFormat = "#,##0.00Rp"
Case "British Pound"
Me.Range("G41").NumberFormat = "£#,##0.00"
Case "European Euro"
Me.Range("G41").NumberFormat = "€#,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub

Gord
Click to expand...

I input the code but it still doesn't work.
Cell G41 stays with the number formatted to zero decimal places on all
selections and no symbol is ever displayed, no matter if I choose
dollars or any other currency.
All the other cells in question stay formatted to two decimal places,
no matter what the currency.
When I make a currency selection a Visual Basic message pops up
saying, "Compile Error: Select Case without End Select" and the "End
Sub" at the end of the code gets highlighted.

I understand if you are losing patience and choose not to reply.
Thanks.

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 "U.S. Dollar"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00"

Case "Indonesian Rupiah"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0"

Case "Canadian Dollar"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00"

Case "British Pound"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00"

Case "European Euro"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00"

Case "Australian Dollar"
Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00"


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

Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0" & _
IIf(Target.Value = "Indonesian Rupiah", "", ".
00")


Select Case Target.Value
Case "U.S. Dollar", "Australian Dollar", "Canadian Dollar"
Me.Range("G41").NumberFormat = "$#,##0.00"
Case "Indonesian Rupiah"
Me.Range("G41").NumberFormat = "#,##0.00Rp"
Case "British Pound"
Me.Range("G41").NumberFormat = "£#,##0.00"
Case "European Euro"
Me.Range("G41").NumberFormat = "€#,##0.00"


End Select
endit:
Application.EnableEvents = True
End Sub
 
G

Gord Dibben

Did you try the code I last posted? Worked for me.

The code you just posted will not work and why are you attempting to use it?


Gord, who is off for two days. Back Wednesday.
 
R

Randy

Did you try the code I last posted?  Worked for me.

The code you just posted will not work and why are you attempting to use it?

Gord,  who is off for two days.  Back Wednesday.


















- Show quoted text -
Click to expand...

The reason I posted a question to this group was to get some
assistance in solving a problem. If I was able to write the code
myself, I would have done so. I posted the code in the last message so
that someone could see what I have for code and perhaps correct it for
me. If you are unable to assist, can you please direct me to another
group site............. perhaps I'm dealing with a group that is only
for professionals???????? Thanks for helping me get within grasp of
the final solution.
 
R

Randy

Did you try the code I last posted?  Worked for me.

The code you just posted will not work and why are you attempting to use it?

Gord,  who is off for two days.  Back Wednesday.


















- Show quoted text -
Click to expand...

........ and yes, I did try the code but it didn't work. The results of
trying the code were described in my second last post.
 
G

Gord Dibben

I did correct your code.

Delete the code you currently have and paste this code into you sheet module.

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("F20:G38, G39:G40").NumberFormat = "[$" & Target.Value & "] #,##0" & _
IIf(Target.Value = "Indonesian Rupiah", "", ".00")

Select Case Target.Value
Case "U.S. Dollar", "Australian Dollar", "Canadian Dollar"
Me.Range("G41").NumberFormat = "$#,##0.00"
Case "Indonesian Rupiah"
Me.Range("G41").NumberFormat = "#,##0 Rp"
Case "British Pound"
Me.Range("G41").NumberFormat = "£#,##0.00"
Case "European Euro"
Me.Range("G41").NumberFormat = "€#,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub

Or send me your workbook by email and I'll see what I can do.

Change the AT and DOT to get my address.

Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

auto number formatting 0
How to make repeating drop-down lists? 2
MS Project Center-Autolink column fields 0
Currency conditional formatting 0
Drop down list control value in vba 2
Matching Data between 2 Tabs on Same Workbook - then calculating 2
Selecting items from list and attaching ratios to selection 0
Cell Selection Control 2

Top