Catégorie : Excel VBA Course

By default, events are associated with charts that are created on separate chart sheets. Let’s look at some examples of handling events related to charts.

Suppose we need to change the color of the chart area and the chart itself (located on a separate chart sheet) depending on where the mouse click occurs. To handle this event, you can use the code shown in the Chart1 module.

Another example concerns handling the mouse move event on chart sheets. Suppose we want to display additional annotations related to data points in a text box located on the chart sheet .

To implement this example, prepare on Sheet1 the corresponding data table and an annotations table. Then, in the Chart1 module, enter the code.

Linking annotation text to a chart sheet (Chart1 module)

Option Explicit
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, _
    ByVal X As Long, ByVal Y As Long)  
    Dim RowId As Long
    Dim rg1 As Long, rg2 As Long
    Dim MyText As String
    On Error Resume Next
    ActiveChart.GetChartElement X, Y, RowId, rg1, rg2  
    If RowId = xlSeries Then
        MyText = Sheets("Sheet1").Range("Note").Offset(rg2, rg1)
    Else
        MyText = "For information about Nobel Prize winners, " & _
                 "select a column in the chart."
    End If   
    ActiveChart.Shapes(1).TextFrame.Characters.Text = MyText
End Sub

Linking Events to Embedded Charts

If a chart is embedded in a worksheet, you cannot directly link events to it. In this case, you need to perform several additional steps before you can bind the required events to charts located on worksheets.

As a small example, do the following:

• Open the VBA editor and create a class module named MyEventClassModule (Insert | Class Module, then set the Name property to MyEventClassModule).
• Declare in the class module a variable of type Chart with the keyword WithEvents. After that, in the code editor, the object MyEventClassModule will appear in the object list, and all events associated with charts will appear in the event list.

Linking events to embedded charts. Class module MyEventClassModule (version 1)

Public WithEvents MyChartClass As Chart

• In the code editor, enter the code for handling the events needed for your project’s business logic. For example we link the mouse button click to a message: “Think about your next steps!”

Linking events to embedded charts. Class module MyEventClassModule (version 2)

Private Sub MyChartClass_MouseDown(ByVal Button As Long, _
    ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    MsgBox "Think about your next steps!"
End Sub

• Link the event to the embedded chart. For example, this can be done at the workbook open stage by adding to the ThisWorkbook module the code. This associates the first embedded chart on the first worksheet with the MyChartClass object. Now, when you click the mouse on this chart, the message “Think about your next steps!” will appear.

Linking events to embedded charts. ThisWorkbook module

Dim MyClassModule As New MyEventClassModule
Sub ChartInit()
    Set MyClassModule.MyChartClass = Worksheets(1).ChartObjects(1).Chart
End Sub

Private Sub Workbook_Open()
    ChartInit
End Sub

Changing Chart Type via Context Menu

As another example of using chart events for embedded charts, let’s consider a project where, when you right-click the chart, a context menu appears with the following commands: Column, Line, Doughnut, Area, Bar, and Cone. Selecting one of these changes the chart to the corresponding type.

First, the event to handle is the right-click on an embedded chart. Therefore, you need to create a class (in this case ChartEventClass) where the code to handle this event is implemented. When the workbook is opened, the instance of ChartEventClass is linked to the specific chart, the context menu is created, and the commands of this menu are assigned macros that perform the chart type change.

If you want to protect a chart built on a worksheet, as well as the worksheet data outside a certain range, use the Protect method of the Worksheet object with the parameter UserInterfaceOnly set to True. This will protect the worksheet and allow data entry only in the specified cells. For example, to protect all worksheet objects except the range B4:G13, add the code from Listing 6.8 to the ThisWorkbook module.

To allow data entry on the worksheet, go to the Review tab on the ribbon and, in the Changes group, click Unprotect Sheet. Typically, you will be prompted to enter the password (in our case, « pass »).

Setting protection on an embedded chart. ThisWorkbook module

Private Sub Workbook_Open()
    SetPtotection
End Sub

Private Sub SetPtotection()
    On Error Resume Next
    Worksheets("Vedomost").Range("B4:G13").Locked = False
    Worksheets("Vedomost").Protect Password:="pass", UserInterfaceOnly:=True
End Sub

Note
If you add protection to a worksheet that contains controls, attempting to use the controls may cause a project error. Remove the sheet protection and perform the usual actions.

Protecting a Chart on a Separate Chart Sheet

To protect a chart located on a separate chart sheet (a Chart object), use the Protect method:

Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly)

