Auto Activate (Window_Open Event) in Personal.xls when another workbook is loaded

**Nap** · 03-23-2006, 01:47 AM

Hi everyone,

Before I explain my problem, here is some background info:

I use a formatted excel spreadsheet as an invoice which I send to customers via email.
The invoice number in the spreadsheet is the same as the name of the file,
To date, I've been performing all operations manually (Invoice number and spreadsheet name),
I've decided that I want to automatically fill in the Invoice number in the spreadsheet from the name of the file, and have worked out a macro that will do it using the WINDOW_OPEN event.

So everything works fine except that my customer must click 'enable macros' when they open my invoice. My goal is therefore to eliminate this requirement.

I moved the code I developed to achieve the above into Personal.xls (which is hidden) and thought it might work. Well, it didn't. The problem is that when I double click the invoice spreadsheet in Windows Explorer, Excel opens the Personal.xls file first, and then opens the file I actually want to edit.

Therefore my code runs when the WINDOW_OPEN event is fired upon loading of Personal.xls, but since my target file is not yet loaded, I don't get what I want. Thus I need to fire the event a second time after the Invoice is loaded, but I don't know how to achieve this.

So my question is, how do I setup my macro in the Personal.xls file to:
1) Execute automatically after the target spreadsheet is loaded,
2) Make the required alterations in the target spreadsheet (not Personal.xls). [This I can workout myself]

This is the code (in Personal.xls) that checks if the opened spreadsheet is an invoice, and if it is, updates the invoice number:
(I know that I will need to index through the opened workbooks once the Invoice is loaded)

Private Sub Workbook_Open()

Dim InvoiceName As String

If Application.ActiveWindow Is Nothing Then
  ' do nothing
Else
  InvoiceName = Application.ActiveWorkbook.Name
  If Left(InvoiceName, 3) = "INV" Then
    InvoiceName = Mid(InvoiceName, 4, Len(InvoiceName) - 7)
    Application.ActiveWorkbook.ActiveSheet.Cells(14, 11) = InvoiceName
  End If
End If

End Sub

I have search the google groups, but have not been able to find an answer as yet. I have added a class module to Personal.xls hoping to trap the OPEN event but don't know what I should code to process the event correctly.
I have used the following line to expose the events but don't know what's next