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"
 
    Else
 
        With ActiveChart
 
            If .ChartType = xlLine Then
                .ChartType = xlColumnClustered
            Else
                .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