Microsoft Office Tutorials and References
In Depth Information
ADJUSTING AXIS SCALING
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$B$2” Then
ActiveSheet.ChartObjects.Visible = False
On Error Resume Next
ChartObjects(Target.Value).Visible = True
End If
End Sub
The five embedded charts are stacked on top of each other, so they all appear in
the same position on-screen.
ADJUSTING AXIS SCALING
Earlier in this chapter, I present a macro that changes the value axis scaling for a
chart (see “Setting axis values”). That macro, when executed, adjusts the chart’s
value axis properties: MinimumScale , MaximumScale , and MajorUnit .
The macro in this section further automates the task by using an event macro.
Figure 16-18 shows the worksheet. The Axis property values are entered in the cells
in column D (named AxisMin , AxisMax , and MajorUnit ).
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)
.MinimumScale = Range(“AxisMin”)
.MaximumScale = Range(“AxisMax”)
.MajorUnit = Range(“MajorUnit”)
End With
End If
End Sub
This procedure is executed whenever any cell in the sheet is changed. The first
statement of the macro checks the Column property of the Target variable, which
represents the cell that was changed. If the changed cell is in column 4, the
property values are retrieved from the sheet and applied to the chart. Otherwise, nothing
happens.
Search JabSto ::




Custom Search