Auto number formatting
 

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.

Auto number formatting
 

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.

Auto number formatting
 

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.

Auto number formatting
 

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.

Auto number formatting
 

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.

Auto number formatting
 

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.

Auto number formatting
 

Probably but OP wants no decimals if the choice is INR

Damn! I missed the "no decimals" part.

Rick

Auto number formatting
 

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

Auto number formatting
 

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\)"
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

Auto number formatting
 

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\)"



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.

Auto number formatting
 

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


On Sat, 15 Sep 2007 18:25:15 -0700, Randy wrote:

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\)"



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.

Auto number formatting
 

On Sep 16, 11:41 pm, Gord Dibben <gorddibbATshawDOTca wrote:
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



On Sat, 15 Sep 2007 18:25:15 -0700, Randy wrote:
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\)"

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.- Hide quoted text -

- Show quoted text -

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

Auto number formatting
 

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

On Sun, 16 Sep 2007 15:25:38 -0700, Randy wrote:

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.

Auto number formatting
 

On Sep 17, 6:13 am, Gord Dibben <gorddibbATshawDOTca wrote:
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



On Sun, 16 Sep 2007 15:25:38 -0700, Randy wrote:
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.- Hide quoted text -

- Show quoted text -

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.

Auto number formatting
 

On Sep 17, 9:10Â*pm, Gord Dibben <gorddibbATshawDOTca wrote:
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.



On Sun, 16 Sep 2007 20:25:20 -0700, Randy wrote:
On Sep 17, 9:19 am, Gord Dibben <gorddibbATshawDOTca wrote:
On Sun, 16 Sep 2007 16:42:42 -0700, Randy wrote:
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.

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

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

1000.00Rp for Rupiah

Thanks for your assistance and your patience.

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

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- Hide quoted text -

- Show quoted text -

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.

Auto number formatting
 

On Sep 17, 9:10Â*pm, Gord Dibben <gorddibbATshawDOTca wrote:
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.



On Sun, 16 Sep 2007 20:25:20 -0700, Randy wrote:
On Sep 17, 9:19 am, Gord Dibben <gorddibbATshawDOTca wrote:
On Sun, 16 Sep 2007 16:42:42 -0700, Randy wrote:
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.

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

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

1000.00Rp for Rupiah

Thanks for your assistance and your patience.

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

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- Hide quoted text -

- Show quoted text -

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

Auto number formatting
 

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

On Tue, 18 Sep 2007 01:34:18 -0000, Randy wrote:

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.