VBA实现图表与CheckBox联动

'类模块---》添加类名为“CCB”的类，代码如下：

Option Explicit

Private WithEvents m_CB As MSForms.CheckBox 
Private m_Form As Worksheet

Public Sub Init(ctl As MSForms.CheckBox, frm As Worksheet)
Set m_CB = ctl
Set m_Form = frm
End Sub

Private Sub m_CB_Click()
Dim KPIName As String
Dim startNo As Long, endNo As Long
Dim objsheet As Worksheet
Set objsheet = Sheets("U121")
KPIName = m_CB.Caption
If m_CB.Value = True Then
startNo = GetStartRowno(objsheet.Range("A1:A20"), KPIName, endNo)
With objsheet.ChartObjects(1).Chart
.SeriesCollection.NewSeries
.SeriesCollection(3).Name = "='U121'!$A$8"
.SeriesCollection(3).Values = "='U121'!$B$8:$Q$8"
End With
Else
objsheet.ChartObjects(1).Chart.SeriesCollection(3).Delete
End If
End Sub

Private Sub Class_Terminate()
Set m_CB = Nothing
Set m_Form = Nothing
End Sub

以下是Sheet页代码，如下：

Private colCB As New Collection
Private ctlCB As CCB
Private Sub cmd1_Click()
Set ctlCB = New CCB
ctlCB.Init CheckBox1, Me
colCB.Add ctlCB
End Sub

sheet页图片和按扭