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. Jan Karel –

   I’m sure that’s what he meant! <g>

3. John, what needs changed to handle Chart sheets instead of embedded sheets?

4. 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

5. 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

6. 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

7. I like Jon’s code better. It uses 12 fewer characters.

8. 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.

9. Sigh. 2 + Obj + Obj + Obj + Obj = 13 Characters

10. 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.

11. Silly question: What are the vb tags (do I enclose VB in rectangular brackets?)?

12. 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

13. 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.

14. I knew their purpose, just didn’t now the syntax. Thanks Dick.

15. OK guys, here it is:

    http://www.jkp-ads.com/downloadscript.asp?filename=ChartSlideShow.zip

16. Forgot instruction:

    – open this file
    – open file with charts
    – run macro “ChartSlideShow”

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

18. oops, slight error. One more try for posterity sake:

    Sub AddNumbers()
    debug.print 2 + 3
    End Sub

19. 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.

20. 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?

21. of course it might compile a little better with “End If” after “Next ChtObj”.

22. 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

23. This is great stuff. Thanks

24. Ok, this is probably a newb question but how do you have multiple charts on one chart sheet?

25. 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.

26. 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.

27. “…In step 3 of the chart wizard…”

    Doh! I meant Step 4 of the Chart Wizard.

    Charles –

    You can only use the data in the pivot table in a pivot chart. However, there’s no rule that says you have to make a pivot chart from a pivot table. This article describes a bit about pivot tables and pivot charts, and how to make a regular chart from a pivot table:

    http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553

28. How do you control the Order in which the charts are shown?

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

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

Posting code? Use <pre> tags for VBA and <code> tags for inline.