Changing Currency in a Graphic (Axis)

Hi,

the currency should reflect the data source.

Could you send a dummy?

Regards

try add this into your worksheet's macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 5 Then
        Dim c As Chart
        Set c = ActiveSheet.ChartObjects(1).Chart
        Select Case Target.Value
        Case "Dollar"
            f = "$#,##0"
        Case "GBP-Pound"
            f = "[$£-809]#,##0"
        Case "Euro"
            f = "[$€-483]#,##0"
        End Select
        c.Axes(xlValue).TickLabels.NumberFormat = f
    End If
End Sub

Select allOpen in new window

it seems that Conditional Formatting is no longer working if I added that scripts above. So I may remove the Conditional Formatting and use scripts to set the format of the values in the worksheet instead, which this working fine for me.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 5 Then
        Dim c As Chart
        Set c = ActiveSheet.ChartObjects(1).Chart
        Select Case Target.Value
        Case "Dollar"
            f = "$#,##0"
        Case "GBP-Pound"
            f = "[$£-809]#,##0"
        Case "Euro"
            f = "[$€-483]#,##0"
        End Select
        c.Axes(xlValue).TickLabels.NumberFormat = f
        Range("I12:J15").NumberFormat = f
    End If
End Sub

Select allOpen in new window

Currency-Formatting_b.xlsm

OK..... just created this for you to look at.  You will see when you select Euros for example... it changes the format in the data/cells but not in the graphic.

B.
Changing-Currency-in-a-graphic.xlsm

@B,

you can try this which is based on your latest uploaded sample.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 12 And Target.Column = 8 Then
        Dim c As Chart
        Set c = ActiveSheet.ChartObjects(1).Chart
        Select Case Target.Value
        Case "Dollars"
            f = "$#,##0"
        Case "GBP-Pound"
            f = "[$£-809]#,##0"
        Case "Euros"
            f = "[$€-483]#,##0"
        End Select
        c.SeriesCollection(1).DataLabels.NumberFormat = f
        Range("H16:K16").NumberFormat = f
    End If
End Sub

Select allOpen in new window

Remember to remove the Conditional Formatting in your worksheet
Changing-Currency-in-a-graphic_b.xlsm

Ryan,

I see you are using the Macro approach.  I believe that both of the previous EE Pros who answered the first question thought that given I had multiple Worksheets in the WB, that the Macro Approach would be more difficult.  Additionally, it would need to be "triggered".  I don't think it would be a problem and probably a clean approach.  When I applied the last fix, as described on the other Solution, I approached it from the Conditional Formatting approach.  This is what I just uploaded.  So....

1.) If I were to go back and use the Macro approach, how would I scale that across multiple WSs?  Place it in a Module?  How would I identify the cells to be changed universally?

2.) Take a look at the Conditional approach I uploaded.  I'm still wondering how to insure attachment of the change to the graphic.

Thoughts?

B.

OK.....let me work on this........  thanks for the input and reply.  B.

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.