VBA maco to change currency is formattign as number?/

garwil · Aug 8th 2021

Hi ALL,

i found this code that changes all cells formatted as currency in a workbook to what is in a select list value (G4).I am experiencing the problem that when the select list changes from one value to the next there is activity but the cells that are supposed to be changed form say R1234.00 to

£1234.00 are not changing. When I right click on a cell that is supposed to have been changed I see they are now formatted as Number?

This is the VBA I am using in Excel 2016.

I hope someone can help me here as searched and searched but getting no-where...

Code

Private Sub Worksheet_Change(ByVal Target As Range)
     Dim objStyle As Style
     If Not Intersect(Target, Range("G4")) Is Nothing Then
     Set objStyle = ThisWorkbook.Styles("Currency")
     Select Case Range("G4").Value
     Case "USD"
     objStyle.NumberFormat = "[$$-409]#,##0_ ;-[$$-409]#,##0"
     Case "EUR"
     objStyle.NumberFormat = "[$€-2] #,##0;-[$€-2] #,##0"
     Case "R"
     objStyle.NumberFormat = "[$R-2] #,##0;-[$R-2] #,##0"
     Case "GBP"
     objStyle.NumberFormat = "[$£-2] #,##0;-[$£-2] #,##0"
     End Select
     End If
    End Sub

Display More

Mumps · Aug 8th 2021

Quote

the cells that are supposed to be changed

What are the cells that are supposed to change format?

garwil · Aug 9th 2021

Hi Mumps ,

Thank you for your reply. I am attempting to change all the cells in the whole workbook that are formatted as Currency.

I used the code I posted in the workbook that the coder supplied and it works fine however when I place the same code in my own workbook it formats the cells as Number.

I have attached two workbooks. One is called FARES.xlsb which are extracts of my whole workbook which is 28mb. The other is called ChangeStyle.xlsb which is the demo workbook in which the code works fine.

Many thanks for taking the time to help, we newbies appreciate it...

Mumps · Aug 9th 2021

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in E7.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "E7" Then Exit Sub
    Application.ScreenUpdating = False
    Select Case Range("E7").Value2
        Case "R"
            With Range("A1", Range("A" & Rows.Count).End(xlUp))
                .Formula = "=RandBetween(5,50000)"
                .NumberFormat = "[$R-1C09]#,##0.00"
            End With
        Case "USD"
            With Range("A1", Range("A" & Rows.Count).End(xlUp))
                .Formula = "=RandBetween(5,50000)"
                .NumberFormat = "[$$-409]#,##0.00"
            End With
        Case "Eur"
            With Range("A1", Range("A" & Rows.Count).End(xlUp))
                .Formula = "=RandBetween(5,50000)"
                .NumberFormat = "[$€-1] #,##0.00"
            End With
        Case "GBP"
            With Range("A1", Range("A" & Rows.Count).End(xlUp))
                .Formula = "=RandBetween(5,50000)"
                .NumberFormat = "[$£-809]#,##0.00"
            End With
    End Select
    Application.ScreenUpdating = True
End Sub

Display More

garwil · Aug 9th 2021

Hi Mumps,

Many thanks. It does not work and now I am wondering if my system is somehow corrupted.

I found this code below and it worked perfectly and when I tried to save the workbook Excel crashed. After restarting I am now receiving error 1004 "Application-or object-defined error" each time I attempt to change the currency..(on lines, 13,15,17 and 19 depending on the currency I am changing)

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim ws As Worksheet
    
    If Target.Address = "$G$4" Then
    
        'Known cell with current currency number format
        Application.FindFormat.NumberFormat = ActiveSheet.Range("h5").NumberFormat
        
        'New currency format
        Select Case Target.Value
            Case Is = "R"
                Application.ReplaceFormat.NumberFormat = "[$R-en-ZA] #,##0.00;-[$R-en-ZA] #,##0.00"
            Case Is = "GBP"
                Application.ReplaceFormat.NumberFormat = "[$£-2] #,##0.00;-[$£-2] #,##0.00"
            Case Is = "EUR"
                Application.ReplaceFormat.NumberFormat = "[$€-2] #,##0.00;-[$€-2] #,##0.00"
            Case Is = "USD"
                Application.ReplaceFormat.NumberFormat = "[$$-409]#,##0.00_ ;-[$$-409]#,##0.00"
                
        
        End Select
        
        If Application.FindFormat.NumberFormat <> Application.ReplaceFormat.NumberFormat Then
            On Error Resume Next
            For Each ws In ThisWorkbook.Worksheets
                'Replace currency number formats
                ws.Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
                                 MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
            Next ws
            On Error GoTo 0
        End If
        
    End If
    
End Sub

Display More

Mumps · Aug 9th 2021

I tested the code on Sheet1 in the file you posted using E7 as the target cell and it worked exactly as you requested. I'm not sure what you are trying to do as you now appear to be looping through each sheet for some reason.

garwil · Aug 9th 2021

Hi again,

Apologies for the confusion... As per my original post I am trying to change each cell in a work book based on the selects currency. The values in range 'A'came from the coder who provided a demo file which did work but not in my own workbook. Instead of the code reformatting all cells in the workbook that were formatted as 'Currency - No symbol, 2 decimals' to the selected currency and format it was reformatting to 'Number' format.

