全民一起VBA提高篇第六课：事件编程

事件编程

事件编程要写在事件的发生地，放在事件编码中

Private Sub Workbook_Open()
'对应子过程名写好
	Dim i As Integer
	Randomize
	i = Rnd()
	'如果随机数i在0到0.5之间，则随机生成一个1到3之间的中奖号码
	If i < 0.5 Then
	    Range("c7").Value = Int(Rnd() * 3) + 1   
	Else    '否则，随机生成一个4到100之间的中奖号码
	    Range("c7").Value = Int(Rnd() * 97) + 4
	End If	
	MsgBox Range("c7")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
	MsgBox "你关不掉这个文件的哈哈"
	Cancel = True
End Sub

用在新建上

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") = "参与项目"
	End With
	sh.Range("B2,D2,F2,B4:G4").Font.Bold = True
	'通过格式设定，减少工作量，每次新建都可以用设定好的表
End Sub

做一个股票的高亮十字架

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Cells.Interior.Color = xlNone
    'xlnone是透明的意思
    Target.EntireRow.Interior.Color = vbCyan
    '取整行和整列
    Target.EntireColumn.Interior.Color = vbCyan
End Sub

对象+模块的写法
sheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call 模块3.十字高亮(Target)
End Sub

模块中

Sub 十字高亮(Target As Range)
    Cells.Interior.Color = xlNone
    'xlnone是透明的意思
    Target.EntireRow.Interior.Color = vbCyan
    '取整行和整列
    Target.EntireColumn.Interior.Color = vbCyan
End Sub

自动实现跳转

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim r As Range, i As Long
    Set r = Target.Cells(1, 1)
    '当选中的是一块是，只取左上角的值
    
    If r.Row > 3 And (r.Column = 4 Or r.Column = 3) 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
                    Exit Do
                End If                
                i = i + 1
            Loop
        End If
    End If
       
End Sub

避免产生事件级联

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
	If Not IsNumeric(Target) Or Target > 100 Or Target < 5 Then
	    MsgBox "要是5-100之间的数字"
	    Target.Select
	    Application.EnableEvents = False
	    '暂停响应，防止事件级联
	    Target = "待输入"
	    Application.EnableEvents = True
    End If
End Sub