Excel VBA利用事件对图表自动更新

Private Sub Workbook_SheetActivate(ByVal sh As Object)Dim shrt As WorksheetDim rg As RangeDim rgChartData As RangeDim chrt As ChartOn Error GoTo NO'Application.ScreenUpdating = FalseSet shrt = sh'Application.StatusBar = shrt.Name & "图表自动校正..."'MsgBox (sh.Name)Set rg = shrt.Range("a2")Do Until IsEmpty(rg)    Set rg = rg.Offset(0, 1)Loop    Set rg = rg.Offset(0, -1)    Set rgChartData = shrt.Range("a1: " & rg.Address & ",a4:" & rg.Offset(2, 0).Address)       If shrt.Name = ThisWorkbook.Sheets(2).Name Then    Set chrt = shrt.ChartObjects(1).ChartElse    Set chrt = shrt.ChartObjects(2).ChartEnd IfWith chrt    .SetSourceData rgChartData    .ChartTitle.Text = Left(rgChartData.Cells(1, 1).Value, Len(rgChartData.Cells(1, 1).Value) - 1) & "故障"End WithIf shrt.Name <> ThisWorkbook.Sheets(2).Name Then    Set chrt = shrt.ChartObjects(1).Chart    With chrt        .Axes(xlCategory).MaximumScale = rg.Column    End WithEnd If'Application.ScreenUpdating = TrueExit SubNO: 'Application.StatusBar = shrt.Name & "无图表或者有错！CodeName=" & shrt.CodeNameEnd Sub