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