Dynamic label and axis format in Excel

[Mark McInerney]

Hi All,

I have a row of data, that is driven by an If then else statement - i.e. if cell a1 = 1 then the output is euro data, or, if cell a2 = 2 then the output is percentage data.

I want to graph the data.

Is there a way to tag the axis and labels to a format depending on the value in cell A1? i.e. A1 = 1 format €, or Cell A1 = 2 format %.

Any help or thoughts...genuinely appreciated.

Best - Mark.

 

[onlyadrafter]

Hello,

I have assumed your graph/data is the same, its just the format/labels that you require changing. I have also assumed that you have already created the graph, and that cell A1 is manually entered, but I guess some (or all) of these are in-correct.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Select Case Target.Value
            Case 1
                ActiveSheet.ChartObjects("Chart 1").Activate
                ActiveChart.Axes(xlValue).Select
                Selection.TickLabels.NumberFormat = "0.00%"
                ActiveChart.SeriesCollection(1).Select
                ActiveChart.SeriesCollection(1).DataLabels.Select
                Selection.NumberFormat = "0.00%"
            Case 2
                ActiveSheet.ChartObjects("Chart 1").Activate
                ActiveChart.Axes(xlValue).Select
                Selection.TickLabels.NumberFormat = "[$€-2] #,##0.00"
                ActiveChart.ChartArea.Select
                ActiveChart.SeriesCollection(1).DataLabels.Select
                Selection.NumberFormat = "[$€-2] #,##0.00"
        End Select
    End If
    Range("A1").Select
End Sub

This was created from using the macro recorder, so you may need to change references.