ercanyuz
03-19-2008, 01:40 AM
Hi everyone,
I wrote a small .xla add-in for Excel, which puts a command bar on top, and provides several buttons that do some tasks on the workbook that are frequently needed in our office.
At some point, I needed to catch the event of a newly activated sheet, because my add-in will do something upon this event (namely, change the state of one of the buttons on its own command bar)
After some reading and searching, I managed to accomplish the task, however not completely. I first want to copy my code. The code has some tags in it, in order to make it easy to follow the flow and find out where the error lies.
Here is the class module in the xla, which handles the events of the application:
' Class name is EventClassModule
Public WithEvents App As Application
Private Sub Class_Initialize()
MsgBox "Event Class initialized" ' Just to follow
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "WB Opened: " & Wb.Name ' Just to follow
End Sub
Private Sub App_SheetActivate(ByVal Sh As Object)
' Unpress the button if sheet selection changes
App.CommandBars(toolbarName).Controls(3).State = msoButtonUp
MsgBox "A new sheet is activated"
End Sub
Below is the ThisWorkbook module of the xla:
Option Explicit
Dim eventInstance As New EventClassModule
' Events of this workbook
Private Sub Workbook_Open()
MsgBox "xla Workbook opened"
Set eventInstance.App = Application
End Sub
Private Sub Workbook_AddinInstall()
Run ("AddCommandBar")
End Sub
Private Sub Workbook_AddinUninstall()
Run ("RemoveCommandBar")
End Sub
There is of course my standard module which has the functions that act on the workbook upon button clicks, but I don't think that code is relevant in this context.
So, this is it. Quite small and simple, right? But its behaviour is very inconsistent. I install it and the command bar gets set up correctly. When I open a new excel instance from programs menu, the output comes in this order:
1. "xla Workbook opened"
2. "Event Class initialized"
3. "WB Opened: MyAddIn.xla"
4. "WB Opened: Mappe1" (Because my excel is German :) )
The expected behaviour beyond that point would be to see the message "A new sheet is activated" whenever I click on the tabs of the sheets. But this doesn't happen. However, when I open a blank workbook, the message "WB Opened: MappeN" still pops up. That means, I can catch the WorkbookOpen event, but not the SheetActivate event. Very strange.. As far as I found out from debugging so far, I anyhow lose the App object soon after the a new workbook is opened.
I must confess that I occasionally saw this "A new sheet is activated" message, but now I can't regenerate that situation now. It seems to be just random, occasionally it works, but mostly not. (of course there is a reason for it that I cannot see)
For example, it works if I manually re-execute the Workbook_Open event handler in ThisWorkbook module of the xla after I open Excel (Alt+F11 and then F8 inside the function). But I still don't know how to make it work itself. Does that tell anything to you?
So, I would greatly appreciate your comments on this.
Cheers!
Ercan
I wrote a small .xla add-in for Excel, which puts a command bar on top, and provides several buttons that do some tasks on the workbook that are frequently needed in our office.
At some point, I needed to catch the event of a newly activated sheet, because my add-in will do something upon this event (namely, change the state of one of the buttons on its own command bar)
After some reading and searching, I managed to accomplish the task, however not completely. I first want to copy my code. The code has some tags in it, in order to make it easy to follow the flow and find out where the error lies.
Here is the class module in the xla, which handles the events of the application:
' Class name is EventClassModule
Public WithEvents App As Application
Private Sub Class_Initialize()
MsgBox "Event Class initialized" ' Just to follow
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "WB Opened: " & Wb.Name ' Just to follow
End Sub
Private Sub App_SheetActivate(ByVal Sh As Object)
' Unpress the button if sheet selection changes
App.CommandBars(toolbarName).Controls(3).State = msoButtonUp
MsgBox "A new sheet is activated"
End Sub
Below is the ThisWorkbook module of the xla:
Option Explicit
Dim eventInstance As New EventClassModule
' Events of this workbook
Private Sub Workbook_Open()
MsgBox "xla Workbook opened"
Set eventInstance.App = Application
End Sub
Private Sub Workbook_AddinInstall()
Run ("AddCommandBar")
End Sub
Private Sub Workbook_AddinUninstall()
Run ("RemoveCommandBar")
End Sub
There is of course my standard module which has the functions that act on the workbook upon button clicks, but I don't think that code is relevant in this context.
So, this is it. Quite small and simple, right? But its behaviour is very inconsistent. I install it and the command bar gets set up correctly. When I open a new excel instance from programs menu, the output comes in this order:
1. "xla Workbook opened"
2. "Event Class initialized"
3. "WB Opened: MyAddIn.xla"
4. "WB Opened: Mappe1" (Because my excel is German :) )
The expected behaviour beyond that point would be to see the message "A new sheet is activated" whenever I click on the tabs of the sheets. But this doesn't happen. However, when I open a blank workbook, the message "WB Opened: MappeN" still pops up. That means, I can catch the WorkbookOpen event, but not the SheetActivate event. Very strange.. As far as I found out from debugging so far, I anyhow lose the App object soon after the a new workbook is opened.
I must confess that I occasionally saw this "A new sheet is activated" message, but now I can't regenerate that situation now. It seems to be just random, occasionally it works, but mostly not. (of course there is a reason for it that I cannot see)
For example, it works if I manually re-execute the Workbook_Open event handler in ThisWorkbook module of the xla after I open Excel (Alt+F11 and then F8 inside the function). But I still don't know how to make it work itself. Does that tell anything to you?
So, I would greatly appreciate your comments on this.
Cheers!
Ercan