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 With
  
  Sh.Range("B2,D2,F2,B4:G4").Font.Bold = True
  
  With 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 = xlContinuous
  
  End With
    
    
    
    
    
End Sub

5．工作表事件

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.Color = xlNone

    Target.EntireRow.Interior.Color = vbCyan
    Target.EntireColumn.Interior.Color = vbCyan
    
End 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 Integer
    Set r = Target.Cells(1, 1)
    
    If r.Row > 3 And (r.Column = 5 Or r.Column = 7) Then
        k = MsgBox("确定跳转?", vbYesNo)
        
        If k = vbYes Then
        
            i = 4
        
            Do While Trim(Cells(i, 2)) <> ""
            
                If Trim(Cells(i, 2)) = Trim(r.Value) Then
                    Cells(i, 2).Select
                End If
            Loop
        
        End If
        
    End If
    
End Sub

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