Instant Slide Show

Here’s a little VBA procedure that gives you a full-screen slide show, displaying all of the charts on the active worksheet.

Sub ChartSlideShow()
    Dim ChtObj As ChartObject
    Application.DisplayFullScreen = True
    Application.ScreenUpdating = False
    For Each ChtObj In ActiveSheet.ChartObjects
        ChtObj.Chart.PrintPreview
    Next ChtObj
    Application.DisplayFullScreen = False
End Sub

Press Space, Enter, or Esc to go to the next slide. This looks a lot better with Excel 2007.

Who will be the first to beef it up by using the OnTime event?

Posted in Uncategorized

30 thoughts on “Instant Slide Show

  1. Hi John,

    Well, the OnTime event won’t help us here, since normally code will halt when Excel is in print preview mode.

    What could be done is to use the API Timer to run a subroutine that uses sendkeys to hit the escape key.

  2. To make it work with chart sheets:

    Sub ChartSlideShow()
    Dim Cht As Chart
    Application.DisplayFullScreen = True
    Application.ScreenUpdating = False
    For Each Cht In ActiveWorkbook.Charts
    Cht.PrintPreview
    Next Cht
    Application.DisplayFullScreen = False
    End Sub

  3. This will show all of the chart sheets in the active workbook:

    Sub ChartSlideShow2()
        Dim ChtObj As Chart
        Application.DisplayFullScreen = True
        Application.ScreenUpdating = False
        For Each ChtObj In ActiveWorkbook.Charts
            ChtObj.PrintPreview
        Next ChtObj
        Application.DisplayFullScreen = False
    End Sub
  4. With VB tags:

    Sub ChartSlideShow()
        Dim Cht As Chart
        Application.DisplayFullScreen = True
        Application.ScreenUpdating = False
        For Each Cht In ActiveWorkbook.Charts
            Cht.PrintPreview
        Next Cht
        Application.DisplayFullScreen = False
    End Sub
  5. Mine’s also clearer for the same reason: I didn’t use a confusing variable name, ChtObj, to refer to a chart. The smaller character count is an unintentional bonus.

  6. Yeah, I thought of that after I posted the comment, Michael. I was going to post a correction, but I didn’t think anyone would notice. Plus, I didn’t want everyone to know that I’m incapable of doing simple arithmetic.

  7. The only draw back is that if you added any “outside” text objects
    then it won’t appear.

    Using this version

    Sub ChartSlideShow()
    Dim ChtObj As ChartObject
    Application.DisplayFullScreen = True
    Application.ScreenUpdating = False
    For Each ChtObj In ActiveSheet.ChartObjects
    ChtObj.Chart.PrintPreview
    Next ChtObj
    Application.DisplayFullScreen = False
    End Sub

  8. JKP: They format the code close to what it looks like in the VBE.

    [vb]Sub MySub
    ‘some code
    End Sub[/vb]

    looks like

    Sub MySub
    ‘some code
    End Sub

    It doesn’t convert greater than and less that signs correctly, which the author acknowledges as a bug.

  9. [vb}Sub mySub()
    msgbox “This is just a test. Pay no attention to the man behind the curtain.”
    End Sub

  10. Kevin –

    “The only draw back is that if you added any “outside” text objects then it won’t appear.”

    Select the chart first, then add the textbox or other shape. This ensures that the shape is embedded in the chart, not just coinhabiting the drawing layer with the chart.

  11. And of course the next discussion item… What if I have both types? something like…

    Sub ChartSlideShow()
        Dim Sht As Object
        Dim ChtObj As ChartObject
        Application.DisplayFullScreen = True
        Application.ScreenUpdating = False
        For Each Sht in ActiveWorkbook.Sheets
            If Sht.Type = 4 Then
                Sht.PrintPreview
            Else
                For Each ChtObj In Sht.ChartObjects
                    ChtObj.Chart.PrintPreview
                Next ChtObj
        Next
        Application.DisplayFullScreen = False
    End Sub

    Am I close?

  12. Charles –

    A chart sheet can contain chart objects. This is more complete:

    Sub ShowStarringAllCharts
        Dim Sht As Object
        Dim ChtObj As ChartObject
        Application.DisplayFullScreen = True
        Application.ScreenUpdating = False
        For Each Sht in ActiveWorkbook.Sheets
            If TypeName(Sht) = “Chart” Then
                Sht.PrintPreview
            End If
            For Each ChtObj In Sht.ChartObjects
                ChtObj.Chart.PrintPreview
            Next ChtObj
        Next
        Application.DisplayFullScreen = False
    End Sub
  13. Ok, this is probably a newb question but how do you have multiple charts on one chart sheet?

  14. On a chart sheet, there’s the main chart (which you can hide by deleting all of the series).

    Then you can have embedded charts the same as on worksheets. In step 3 of the chart wizard, or by choosing Location from the Chart menu, you can choose to have the chart on its own sheet, or as an object in any of the sheets listed in the pulldown. And this pulldown lists all sheets, not just worksheets.

  15. Cool, thanks, I play with that next time I have a chance. Is it possible to VB controls on there as well? Most of my charts are based on input parameters, I like to be able to change those parameters and immediately see the results. Unfortunately my parameters involve long array formulas and I haven’t figured out how to use them in a pivot chart.

  16. Tony – You arrange the charts by using the Send to Back / Bring Forward functions (right mouse button on your chart.

  17. How do you make this a endless loop of displaying the charts (except if you hit the esc key)?

Leave a Reply

Your email address will not be published. Required fields are marked *