Using Application Events
 

I am trying to use the Application events to check what sheets are being
activated by the user and then execute code accordingly. While this seems
to work fine on my PC, when I create an add-in and load it on another PC it
does not necessarily work (in some cases it does, in others it doesn't). In
those cases where it does not work the whole event is ignored. Anybody with
any experience on this?

The code I use is as follows:

'In the ThisWorkbook module
Dim WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
'code
End Select
End Sub

Using Application Events
 

This is what you should so

Firstly, all of this code goes in the designated workbook.

'========================================
Insert a class module, rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
With Wb.ActiveSheet
.PageSetup.LeftFooter = "some text"
End With
End Sub

'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Thomas Herr" wrote in message
...
I am trying to use the Application events to check what sheets are being
activated by the user and then execute code accordingly. While this seems
to work fine on my PC, when I create an add-in and load it on another PC
it
does not necessarily work (in some cases it does, in others it doesn't).
In
those cases where it does not work the whole event is ignored. Anybody
with
any experience on this?

The code I use is as follows:

'In the ThisWorkbook module
Dim WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
'code
End Select
End Sub

Using Application Events
 

Your "event ignoring machines" may need to have the Events
handler activated try adding --- Lines below:
Dim WithEvents App As Application

---Application.EnableEvents = True

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
'code
End Select

---Application.EnableEvents = False
---ActiveWorkbook.Save

End Sub


BAC


-----Original Message-----
I am trying to use the Application events to check what
sheets are being
activated by the user and then execute code accordingly.
While this seems
to work fine on my PC, when I create an add-in and load
it on another PC it
does not necessarily work (in some cases it does, in
others it doesn't). In
those cases where it does not work the whole event is
ignored. Anybody with
any experience on this?

The code I use is as follows:

'In the ThisWorkbook module
Dim WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
'code
End Select
End Sub


.