Userform to change currency values in a named range

[benprelf]

I have had to make some changes to another person's workbook (no vba code just cell formatting and shrinking the range of a named group of cells). When the workbook opens up the user has the option to select the currency for the named group "Vals" by a userform. The form has the options USD ($), EUR (€), & GBP(£).

My problem is that when USD is selected it formats the range to "£" rather than "$". All the other options work. As I did not write the original code and can't really contact the author I am confused at what has gone wrong.

Code:

Private Sub OKButton_Click()

ActiveSheet.Unprotect


'If USD is selected


    If OptionUSD Then
    Application.Goto Reference:="Vals"
    Application.CutCopyMode = False
    Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    Application.Goto Reference:="Curr"
    ActiveCell.FormulaR1C1 = "USD"
         
'Format "Phone" as US Phone Numbers
    Range("Phone").Select
    Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####"
    Range("Custno").Select
    End If


'If GBP is selected


    If OptionGBP Then
     Application.Goto Reference:="Vals"
     Application.CutCopyMode = False
     Selection.NumberFormat = _
        "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-"
    Application.Goto Reference:="Curr"
    ActiveCell.FormulaR1C1 = "GBP"
      
'Format "Phone" as text
    Application.Goto Reference:="Phone"
    Selection.NumberFormat = "@"
    Range("Custno").Select
    
    End If


'If EUR is selected


    If OptionEUR Then
     Application.Goto Reference:="Vals"
     Application.CutCopyMode = False
    Selection.NumberFormat = _
        "_([$€-2] * #,##0.00_);_([$€-2] * (#,##0.00);_([$€-2] * ""-""??_);_(@_)"
    Application.Goto Reference:="Curr"
    ActiveCell.FormulaR1C1 = "EUR"
            
'Format "Phone" as text
    Application.Goto Reference:="Phone"
    Selection.NumberFormat = "@"
    Range("Custno").Select
     End If


'Close the Dialog box


Unload UserForm1
ActiveSheet.Protect


End Sub

 

[Andrew Poulsom]

For the US Dollar try the number fomat:

"_-[$$-409]* #,##0.00_-;-[$$-409]* #,##0.00_-;_-[$$-409]* ""-""??_-;_-@_-"

The part in square brackets determines the locale.

 

[benprelf]

That works fine, Thanks!