The code I posted recently did what I wanted until excel crashed and now I am back to square one

Mumps · Aug 9th 2021

In the code you posted, you refer to G4 as the target cell and H5 as the reference cell. Looking at your FARES file, those cells are blank in all the sheets and are formatted as "General". In which specific sheets do you want to format the cells and what is the target cell and reference cell? I used E7 on Sheet1 as the target cell because that cell contains the currency codes. Please clarify in detail.

garwil · Aug 9th 2021

The cell 'G4' (now in the sheet called 'Temp') is the select list value ('GBP','R' etc) that I am using to choose a new currency. 'H5' is a currently a formatted cell, which I understood by the remarked sentence just above that line in the code, that would be searched for throughout the work book and replaced. As I say, it was working until Excel crashed. I have attached a new Fares.zip file with my latest efforts. The tab called temp is where I make the currency selection and the changes should take effect on tabs 'Temp' and 'Converted fares' only and any other sheet within the work book I will eventually be using this code on. Sheet 'Base fares' are conditionally formatted and so will not be altered which is correct. Column 'A' in Temp is just so I can see if there is any immediate change and those figures have no relevance other than that.

Once I have working code the select list will go into the main workbook's 'General Assumptions' sheet and so effect the changes through all 30 other sheets...

As you will see in the "Converted Fares" tab, the last time the code worked before it crashed it did change the symbol in rows D14:AK16. (I was busy changing the base format on the rest of the sheet when it crashed) . These tabs are just extracts of my whole book which is 28Mb and there are many sheets I will have to go through and change the base formatting to once I know what I am doing with this test file..

I appreciate your patience Mamps..

Mumps · Aug 9th 2021

Before testing it on all the sheets, try this code in the code module for the "Temp" sheet and see how it works.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "G4" Then Exit Sub
    Application.FindFormat.NumberFormat = Range("H5").NumberFormat
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "G4" Then Exit Sub
    Application.ScreenUpdating = False
    Select Case Target.Value2
        Case "R"
            With Range("A1", Range("A" & Rows.Count).End(xlUp))
                .Formula = "=RandBetween(5,50000)"
                Application.FindFormat.NumberFormat = .Range("H5").NumberFormat
                Application.ReplaceFormat.NumberFormat = "[$R-en-ZA] #,##0.00;-[$R-en-ZA] #,##0.00"
                .Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
                     xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
            End With
            Range("H5").NumberFormat = "[$R-en-ZA] #,##0.00;-[$R-en-ZA] #,##0.00"
        Case "USD"
            With Range("A1", Range("A" & Rows.Count).End(xlUp))
                .Formula = "=RandBetween(5,50000)"
                Application.FindFormat.NumberFormat = .Range("H5").NumberFormat
                Application.ReplaceFormat.NumberFormat = "[$$-409]#,##0.00_ ;-[$$-409]#,##0.00"
                .Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
                     xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
            End With
            Range("H5").NumberFormat = "[$$-409]#,##0.00_ ;-[$$-409]#,##0.00"
        Case "EUR"
            With Range("A1", Range("A" & Rows.Count).End(xlUp))
                .Formula = "=RandBetween(5,50000)"
                Application.FindFormat.NumberFormat = .Range("H5").NumberFormat
                Application.ReplaceFormat.NumberFormat = "[$€-2] #,##0.00;-[$€-2] #,##0.00"
                .Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
                     xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
            End With
            Range("H5").NumberFormat = "[$€-2] #,##0.00;-[$€-2] #,##0.00"
        Case "GBP"
            With Range("A1", Range("A" & Rows.Count).End(xlUp))
                .Formula = "=RandBetween(5,50000)"
                Application.FindFormat.NumberFormat = .Range("H5").NumberFormat
                Application.ReplaceFormat.NumberFormat = "[$£-2] #,##0.00;-[$£-2] #,##0.00"
                .Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
                     xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
            End With
            Range("H5").NumberFormat = "[$£-2] #,##0.00;-[$£-2] #,##0.00"
    End Select
    Range("H4").Select
    Application.ScreenUpdating = True
End Sub

Display More

garwil · Aug 9th 2021

It works on Temp but only for currencies 'GBP' and 'EUR'. 'R' and 'USD' returned application-defined or object-defined error.

Cell d6:d9 did not change but I suspect is because they are not defined in the code?

Mumps · Aug 9th 2021

The code is currently designed to work on the Temp sheet. Did it work for you?

garwil · Aug 9th 2021

Yes but only on two currencies GBP and EUR.. The others gave error

garwil · Aug 9th 2021

I managed to get the 'USD' currency OK...there was an underscore before the semi-colon ("[$$-409]#,##0.00_ which i removed and that is now OK.

The 'ZAR' is still an issue.. I have changed the code for one that is working in the other workbooks i sent but no go.. It seems to be the 'R' that is causing the problem.. just to test I replaced the 'R' with a '$' and it changed without any error albeit the symbol was wrong for that currency...

I will have bash at it again tomorrow...

Many hanks once again

VBA maco to change currency is formattign as number?/

Participate now!

Share

Tags