header image
Toggle all Charts between Logarithmic and Linear Scale
September 1st, 2015 under Excel. [ Comments: none ]

I had multiple chart in one Excel Worksheet and wanted to change the scale of the x-axis and y-axis to the logarithmic scale. The following macro toggles between the linear and logarithmic scale for all charts:

Click to toggle codeblock
1
2
3
4
5
6
7
8
9
10
11
12
13

Sub ChartsToggle()
    For i = 1 To ActiveSheet.ChartObjects.Count      
      Application.DisplayAlerts = False
      If ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLinear Then
        ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLogarithmic
        ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType = xlLogarithmic
      Else
        ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLinear
        ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType = xlLinear
      End If
    Next i
  Application.DisplayAlerts = True
End Sub
Sub ChartsToggle()
    For i = 1 To ActiveSheet.ChartObjects.Count      
      Application.DisplayAlerts = False
      If ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLinear Then
        ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLogarithmic
        ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType = xlLogarithmic
      Else
        ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLinear
        ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType = xlLinear
      End If
    Next i
  Application.DisplayAlerts = True
End Sub

Note the

Click to toggle codeblock
1

Application.DisplayAlerts = False
Application.DisplayAlerts = False
. This prevents Excel from opening a dialog box, for every chart, informing you that negative values cannot be displayed. The dialog is just shown once at the end. I had to put this line inside the for-loop because it got changed back to true after the chart modification.