Weird excel error on Custom formatting please help

[Du1point8]

£ added to cell in excel despite $ on the custom format using VBA, how to fix?

Im trying to programmatically update cells in Excel but for some reason the VBA code cant programmatically update for $, but can for "£", "€", "¥".

I can use -"$"* #,##0.00-;-"$"* #,##0.00_-;-"$"* "-"??-;_-@ manually in custom format, but when this is added using VBA I get £.

The cells in question have no conditional formatting and have no custom formatting on them as I cleared them all up.

You may suggest to do this using conditional formatting if that works, unfortunately I have hundreds of cells and several CCYs to do so would rather do it smarter then harder.

Sub UpdateCurrencyFormatting()
    Dim currencySymbol As String
    Dim currencyFormat As String
    Dim ws As Worksheet

    ' Define the specific worksheet
    Set ws = ThisWorkbook.Sheets("Step 2. Group Effort")

    ' Get the currency symbol from the helper cell on the specified worksheet
    currencySymbol = ws.Range("J1").Value

    ' Create the custom number format string using the currency symbol
     currencyFormat = "_-""" & currencySymbol & """* #,##0.00_-;-" & _
                 """" & currencySymbol & """* #,##0.00_-;_-" & _
                 """" & currencySymbol & """* ""-""??_-;_-@"

  Debug.Print "Currency Symbol: " & currencySymbol

Debug.Print "Currency Format: " & currencyFormat
    ' Apply the custom number format to the desired range on the specified worksheet
    ws.Range("I6:I7").NumberFormat = currencyFormat
 ws.Range("I9:I11").NumberFormat = currencyFormat
End Sub

The outputs are as follows:

Currency Symbol: £
Currency Format: -"£"* #,##0.00-;-"£"* #,##0.00_-;-"£"* "-"??-;-@
Currency Symbol: €
Currency Format: -"€"* #,##0.00-;-"€"* #,##0.00-;-"€"* "-"??-;-@
Currency Symbol: $
Currency Format: -"$"* #,##0.00-;-"$"* #,##0.00-;-"$"* "-"??-;-@
Currency Symbol: ¥
Currency Format: -"¥"* #,##0.00-;-"¥"* #,##0.00-;-"¥"* "-"??-;_-@

The only one that will not work is $

Cell J1 is a helper cell that I use the following code on:

=CHOOSE(MATCH('Step 3. Pricing Review'!$L$39, {"JPY","EUR","USD","GBP"}, 0), "¥", "€", "$","£")

I have a trigger code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("J1")) Is Nothing Then
Call UpdateCurrencyFormatting
End If
End Sub

So whenever someone changes the Pricing Review'!$L$39 CCY it auto updates Step 2. Group Effort'!$J$1 and the code above fires the autocurrency format update.

As mentioned it works for all but $ and I have tried various techniques including ASCII:

currencyFormat = "_-""" & Chr(36) & """* #,##0.00_-;-""" & Chr(36) & """* #,##0.00_-;_-""" & Chr(36) & """* ""-""??_-;_-@"

Which gives the following:

Currency Symbol: $
Currency Format: -"$"* #,##0.00-;-"$"* #,##0.00_-;-"$"* "-"??-;_-@

Yet still prints £ instead of $, So now I need to reach out and find out if someone has a workaround and can help.

Thanks in advance

[RustyMX5]

This is an unhelpful post. When I try to code (I'm crap by the way) and get stuck, I tend to chuck it into ChatGPT and ask for help. Amazingly it's come up with some really good suggestions.

[MOBB]

You might need someone on MrExcel for that one

[Sporky]

Can you add a debug.print line that outputs the currencyformat string to the immediate window?

The we can start to work out where Excel is trying to outsmart you.

Argh - I an idiot, you've already got that, sorry. I'll have another look through.

Edited by Sporky on Wednesday 1st May 20:10

[TonyRPH]

I asked ChatGPT (supplying your code) and it suggested the following:

ChatGPT said:

Your VBA code seems to be trying to format cells in the specified worksheet ("Step 2. Group Effort") with a custom currency format derived from a currency symbol located in cell J1. However, it looks like you're encountering issues with the "$" sign in the currency symbol.

Your currencyFormat string construction seems to be using " as a string delimiter, which is correct. The issue might lie in how the "$" sign is represented in your helper cell. Make sure that cell J1 contains just the "$" sign without any additional characters or formatting.

Here's what you can check and try:

Ensure J1 Contains Only the Currency Symbol: Make sure cell J1 contains only the currency symbol "$" without any leading or trailing spaces or any other characters.

Debug the Currency Symbol Retrieval: You're printing the currency symbol to the debug console. Check whether the correct currency symbol is being retrieved from cell J1 by reviewing the output in the Immediate Window.

Test with a Hardcoded Currency Symbol: Instead of retrieving the currency symbol from cell J1, try hardcoding it directly into the VBA code (e.g., currencySymbol = "$"). This will help determine if the issue is with the retrieval of the currency symbol from the cell.

Check Cell Formatting: Ensure that cell J1 is formatted as text to preserve the "$" sign as a string. Excel sometimes automatically formats input based on its interpretation, which might lead to unexpected behavior.

Check for Hidden Characters: There might be hidden characters in cell J1 that are affecting the currency symbol retrieval. Try clearing any formatting or hidden characters from the cell.

By verifying these aspects, you should be able to identify and resolve the issue with using the "$" sign in your VBA macro for currency formatting.

[Du1point8]

I chucked it into chatGpt a few times, it was fixed by doing the following:


if currencySymbol = "$" then currencySymbol = Replace(ws.Range("J1").Value,"$","\$")

currencyFormat = "_-" & currencySymbol & "* #,##0.00_-;-" & _
currencySymbol & "* #,##0.00_-;_-" & _
currencySymbol & "* ""-""??_-;_-@"

Seems it didn't like the "" even if manual formatting did.

[RustyMX5]

I've had to resort to using ChatGPT with some SQL queries. I found it could take a few goes before I was able to provide it with the information it needed to give me a query which delivered what I was looking for.

[Du1point8]

RustyMX5 said:

I've had to resort to using ChatGPT with some SQL queries. I found it could take a few goes before I was able to provide it with the information it needed to give me a query which delivered what I was looking for.

I must admit that whilst I put it in ChatGPT a few times, it was a live human that spotted the possible issue that $ was causing.