Using VBA to activate a chart

When a user clicks on an embedded chart, the chart is activated. Your VBA code can activate an embedded chart with the Activate method. Here's an example:

ActiveSheet.ChartObjects("Chart 1").Activate

If the chart is on a chart sheet, use a statement like this:

Sheets("Chart1").Activate

After a chart is activated, you can refer to it in your code with ActiveChart. For example, the following instruction displays the name of the active chart. If there is no active chart, the statement generates an error:

MsgBox ActiveChart.Name

To modify a chart with VBA, it's not necessary to activate it. The two procedures that follow have exactly the same effect: That is, they change the embedded chart named Chart 1 to an area chart. The first procedure activates the chart before performing the manipulations; the second one doesn't.

Sub ModifyChart1()

ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.Type = xlArea ActiveChart.Deselect

End Sub

Sub ModifyChart2()

ActiveSheet.ChartObjects("Chart 1").Chart.Type = xlArea End Sub

A chart embedded on a worksheet can easily be converted to a chart sheet. To do so manually, just activate the embedded chart and choose Chart ^ Location. In the Chart Location dialog box, select the As New Sheet option and specify a name. This action essentially copies the Chart object (contained in a ChartObject object) to a chart sheet and then destroys its containing ChartObject object.

You can also convert an embedded chart to a chart sheet with VBA. Here's an example that converts the first ChartObject on a worksheet named Sheet1 to a chart sheet named MyChart:

Sub ConvertChart1()

Sheets("Sheet1").ChartObjects(1).Chart. _ Location xlLocationAsNewSheet, "MyChart" End Sub

The following example does just the opposite of the preceding procedure: It converts the chart on a chart sheet named MyChart to an embedded chart on the worksheet named Sheet1.

Sub ConvertChart2()

Charts("MyChart") _

.Location xlLocationAsObject, "Sheetl" End Sub

Using the Location method also activates the relocated chart.

When you activate a chart contained in a ChartObject, the chart is actually contained in a window that is normally invisible. To see an embedded chart in its own window, right-click the ChartObject and select Chart Window from the shortcut menu. The embedded chart will remain on the worksheet, but the chart will also appear in its own floating window (see Figure 18-2). You can move and resize this window, but you can't maximize it. If you move the window, you'll notice that the embedded chart is still displayed in its original location. Activating any other window makes the ChartObject window invisible again.

The following VBA code displays the window for the first ChartObject on the active sheet:

ActiveSheet.ChartObjects(1).Activate ActiveChart.ShowWindow = True

A

B | C

D

E FIG H I 1 J K

2

John Fietl

Day 1

_32

36

IS [scores,x!s]Sheet1 Chart 1

4

Day 2 Day 3

38

32 34

60 50 -

6

Day 4

45

30

Day 5

52

26

8 g

~Y2 13

" 20

—■

-■-Fred

15

16

,_

20

Day 1 Day 2 Day 3 Day 4 Day 5 J

I I I I I I I

M A ► mI\ Chart 1 VSheetl /

Figure 18-2: Displaying an embedded chart in a window.

Figure 18-2: Displaying an embedded chart in a window.

For a practical application of using a window to display an embedded chart, see "Printing embedded charts on a full page," later in this chapter.

0 0

Responses

Post a comment