• Password — sets the protection password.
• DrawingObjects — protects drawing objects.
• Contents — protects the entire chart.
• Scenarios — protects scenarios.
• UserInterfaceOnly — protects the user interface but not macros. If this parameter is omitted, the protection applies to both the interface and macros.

You can remove protection using Unprotect:

Unprotect(Password)

Listing shows how to protect a chart located on a separate sheet, and also how to lock all cells on worksheet Sheet1 for the user, except the range B4:G13.

Setting protection on a chart. ThisWorkbook module

Private Sub Workbook_Open()
    SetPtotection
End Sub

Private Sub SetPtotection()
    On Error Resume Next
    Charts(1).Protect Password:="d1", UserInterfaceOnly:=True
    Worksheets("Sheet1").Range("B4:G13").Locked = False
    Worksheets("Sheet1").Protect Password:="1"
End Sub

Let us now look at an example of creating a surface chart, which naturally has a three-dimensional orientation. As the data source, we will once again use the report of the computer club network’s performance. The surface will be created automatically from the table when the workbook is opened:

• the x-axis will represent the names of the clubs,
• the y-axis will represent the months,
• the z-axis will represent the clubs’ revenues.

Clearly, the clarity of a surface chart greatly depends on the side and angle from which the user views it. Therefore, in addition to programming the chart creation process, we will place two control elements (lists) on the worksheet.

• The first list is intended for changing the elevation angle from which the surface is viewed. Set its Name property to Elev.
• The second list is intended for rotating the surface around the z-axis. Set its Name property to Rotat.

When the worksheet is activated, the lists are populated with permissible angles. In the ThisWorkbook standard module and the Vedomost worksheet module, enter the code for the corresponding procedures.

As another example, let us consider a surface chart where the rotation takes place in three dimensions.

To implement this example, perform the following steps:

1. Prepare the data range for the chart. Enter values into cells A1:A16 and B1:L1 for the x-axis (interval: –1 to 6.5, step: 0.5) and y-axis (interval: –1 to 1.2, step: 0.2).

In cell B2, enter the formula for z = cos(x)cos(y)sin(xy):

=COS($A2)*COS(B$1)*SIN($A2*B$1)

Copy this formula across the range B2:L16.

• Select the range A1:L16 and build a surface chart using Excel’s built-in tools: go to the Insert tab on the ribbon, in the Charts group click Other Charts, and choose the type Surface.
• Format the resulting surface using the features of the contextual Chart Tools tabs.
• Place three CommandButton controls on the worksheet and set their Caption properties as follows:
  • ROTATION (for CommandButton1),
  • TURN (for CommandButton2),
  • PERSPECTIVE (for CommandButton3).
• Enter in a standard module and in the worksheet module Surface_Rotation the code for the procedures that support surface rotation in three dimensions.

Quite often, it is desirable to see certain trends in the data presented on a chart. In such cases, you can add a trendline, which makes it possible to forecast values.

If you want to add a trendline using Microsoft Excel’s built-in tools, activate the chart, go to the Layout contextual tab on the ribbon, and in the Analysis group click the Trendline drop-down list, then choose the desired type of trendline. Remember that a trendline is always constructed for the selected data series; therefore, if the chart contains multiple series, you need to specify for which series the trendline should be built. Alternatively, you can select the desired series directly on the chart and, from the context menu, choose Add Trendline.

From the VBA perspective, all trendlines corresponding to a given data series form the Trendlines collection, whose elements are Trendline objects. The Trendlines collection has only two methods:

• Add — adds a new element to the collection,
• Item — returns a specific element from the collection.

The Trendline object has the same properties as the parameters of the Add method (see Microsoft VBA Help).

Description of the Add method of the Trendlines collection

Add(Type, Order, Period, Forward, Backward, Intercept, DisplayEquation, DisplayRSquared, Name)

• Type — sets the type of trendline. Valid values:
  • xlLinear (linear),
  • xlLogarithmic (logarithmic),
  • xlExponential (exponential),
  • xlPolynomial (polynomial),
  • xlMovingAvg (moving average),
  • xlPower (power).
