Microsoft Office Tutorials and References
In Depth Information
Using the ActiveChart Property
Charts are great for visually summarizing data, but if you’re constrained to displaying a chart
within a relatively small area and don’t have the room to include a legend or other
information, you can use message boxes to display information about the chart that’s selected. The
following macro might be attached to a command button on a worksheet offering help to the
user on the information in the active chart.
ChartChosen = ActiveChart.Name
Select Case ChartChosen
Case "": Exit Sub
Case "Sheet1 Chart 1": MsgBox "This chart shows the sales for 2000-2004."
Case "Sheet1 Chart 2": MsgBox "This chart shows the profits for 2000-2004."
For more information on creating and manipulating charts in VBA, see Chapter 15, “Charts.”
Displaying Drawing Objects
Workbooks are full of data and, regardless of how well you structure the data, it might not
always be easy to follow what’s going on. Adding drawing objects, such as text boxes and
arrows, lets you call out special features of your worksheets and provide helpful information
to your users. If you use drawing objects to provide that sort of information, you also have
the option to hide or display the objects at will by using the DisplayDrawingObjects method.
The following code assumes you have a series of drawing objects that might obscure your
data if left on the worksheet, but would help explain what’s going on if the user could turn
them on and off as desired. Figure 7-3 shows the message box produced by the procedure:
clicking OK hides the drawing objects, whereas clicking Cancel leaves the objects visible.
Public Sub ShowObjects()
ThisWorkbook.DisplayDrawingObjects = xlDisplayShapes
Answer = MsgBox(Prompt:="Click OK to hide the drawing _
objects.", Buttons:=vbOKCancel + vbQuestion)
If Answer = vbCancel Then
ThisWorkbook.DisplayDrawingObjects = xlHide