xltrader100
07-24-2012, 03:37 PM
I have an Add-in that works only with a specific workbook. The add-in stays loaded and waits for it's mate to be opened up. It then does some initialization and they're off. At least, that's what I'd like it to do, but I can't find an Event that fires when an existing workbook is Opened.
I thought I had it here: http://cpearson.com/excel/AppEvent.aspx where the Application Events seem to do exactly what I want, only I can't make it work. The instructions could hardly be clearer, but I still can't detect new workbooks being opened.
Any ideas on what the problem might be? Everything below this is copied directly from that link:
The first step to to insert a new class module into your project and name this class CExcelEvents. In that class module, insert the following code:
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
End Sub
Next, create the event procedures. In the left side drop down list at the top of the code window, choose App and then in the right side drop down list at the top of the code window choose the event you want to use. For example, you can use the WorkbookOpen event to receive an a message when an existing workbook is opened:
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "New Workbook: " & Wb.Name
End Sub
Now, close the CExcelEvents class module and open the ThisWorkbook module. In that module, insert the following code:
Private XLApp As CExcelEvents
Private Sub Workbook_Open()
Set XLApp = New CExcelEvents
End Sub
I thought I had it here: http://cpearson.com/excel/AppEvent.aspx where the Application Events seem to do exactly what I want, only I can't make it work. The instructions could hardly be clearer, but I still can't detect new workbooks being opened.
Any ideas on what the problem might be? Everything below this is copied directly from that link:
The first step to to insert a new class module into your project and name this class CExcelEvents. In that class module, insert the following code:
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
End Sub
Next, create the event procedures. In the left side drop down list at the top of the code window, choose App and then in the right side drop down list at the top of the code window choose the event you want to use. For example, you can use the WorkbookOpen event to receive an a message when an existing workbook is opened:
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "New Workbook: " & Wb.Name
End Sub
Now, close the CExcelEvents class module and open the ThisWorkbook module. In that module, insert the following code:
Private XLApp As CExcelEvents
Private Sub Workbook_Open()
Set XLApp = New CExcelEvents
End Sub