• Order — sets the order of the polynomial trendline (valid integers 2 to 6; used only if Type = xlPolynomial).
• Period — trend period (valid integers 1 to the number of data points; used only if Type = xlMovingAvg).
• Forward — number of points forward (future) to forecast.
• Backward — number of points backward (past) to forecast.
• Intercept — intercept on the y-axis.
• DisplayEquation — Boolean, whether to display the trendline equation on the chart.
• DisplayRSquared — Boolean, whether to display the R² (coefficient of determination) value.
• Name — string specifying the name of the trendline.

Example: Trendline in the Computer Club Project

In our last project, we will add a group of CheckBox controls that allow the user to manage the display of the trendline on the chart (Fig. 6.10; see also file 7-Building a Trendline.xlsm on the CD).

On the Vedomost worksheet, add three checkboxes and, in the Properties window, set their properties as shown in Table.

Table. Values of properties set in the Properties window

Control	Property	Value
CheckBox	Name	Trend
	Caption	Trendline
CheckBox	Name	Equation
	Caption	Equation
CheckBox	Name	Coef
	Caption	R-squared value

Open the previous project related to computer clubs and make the following additions. In the Vedomost worksheet module, add the code for the three Click event procedures of the checkboxes:

• The Trendline checkbox builds or removes the trendline. If the trendline is removed, the Equation and R-squared checkboxes are disabled.
• The Equation checkbox controls whether the equation of the trendline is displayed.
• The R-squared checkbox controls whether the coefficient of determination is displayed.

Trendline. Vedomost worksheet module

Private Sub Trend_Click()
    Dim c As Chart
    On Error Resume Next
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then Exit Sub
    ActiveSheet.ChartObjects(1).Activate
    Set c = ActiveChart
    If Trend.Value Then
        Equation.Enabled = True
        Coef.Enabled = True
        c.SeriesCollection(1).Trendlines.Add _
            Type:=xlLinear, Forward:=0, Backward:=0, _
            DisplayEquation:=False, DisplayRSquared:=False
        If Trend.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayEquation = False
        End If
        If Coef.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayRSquared = True
        End If
    Else
        c.SeriesCollection(1).Trendlines(1).Delete
        Equation.Enabled = False
        Coef.Enabled = False
    End If
End Sub

Private Sub Equation_Click()
    On Error Resume Next
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then Exit Sub
    If Trend.Value Then
        Dim c As Chart
        ActiveSheet.ChartObjects(1).Activate
        Set c = ActiveChart
        If Equation.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayEquation = True
        Else
            c.SeriesCollection(1).Trendlines(1).DisplayEquation = False
        End If
    End If
End Sub

Private Sub Coef_Click()
    On Error Resume Next
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then Exit Sub
    If Trend.Value Then
        Dim c As Chart
        ActiveSheet.ChartObjects(1).Activate
        Set c = ActiveChart
        If Coef.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayRSquared = True
        Else
            c.SeriesCollection(1).Trendlines(1).DisplayRSquared = False
        End If
    End If
End Sub

In the ThisWorkbook module, add the following procedure to set the initial states of the checkboxes . Naturally, the call to InitTrend must also be added to the Workbook_Open procedure in the same module.

Trendline. ThisWorkbook module

Private Sub InitTrend()
    With Worksheets("Vedomost")
        .Trend.Value = False
        .Equation.Value = False
        .Coef.Value = False
    End With
End Sub

By default, in Microsoft Excel, charts do not display data contained in hidden rows or columns. In this example, we will illustrate a simple way to hide and display data series in a chart. As control elements for visualizing or hiding a data series—both in the worksheet and in the chart we will use checkboxes.

To implement this example, follow these steps:

• Prepare a table on the worksheet showing product sales by months.
• Build a chart for the prepared data: go to the Insert tab on the ribbon, and in the Charts group, choose from the Line list the chart type Line with Markers.
• Format the resulting chart.
• Add five CheckBox controls on the worksheet in sequence and set the Caption property for each of them respectively to: Ruler, Pencil Case, Pen, Pencil, Eraser.
• In the Sheet1 worksheet module, add event-handling procedures for the Click events of the CheckBox controls.