Fire NewWorkbook Event (Excel 2003)

Topic

New Reply

I know I’ve done this before, but for the life of me, I can’t figure it out now!

I am building an Excel app as a template. I have a button in the template labelled “New File” which should create a new workbook based on the same template. When the new workbook is created, I want the NewWorkbook event to fire.

I have created a new class called clsEvents with this code:

Public WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
‘this never fires
MsgBox “new workbook event fired”
End Sub

The problem is that I don’t know where to place the global variable to hold a pointer to my clsEvents.App object. Right now its in a module called Main:

Dim AppObject As New clsEvents

Sub Init()
Set AppObject.App = Application
End Sub

…. but where should I call the Init() from?? Right now I’m calling it from ThisWorkBook.Workbook_Open.

Reply | Quote

Replies

Are you sure you that want the NewWorkbook event to fire? It it works, it would fire for EACH new workbook the user creates, unless you build in extra checks. Since you already have a command button that the user clicks to create a new workbook, you can call the code from there.
And are you sure that you want to do all this in the template? A new workbook based on a template in Excel is a straight copy – all code from the template is copied into the workbook, so you’d end up with multiple instances of the event code. Perhaps an add-in or a workbook in XLSTART would be better.

Reply | Quote

Thanks Hans – I’ll re-think how I’m doing this.

Reply | Quote