Switching Between a Line and a Column Chart

Keeping charts simple is a good rule to live by. Two of the simplest charts are a column chart and a line chart. People have their preferences. Here is a short macro that lets you switch between the two chart types.

In the image below we have data on the left and the chart on the right plus a blue button.

You can download an example file with the macro using the button at the bottom of this post.

Clicking the blue button changes the chart.

MACRO WARNING: Macros clear the undo list, so you can’t undo what the macro does or anything before the macro was run. Save your file before running the macro then you can close without saving if an issue arises.

The macro that does this is short and it works on the selected chart. The code is shown below.

Sub Switch_Column_Line()
'A toggle macro that switches the selected chart between
' a column chart and a line chart
    If ActiveChart Is Nothing Then
        MsgBox "Please select a chart before clicking the button", _
            vbOKOnly + vbInformation, "Chart required"
        With ActiveChart
            If .ChartType = xlLine Then
                .ChartType = xlColumnClustered
                .ChartType = xlLine
            End If
        End With
    End If
End Sub

This code relies on a chart being selected before the macro is run.

The code first checks if there is an ActiveChart object. If ActiveChart is Nothing that means no chart is selected. If that happens, a message box is displayed to inform the user to select a chart first. See example below.

The code then checks if the selected chart is a line chart. If it is it changes the type to a column chart otherwise it changes the type to a line chart.

One macro to make two changes.

To run the macro off the image, right click the image and choose Assign Macro.

Pic 4

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.