Change currency throughout workbook based on dropdown value

[ctcarroll]

I have tried to find my answer via past posts/answers but must be missing something or continually mis-typing.
I have a 53 worksheet workbook that has 10 sets of 5-worksheet accounts which include tabular data, and charts, including speedometer charts. It is working fine but I want users to be able to set currency symbols. The idea is that they will keep the basic workbook and copy/save it for each new project that sometimes will require non-U.S. currency.

I am currently getting "subscript out of range" error messages on the following macro:

Sub SetCurrency2()

Dim i As Integer
If Sheets("Spending Tracker").Range("N2") = "US" Then
For i = 1 To 53
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "[$$-409] * #,##0.00"
End With
Next i
ElseIf Sheets("Spending Tracker").Range("N2") = "UK" Then
For i = 1 To 53
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "£#,##0.00"
End With
Next i
ElseIf Sheets("Spending Tracker").Range("N2") = "EUR" Then
For i = 1 To 53
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "[$€-2] * #,##0.00"
End With
Next i
Else
For i = 1 To 53
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "¥#,##0.00"
End With
Next i
End If

End Sub

I have referenced the "Spending Tracker" worksheet where the dropdown exists (and which also has currency data) but also tried referring to it as "Sheet2" in case that was the problem. It apparently isn't.

I will appreciate some guidance on addressing this problem.

Thanks,
Charlie Carroll

 

[Legacy 393636]

Hi Charlie Carroll,

Did you have any luck with this? I've searched the forums, but I don't really know VBA so I'm struggling to put it together.

My Goal: To have a cell where you enter/select a currency (USD,EUR,GBP etc.) that then changes all currency cells in a workbook to that currency automatically. I think you're trying to do the same thing here.


Currently I found this on the forum to change cells (A15:C15) to a particular currency format based on the input to 'C12':

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Me.Range("C12")) Is Nothing Then Exit Sub
 On Error GoTo endit
       Application.EnableEvents = False
 With Me.Range("A15:C15")
 Select Case Target.Value
    Case "USD"
        .NumberFormat = "[$$-en-US]* #,##0.00;[Red]-([$$-en-US]* #,##0.00);"
    Case "GBP"
        .NumberFormat = "[$£-en-GB]* #,##0.00;[Red]-([$£-en-GB]* #,##0.00);"
    Case "EUR"
        .NumberFormat = "[$€-x-euro2] * #,##0.00;[Red]-([$€-x-euro2] * #,##0.00);"
     End Select
End With
endit:
     Application.EnableEvents = True
End Sub

Now I am struggling to find VBA code that would allow me to select all cells in the workbook (instead of just A15:C15) if they are currency/accounting formatted, which from my understanding you have above.


Cheers!