Page 165 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

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 informa- Chapter 7
tion, 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.

Sub ChartHelp()
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."
End Select
End Sub

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
Exit Sub
Else
ThisWorkbook.DisplayDrawingObjects = xlHide
End If
End Sub

139