Userform to change currency values in a named range

benprelf

New Member
Joined
Aug 28, 2014
Messages
34
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:

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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
For the US Dollar try the number fomat:

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

The part in square brackets determines the locale.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,984
Messages
5,526,062
Members
409,684
Latest member
Nazmul00

This Week's Hot Topics

Top