VBA提高篇_ 22 事件处理

1.事件编程

写在事件发生地(对应工作簿或工作表)

2.常用工作簿事件名称与对应处理过程名称示例

3. 事件编程的步骤

1. 找到该事件所在对象,双击打开代码页
2. 按照ＶＢＡ规定，为该过程正确命名

4．工作簿事件

4.1 Open

4.2 BeforeClose

Private Sub Workbook_BeforeClose(Cancel As Boolean)MsgBox "别忘记备份数据! 再见"
End Sub

4.3 NewSheet

Private Sub Workbook_NewSheet(ByVal Sh As Object)Sh.Range("B2") = "工号"Sh.Range("D2") = "姓名"Sh.Range("F2") = "年龄"With Sh.Range("B4") = "参与项目".Range("C4") = "主要职责".Range("D4") = "有效工时".Range("E4") = "业绩评价".Range("F4") = "进入日期".Range("G4") = "退出日期"End WithSh.Range("B2,D2,F2,B4:G4").Font.Bold = TrueWith Sh.Range("B4:G30").Borders(xlEdgeLeft).LineStyle = xlContinuous.Borders(xlEdgeTop).LineStyle = xlContinuous.Borders(xlEdgeBottom).LineStyle = xlContinuous.Borders(xlEdgeRight).LineStyle = xlContinuous.Borders(xlInsideVertical).LineStyle = xlContinuous.Borders(xlInsideHorizontal).LineStyle = xlContinuousEnd WithEnd Sub

5．工作表事件

SelectionChange：每当一个用户选中一个新的单元格的时候，
SelectionChange就会被触发（Target刚被选中的单元格对象）
Range．EntireRow／EntireColumn（整行或整列）
封装事件代码：工作表都可以调用　／　透明色　：XlNone

Private Sub Worksheet_SelectionChange(ByVal Target As Range)Cells.Interior.Color = xlNoneTarget.EntireRow.Interior.Color = vbCyanTarget.EntireColumn.Interior.Color = vbCyanEnd Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)Call 模块1.highlight1(Target)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim r As Range, i As Long, k As IntegerSet r = Target.Cells(1, 1)If r.Row > 3 And (r.Column = 5 Or r.Column = 7) Thenk = MsgBox("确定跳转?", vbYesNo)If k = vbYes Theni = 4Do While Trim(Cells(i, 2)) <> ""If Trim(Cells(i, 2)) = Trim(r.Value) ThenCells(i, 2).SelectEnd IfLoopEnd IfEnd IfEnd Sub

6．变量和过程函数的作用域