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
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?
jkpieterse:
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.
25 October 2006, 8:21 amJon Peltier:
Jan Karel -
I'm sure that's what he meant! <g>
25 October 2006, 9:33 amCharles Chickering:
John, what needs changed to handle Chart sheets instead of embedded sheets?
25 October 2006, 9:41 amJon Peltier:
To make it work with chart sheets:
Sub ChartSlideShow()
25 October 2006, 10:58 amDim Cht As Chart
Application.DisplayFullScreen = True
Application.ScreenUpdating = False
For Each Cht In ActiveWorkbook.Charts
Cht.PrintPreview
Next Cht
Application.DisplayFullScreen = False
End Sub
John Walkenbach:
This will show all of the chart sheets in the active workbook:
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
Jon Peltier:
With VB tags:
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
John Walkenbach:
I like Jon's code better. It uses 12 fewer characters.
25 October 2006, 11:07 amJon Peltier:
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.
25 October 2006, 12:47 pmMichael:
Sigh. 2 + Obj + Obj + Obj + Obj = 13 Characters
25 October 2006, 3:24 pmJohn Walkenbach:
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.
25 October 2006, 5:06 pmjkpieterse:
Silly question: What are the vb tags (do I enclose VB in rectangular brackets?)?
26 October 2006, 4:59 amKevin:
The only draw back is that if you added any "outside" text objects
then it won't appear.
Using this version
Sub ChartSlideShow()
26 October 2006, 6:48 amDim 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
Dick Kusleika:
JKP: They format the code close to what it looks like in the VBE.
[vb]Sub MySub
'some code
End Sub[/vb]
looks like
'some code
End Sub
It doesn't convert greater than and less that signs correctly, which the author acknowledges as a bug.
26 October 2006, 6:53 amjkpieterse:
I knew their purpose, just didn't now the syntax. Thanks Dick.
26 October 2006, 7:36 amjkpieterse:
OK guys, here it is:
http://www.jkp-ads.com/downloadscript.asp?filename=ChartSlideShow.zip
26 October 2006, 7:44 amjkpieterse:
Forgot instruction:
- open this file
26 October 2006, 7:45 am- open file with charts
- run macro "ChartSlideShow"
Kevin Vaughn:
[vb}Sub mySub()
26 October 2006, 8:49 ammsgbox "This is just a test. Pay no attention to the man behind the curtain."
End Sub [/vb]
Kevin Vaughn:
oops, slight error. One more try for posterity sake:
debug.print 2 + 3
End Sub
Jon Peltier:
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.
26 October 2006, 9:06 amCharles Chickering:
And of course the next discussion item... What if I have both types? something like...
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?
26 October 2006, 1:41 pmCharles Chickering:
of course it might compile a little better with "End If" after "Next ChtObj".
26 October 2006, 1:44 pmJon Peltier:
Charles -
A chart sheet can contain chart objects. This is more complete:
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
Kevin:
This is great stuff. Thanks
27 October 2006, 7:18 amCharles Chickering:
Ok, this is probably a newb question but how do you have multiple charts on one chart sheet?
27 October 2006, 9:54 amJon Peltier:
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.
27 October 2006, 10:20 amCharles Chickering:
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 October 2006, 10:28 amJon Peltier:
"...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
27 October 2006, 3:26 pm