[RESOLVED]Excel-Triggering Personal Workbook BeforeSave event with any open workbook?

Hi,

I wrote BeforeSave event handler in my Personal Workbook that runs Excel's spellchecker. My goal is to ensure that I do not forget to spellcheck spreadsheets. I was pleased to see that the "spell check complete" dialog doesn't appear when there are no spelling errors in this case, however...

The Personal Workbook's BeforeSave event is only triggered when the Personal Workbook is saved; I can see why this is...

Now I'm looking for a way to accomplish my original goal, any suggestions?

If there is a way to have the Personal Workbook copy the procedure into other opened workbooks when it/they are opened... I'd be interested to know HOW that is done, it sounds like the most difficult approach though.

I've thought about saving an file and trying to use it as a template... Perhaps templates are my best answer, I haven't worked with those much.

Finally, I do not know many details about the network that I am on but I use several different computers on it. Off topic, but if there is a method to use a single Personal Workbook macro across a network (XP), let me know.

Thanks all,

David.

Have you any experience with Class Modules?

Yes, I started on object-oriented programming several months ago with C++... I found it difficult to get going with VBA but I have written a few programs using objects. Nothing too advanced but I sure do notice the difference when it comes to debugging!

The best way to handle events across multiple workbooks is to create your own Event Handler using a Class Module. Using this approach, we will create a reference to the Excel application within the class and then use that object variable to handle any events that we need.

I have attached a workbook showing this in action, you will need to transfer the Class Module and the Auto_Open and Auto_Close proc's to your personal WB.


In the Class Module, which I've called "CExcelHandler", we start by declaring an object variable, with events, to reference the Excel application.

VB Code:

Private WithEvents oXLApp As Excel.Application

Next, in the _Initialize procedure for the Class we instantiate the variable, making sure to destroy the reference in the _Terminate proc.

VB Code:

Private Sub Class_Initialize()
    Set oXLApp = Excel.Application
End Sub
 
Private Sub Class_Terminate()
    Set oXLApp = Nothing
End Sub

Now, still in the Class Module we can write our code for any of the Event Handlers we need. The Events will be listed in the 2 dropdowns at the top of the VBA editor window. Select oXLApp in the first DD and then select the Event proc in the second.
In my example I have chosen the _WindowActivate event as a demonstation of the Event Handler in action. This trivial example merely displays the name of the active WB when a window is selected.

VB Code:

Private Sub oXLApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    MsgBox Wb.Name
End Sub

After you have written all your Event Handlers in the Class Module, we are now ready to move onto a standard module.

First we declare a public instance of the Event Handler Class

VB Code:

Public cEventHandler As CExcelHandler

Then in the Auto_Open proc we instantiate the object variable and destroy it in the auto_close.

VB Code:

Sub auto_open()
    ' Instantiate the Application event handler
    If cEventHandler Is Nothing Then
        Set cEventHandler = New CExcelHandler
    End If
End Sub
 
Sub auto_close()
    Set cEventHandler = Nothing
End Sub

And that's it, now every time one of your handled events occurs the live instance of the Event Handler will trap it.

Here's the demo WB that shows my trivial example in action.
Open 2 or 3 other WB's first and arrange the windows (tiled), and try selecting different windows.
Open the Event Handler and repeat. Now, when you select a different window the EH Class traps that event and displays the MsgBox.

Thanks much DKenny, this has been most helpful to me!

Do you know where I might find a list of Excel event handlers?

For some reason the WorkbookBeforeSave event doesn't seem to trigger when I am saving the workbook while closing Excel. Excel is closing, prompts "do you want to save," I click yes, event handler doesn't get called.

Any ideas what I should use in this case?

Thanks again,

David