用VBA无法截获用户对Excel文件的打印、保存、和关闭事件，在线等立刻给分

[zych72]

兄弟用VBA写了一段代码，打开一个指定的excel文件 ，并且在用户打印、保存、何关闭这个文件之前显示操作的动作，结果在程序中调用
Application.ActiveWorkbook.PrintOut()
Application.ActiveWorkbook.Save()
Application.ActiveWorkbook.Close()
能够触发这些事件
Application.WorkbookBeforePrint()
Application.WorkbookBeforeSave()
Application.WorkbookBeforeClose()
但是用户操作打印，保存，和关闭时并不能触发
请大虾指点，在线等立刻给分
下面是兄弟的代码

[jackystar]

up

[zych72]

'MainModule

Option Explicit
Sub Main()

Dim fh As New ExcelFileHandler
Call fh.openFile("D:\temp\book1.xls")
Call fh.testEvent
MsgBox "Click to Exit"
Set fh = Nothing
End Sub

[zych72]

'ExcelFileHandler

Option Explicit


Private WithEvents objApp As Excel.Application


Private Sub Class_Initialize()
Set objApp = New Excel.Application
End Sub

Private Sub Class_Terminate()
Set objApp = Nothing
End Sub


Public Function openFile(fileName As String) As Boolean

objApp.Workbooks.Open fileName, , False
objApp.Visible = True
End Function

Public Sub testEvent()
objApp.ActiveWorkbook.PrintOut
objApp.ActiveWorkbook.Save
objApp.ActiveWorkbook.Close
End Sub


Private Sub objApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
MsgBox "objApp_WorkbookBeforeClose"
Wb.Saved = True

End Sub

Private Sub objApp_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
MsgBox "objApp_WorkbookBeforePrint"

End Sub

Private Sub objApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUi As Boolean, Cancel As Boolean)

MsgBox "objApp_WorkbookBeforeSave"

End Sub

Private Sub objApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)

MsgBox "objApp_WorkbookNewSheet"

End Sub
Private Sub objApp_NewWorkbook(ByVal Wb As Workbook)

MsgBox "objApp_NewWorkbook"

End Sub

Private Sub objApp_SheetActivate(ByVal Sh As Object)
MsgBox "objApp_SheetActivate"

End Sub

Private Sub objApp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "objApp_WorkbookOpen"

End Sub
Private Sub objApp_AutoOpen(ByVal Wb As Workbook)
MsgBox "objApp_WorkbookOpen"

End Sub

[zych72]

调用testEvent可以出发这些事件处理过程
可以对窗口中的Excel文件进行print,save,close操作时却没有丝毫反应
嘿嘿，真怪

[zych721]

没办法，自己另外注册了个账户
up 以下 :)

[zych72]

怎么没有人给点提示

[zych72]

算了，没有人会，结账

[zych72]

解决，是在完成以上设置后运行
while(not finish)
DoEvents
endW
或者在form_load中设置只要不关闭form就可以触发事件