A Quick And Dirty Slideshow Macro

Category: Charts & Graphics / General VBA | [Item URL]

A companion file is available: Click here to download

Here's a VBA macro that displays a full-screen slide show, using all of the embedded charts on the active worksheet.

Sub ChartSlideShow()
    Dim Cht As ChartObject
    Dim UserSheet As Worksheet
    Set UserSheet = ActiveSheet
    Application.DisplayFullScreen = True
    Application.DisplayAlerts = False

    For Each Cht In UserSheet.ChartObjects
        Application.ScreenUpdating = False
        ' Delete old chart sheet if it exists
        On Error Resume Next
        Charts("ChartTemp").Delete
        On Error GoTo 0
        ' Copy embedded chart and move it
        UserSheet.Activate
        Cht.Chart.ChartArea.Copy
        ActiveSheet.Paste
        ActiveChart.Location Where:=xlLocationAsNewSheet, _
           Name:="ChartTemp"
        ' Show the chart sheet and prompt for next one
        Application.ScreenUpdating = True
        If MsgBox("OK for next chart, Cancel to stop.", _
          vbQuestion + vbOKCancel) = vbCancel Then Exit For
     Next Cht
     ' Clean up
     On Error Resume Next
     Charts("ChartTemp").Delete
     On Error GoTo 0
     Application.DisplayFullScreen = False
     Application.DisplayAlerts = True
     UserSheet.Activate
End Sub

Just copy and paste the code to a VBA module. Then activate a worksheet that contains embedded charts and execute the ChartSlideShow macro. It makes no changes to your original charts.

Note that you can create text-only charts by deleting the chart in a chart object. Then insert a shape and add some text. You can also display pictures by deleting the chart and inserting a picture.

I tested it in both Excel 2007 and Excel 2003. It works in both versions, but it looks a bit better in Excel 2007.

The order of the slides is determined by the z-order of the charts on the worksheet. You can change the z-order by right-clicking the chart object, and using the 'send forward' or 'send backward' command. In Excel 2007, it's much easier to do this by using the Re-order buttons in the Selection and Visibility pane.