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.