VBA 工作簿workbook常用的事件，比如聚光灯，禁止保存，禁止关闭EXCEL，禁止打印，监测内容变化等等

1 工作簿workbook 常用的事件

1.1 常用事件

• workbook_open()
• workbook_WindowActivate()
• workbook_Windowresize()
•  
• workbook_sheetchange()
• workbook_sheetselectionchange()
•  
• workbook_beforesave()
• workbook_beforeclose()
• workbook_beforeprint()

 

1.1 左边是对象选择器，右边是 对象.事件选择器

 

2 workbook的常用事件举例

2.1 workbook_open()

Private Sub Workbook_Open()
Debug.Print "wb is open"
End Sub

 

2.2 workbook_WindowActivate()

• Workbook_WindowActivate(ByVal Wn As Window)
• 参数wn
• wn.windowstate=xlmaximized
• wn.windowstate=xlminimized
• wn.width=100
• wn.name? 这个好像不行

 

• 只有切换不同的wb时才触发，当前wb一直active是不触发的
• 比如 workbook 和其他程序切换时，不会触发

 

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Debug.Print "window is active now"
Debug.Print "只有切换wb窗口时才触发，当前wb一直active是不触发的"

End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Debug.Print "window is active now"
Debug.Print "只有切换wb窗口时才触发，当前wb一直active是不触发的"
Wn.WindowState = xlMaximized

End Sub

 

 

2.3 workbook_WindowResize()

•  只有调整workbook里面的尺寸才变化，而调整整个excel窗口大小不会触发

Private Sub Workbook_WindowResize(ByVal Wn As Window)
Debug.Print "window is resized"
Debug.Print "只有调整workbook里面的尺寸才变化，而调整整个excel窗口大小不会触发"
End Sub

 

2.4 workbook_beforeSave()

• Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
• 自带参数  saveasui 好像没啥用
• cancel 是取消保存参数

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
x1 = MsgBox("您要保存您的修改吗，还是要放弃？", vbYesNo, "是否保存修改")
If x1 = vbNo Then
   Cancel = True
ElseIf x1 = vbYes Then
   ThisWorkbook.Save
   Debug.Print "now is saving"
End If


End Sub

 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

x1 = MsgBox("您要保存您的修改吗，还是要放弃？", vbYesNo, "是否保存修改")
If x1 = vbNo Then
   Cancel = True
   Debug.Print "用户放弃保存"
ElseIf x1 = vbYes Then
   SaveAsUI = False
   ThisWorkbook.Save
   Debug.Print "now is saving"
End If


End Sub

 

比如这样就永远无法正常保存了

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Cancel = True

End Sub

 

2.5 workbook_beforeClose()

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "wb is closing"

If ThisWorkbook.Saved = False Then
   ThisWorkbook.Save
   MsgBox "保存前检测到有内容变化，已经额外保存，再关闭"
Else
   MsgBox "保存前检测到无内容变化，不需要额外保存即关闭"
End If

End Sub

 

如果这样，就无法正常关闭工作簿

• 因为现在只开了一个工作簿
• 不让关闭这个工作簿，也就相当于无法退出EXCEL

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Cancel = True
End Sub

 

Sub 单独写的退出()
Application.EnableEvents = False
ActiveWorkbook.Close
Application.EnableEvents = True
End Sub

 

 

2.7 workbook_sheetchange()    监测内容变化

• "只要wb里任意一个sheet内容里的cells变化都会触发"
• 比 worksheet的 change() 更好，更通用。因为不需要每个 worksheet单独写一份 change的 事件代码

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Debug.Print "Workbook_SheetChange触发"
Debug.Print "只要wb里任意一个sheet内容里的cells变化都会触发"
End Sub

 

2.8  workbook_sheetselectionchange()   监测选择变化

• 这几个事件都是默认带参数的
• 参数应该就是选择的范围
• 代码1，没用这些参数也OK

利用 sheetselectionchage()做聚光灯

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Application.ScreenUpdating = False
Cells.Interior.ColorIndex = -4142

For Each r1 In Selection.Rows
    Rows(r1.Row).Interior.ColorIndex = 38
Next

For Each c1 In Selection.Columns
    Columns(c1.Column).Interior.ColorIndex = 38
Next

Application.ScreenUpdating = True


End Sub

聚光灯效果加强版，可以使用 target参数

• tarfet参数已经默认为 range 

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


Application.ScreenUpdating = False
Cells.Interior.ColorIndex = -4142

For Each r1 In Selection.Rows
    Rows(r1.Row).Interior.ColorIndex = 38
Next

For Each c1 In Selection.Columns
    Columns(c1.Column).Interior.ColorIndex = 38
Next

Application.ScreenUpdating = True


Target.Interior.ColorIndex = 5

End Sub

 

2.9 WindowDeactivate

• 没测出来管用
• wb也属于 window的一种

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Debug.Print "thisworkbook is deactivate"
End Sub

 

 

2.10  打印 BeforePrint

• BeforePrint(Cancel As Boolean)
• cancel=true   可以禁止打印

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub