Microsoft Office Tutorials and References
In Depth Information
Discovering VBA Charting Tricks
Printing embedded charts on a full page
When an embedded chart is selected, you can print the chart by choosing File➜Print. The
embedded chart will be printed on a full page by itself (just as if it were on a chart sheet), yet it
will remain an embedded chart.
The following macro prints all embedded charts on the active sheet, and each chart is printed on
a full page:
Sub PrintEmbeddedCharts()
Dim ChtObj As ChartObject
For Each ChtObj In ActiveSheet.ChartObjects
ChtObj.Chart.PrintOut
Next ChtObj
End Sub
Hiding series by hiding columns
By default, Excel charts don’t display data contained in hidden rows or columns. The workbook
shown in Figure 18-15 demonstrates an easy way to allow the user to hide and unhide particular
chart series. The chart has seven data series, and it’s a confusing mess. A few simple macros
allow the user to use the ActiveX CheckBox to indicate which series they’d like to view. Figure
18-16 shows the chart with only three series displayed.
Each series is in a named range: Product_A , Product_B , and so on. Each check box has its
own Click event procedure. For example, the procedure that’s executed when the user clicks
the Product A check box is
Private Sub CheckBox1_Click()
ActiveSheet.Range(“Product_A”).EntireColumn.Hidden = _
Not ActiveSheet.OLEObjects(1).Object.Value
End Sub
This workbook, named hide and unhide series.xlsm , is available on the
companion CD-ROM.
 
Search JabSto ::




Custom Search