VBA Open Event for any Excel workbook - possible? 1

[Fenrirshowl]

Hi everybody

I was wondering if there is a way to set up a piece of VBA code that runs whenever a new workbook is opened in Excel (I am currently using 2007). Or if there definitely isn't then to know there isn't so I stop trying to find a way!

I can't think where it would need to be located and what code is needed for an application event rather than workbook event.

I know you can set up a module and use the Open event for that workbook, but can you run one from say an Add-in, on workbooks which do not have any macros / modules?

My aim is to try to record every workbook opened by people in my dept on a rolling 4 week basis. This is to log where data may have been extracted from, where workbooks used are etc - so providing some sort of basic audit trail for inspection if people are absent and we need to identify what has been done. If taken to extreme it would be similar to using MS Outlook's Journal but as a central log for 20+ people in the team.

Any advice happily received and I am happy to receive pointers and do the rest of the research needed!

Fen

 

[combo]

You need to handle Application event. As excel itself has no class/document module, you need to use your own class module.

The steps:
1. add class module, name it. say, clsApplication
2. add WithEvents declaration as Application, say:
[tt]Public WithEvents xlApp as Application[/tt]
3. now you should be able to create event procedure template, add code to proceed with Wb here:
[tt]Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)

End Sub[/tt]
4. instantiate the class in a module and assign Application to its xlApp variable:
[tt]Dim oApp as clsApplication
Set oApp = New clsApplication
Set oApp.xlApp = Application[/tt]

combo

 

[Fenrirshowl]

Thanks Combo

I must admit to being a total novice with Class modules.
Done step 1 by adding a class module and changing its name under the properties window.
Steps 2 & 3 I took as being simply typing within the module your code plus a debug.print command to get some output

Public WithEvents xlApp As Application

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
Debug.Print Wb.Name
End Sub

(hopefully that is correct)

Step 4 - I admit I don't understand. So am investigating Class Modules and "Instantiate" (new word to me) to see how I get it to work in the background.

Many thanks for the assistance and your time, and for putting me onto something new.

Fen

 

[combo]

A class is a template for object, you need to get an object based on it. So the first line declares an object, at this point it is still Nothing. The second line creates an object based on class description, with oApp name and clsApplication type. The oApp object has still empty xlApp entry (a property of application type, so application events are not yet returned. The third line assigns excel Application to it, now all handled application events are passed here.
To disable events clear the xlApp object:
[tt]Set oApp.xlApp = Nothing[/tt]
You can reset oApp at the end of your code:
[tt]Set oApp = Nothing[/tt]

You may find interesting my faqs concerning events in this forum.


combo

 

[Fenrirshowl]

I will certainly have a look!

 

[Fenrirshowl]

The "Understanding Events" FAQ has sorted it out.

Brilliant - thanks Combo.