Dollar / Euro sign

Y

Yendorian

Using Excel 2003
I have a line in a VBA code because when I enter a '$' sign, the '€' sign
is returned.
(If Target.Value = "$" Then Range("B1").NumberFormat = "$" & "0.00")
which should produce $123.00, for example.
However, each time I run the macro, the '£' and '€' signs work perfectly but
the '$' sign always returns '€' (e.g. €123.00).
Is there a way of replacing the 'NumberFormat =' definition ("$" & "0.00")
maybe by using an ASCII code, or CHR$ or any other method?
Frustration is setting in!
Any help gratefully received
 
F

FloMM2

Yendorian,
If you want the entire worksheet formatted for US dollars try this:
Select the block above the row and to the left of the column.
This will select the whole worksheet.
Select "Format", "Cells...",select currency. "Symbol:" select $.
God Luck,hth :)
 
Y

Yendorian

tHi,
Thanks for that.
Unfortuinately, I do not want the whole sheet converted to '$' format.
The idea is, for example, that if A1 = "£" then B1, C1, D2, E3 will show the
"£" format. But if A1="€" those cells will show the "€" format and if A1 is
"$" the other cells should show the "$" format. But this is not the case -
the "£" and "€" work correctly and it is only the "$" that gives the problem
by showing the "€" format.
 
F

FloMM2

Yendorian,
If A1 is in the format that you desire, then you can select A1, select
Edit,copy.
Select the cells that have the wrong format, select edit, paste special.
Then select "Formats".
hth :)
 
Y

Yendorian

I was hoping to be able to use a macro that would avoid the necessity for
Edit, Copy, Paste Special etc. I think it is more a problem with Microsoft in
that the system replaces the "$" automatically with the "€" sign.
Thanks anyway
 
Y

Yendorian

'Regional and Language Options' to English (United States) I think I've
discovered the problem.
If I set my 'Regional and Language Options' to English (United States) the
keys '£', '€' and '$' produce '£', '€' and '$' which is what I want.
However, if I set the 'Regional and Language Options' to English (United
Kingdom), the same keys produce '£', '€' and '£'.
Then if I set my 'Regional and Language Options' to French (France), these
keys produce '£', '€' and '€'.
So, it seems that when the settings are other than English (United States),
the '$' defaults to that particular setting.
All that is needed is to change the 'Regional and Language Options to
English (United States).
Hope this helps anyone who is having similar difficulties.
 
F

FloMM2

Yendorian,
Let me see if I now understand:
Case 1: If cell A1 format is in British pounds, you would like the cells
B1,C1,D2 & E3 to match this format.
Case 2: If cell A1 format is in dollars (US), you would like the cells
B1,C1,D2 & E3 to match this format.
Case 3 : If cell A1 format is in Euros, you would like the cells B1,C1,D2 &
E3 to match this format.
And you would like it to be a Macro that you run.
 
F

FloMM2

Yendornian,
Here is what I came up with:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/30/2007 by Dennis L. Follis
'

'
Worksheets("Sheet??").Activate
Worksheets("Sheet??").Range("A1").Select

If Selection.NumberFormat = "$#,##0.00" Then

Range("B1:C1").Select
Selection.NumberFormat = "$#,##0.00"
Range("D2").Select
Selection.NumberFormat = "$#,##0.00"
Range("E3").Select
Selection.NumberFormat = "$#,##0.00"
Else
If Selection.NumberFormat = "[$£-809]#,##0.00" Then
Range("B1:C1").Select
Selection.NumberFormat = "[$£-809]#,##0.00"
Range("D2").Select
Selection.NumberFormat = "[$£-809]#,##0.00"
Range("E3").Select
Selection.NumberFormat = "[$£-809]#,##0.00"

Else
If Selection.NumberFormat = "[$€-2] #,##0.00" Then
Range("B1:C1").Select
Selection.NumberFormat = "[$€-2] #,##0.00"
Range("D2").Select
Selection.NumberFormat = "[$€-2] #,##0.00"
Range("E3").Select
End If
End If
End If
End Sub
Copy this from "Sub Macro1()" all the way through "End Sub"
The line of code:"Worksheets("Sheet??").Activate
Worksheets("Sheet??").Range("A1").Select
You will have to replace the "??" with the Sheet number. This is typically 1.
So it will look like: Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range.("A1").Select
HTH

:)
 
Y

Yendorian

Hi FloMM2
Thanks for all the work you have done on this.
I'm in the process of adapting it to my workbook but it looks as if it
should work.

FloMM2 said:
Yendornian,
Here is what I came up with:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/30/2007 by Dennis L. Follis
'

'
Worksheets("Sheet??").Activate
Worksheets("Sheet??").Range("A1").Select

If Selection.NumberFormat = "$#,##0.00" Then

Range("B1:C1").Select
Selection.NumberFormat = "$#,##0.00"
Range("D2").Select
Selection.NumberFormat = "$#,##0.00"
Range("E3").Select
Selection.NumberFormat = "$#,##0.00"
Else
If Selection.NumberFormat = "[$£-809]#,##0.00" Then
Range("B1:C1").Select
Selection.NumberFormat = "[$£-809]#,##0.00"
Range("D2").Select
Selection.NumberFormat = "[$£-809]#,##0.00"
Range("E3").Select
Selection.NumberFormat = "[$£-809]#,##0.00"

Else
If Selection.NumberFormat = "[$€-2] #,##0.00" Then
Range("B1:C1").Select
Selection.NumberFormat = "[$€-2] #,##0.00"
Range("D2").Select
Selection.NumberFormat = "[$€-2] #,##0.00"
Range("E3").Select
End If
End If
End If
End Sub
Copy this from "Sub Macro1()" all the way through "End Sub"
The line of code:"Worksheets("Sheet??").Activate
Worksheets("Sheet??").Range("A1").Select
You will have to replace the "??" with the Sheet number. This is typically 1.
So it will look like: Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range.("A1").Select
HTH

:)

Yendorian said:
'Regional and Language Options' to English (United States) I think I've
discovered the problem.
If I set my 'Regional and Language Options' to English (United States) the
keys '£', '€' and '$' produce '£', '€' and '$' which is what I want.
However, if I set the 'Regional and Language Options' to English (United
Kingdom), the same keys produce '£', '€' and '£'.
Then if I set my 'Regional and Language Options' to French (France), these
keys produce '£', '€' and '€'.
So, it seems that when the settings are other than English (United States),
the '$' defaults to that particular setting.
All that is needed is to change the 'Regional and Language Options to
English (United States).
Hope this helps anyone who is having similar difficulties.
Click to expand...
Click to expand...
 
Top