Changing currency in excel using a dropdown via VBA-CodePudding

I have some Excel VBA code which I can use a dropdown (F1) to change currency. It changes only one specified cell (H1). I would like the code to change the currency for the whole workbook. I do not know how to tell the code to change the currency for the whole workbook rather than just a cell H1.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Me.Range("F1")) Is Nothing Then Exit Sub
 On Error GoTo endit
       Application.EnableEvents = False
 With Me.Range("H1")
 Select Case Target.Value
    Case "USD"
        .NumberFormat = "$#,##0.00"
    Case "GBP"
        .NumberFormat = "£#,##0.00"
    Case "EUR"
        .NumberFormat = "€#,##0.00"
     End Select
End With
endit:
     Application.EnableEvents = True
 End Sub

CodePudding user response：

You can group all target cells and name them as, say, "CurrencyFormattedCells" then, run the code (adapting it to your scenario):

Dim objCell As Excel.Range

For Each objCell In Range("CurrencyFormattedCells").Cells
    With objCell
        If VBA.IsNumeric(objCell) Then
            Select Case Target.Value
            Case "USD"
                .NumberFormat = "$#,##0.00"
            Case "GBP"
                .NumberFormat = "£#,##0.00"
            Case "EUR"
                .NumberFormat = "€#,##0.00"
            End Select
        End If
    End With
Next

It's worth mention that running the code here, in Brazil, Excel shows the resultant NumberFormat for "$#,##0.00" as R$ #.##0,00 (R$ = Real, Brazil's currency notation)

CodePudding user response：

I adapted the code with named ranges for the sheet and it is working very well just for the sheet (not the workbook) with this code:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Me.Range("F1")) Is Nothing Then Exit Sub
 On Error GoTo endit
       Application.EnableEvents = False
 With Me.Range("CurrencyFormattedCells")
 Select Case Target.Value
    Case "USD"
        .NumberFormat = "$#,##0.00"
    Case "GBP"
        .NumberFormat = "£#,##0.00"
    Case "EUR"
        .NumberFormat = "€#,##0.00"
     End Select
End With
endit:
     Application.EnableEvents = True
 End Sub

If I wanted to use the same code on another tab, how can I change the target (Target, Me.Range("F1")) to look at the other sheet reference cell with my dropdown.