VBA——事件（2）

单纯的工作表事件和工作簿事件我在实际运用中代码不知道为什么不是特别的稳定（时灵时不灵）。而事件结合控件运用还是比较实用的。

工作表事件的选择区域改变事件（selectionchange）

GIF2.gif

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$3" Or Target.Address = "$B$3" _
Or Target.Address = "$C$3" Or Target.Address = "$D$3" Then
Target.Value = Target.Value + 1
End If
End Sub

工作表离开事件（Worksheet_Deactivate）

GIF3.gif

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.StatusBar = "当前选择的区域是：" & Target.Address(0, 0)
End Sub
------------------------------------------------------
Private Sub Worksheet_Deactivate()
Application.StatusBar = ""
End Sub

①statusbar状态栏
②target.address(0,0)表示相对引用的地址

• 同样不用target方法用selection也行。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.StatusBar = "当前选择的区域是：" & Selection.Address(0, 0)
End Sub
-------------------------------------------------------------
Private Sub Worksheet_Deactivate()
Application.StatusBar = "当前选择的区域是："
End Sub

防止工作表改名

GIF4.gif

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheet3.Name <> "成绩表" Then Sheet3.Name = "成绩表"
End Sub

限定工作区域与自动写入工作表名称

GIF5.gif

Private Sub Worksheet_Activate()
For Each sht In Sheets
    If sht.Name <> "全年月份" Then
        k = k + 1
        Sheets("全年月份").Cells(k, 1) = sht.Name
    End If
Next
End Sub
---------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, [a1:c12]) Is Nothing Then
    MsgBox "你只能在[a1:c12]区域中工作！"
    [a1].Select
End If
End Sub

①Worksheet_Activate激活事件
②intersect()交集函数