Code for App level event (not firing)

[Chris W]

This is the code im my class module called:
EventClassModule

Public WithEvents App As Application

Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
Dim cnt As Integer, X As Integer
If ActiveWorkbook Is Nothing Then
cnt = Application.CommandBars("Worksheet Menu
Bar").Controls.Count
For X = 1 To cnt
If Application.CommandBars("Worksheet Menu
Bar").Controls(X).Caption = "&Rolodex Tools" Or _
Application.CommandBars("Worksheet Menu
Bar").Controls(X).Caption = "&Rolodex Tools*" Then
Application.CommandBars("Worksheet Menu
Bar").Controls(X).Enabled = False
Exit Sub
End If
Next X
End If
End Sub
--------------------------------------------------------

This is the subroutine placed in a standard module (this
is called from the ThisWorkbook > Workbook_Open() event of
Personal.XLS.

Dim EvntCl As New EventClassModule

Sub InitializeApp()
Set EvntCl.App = Application
End Sub
----------------------------------------------------------

 

[Gary Brown]

Try putting the Set command in the ThisWorkbook Workbook_Open() event of
Personal.XLS.

Private Sub Workbook_Open()
Set EvntCl.App = Application

HTH,
Gary Brown

 

[Tushar Mehta]

How do you conclude that it is not firing?

Have you considered inserting an unconditional 'MsgBox' statement in
App_WorkbookDeactivate to see if the code executes?

Also, what is "Rolodex Tools*" supposed to be? If it meant to be a
wild card search, VBA's If statement doesn't support that capability.

Finally, instead of looping through all the controls, consider the
direct approach:
commandbars("worksheet menu bar").Controls("Rolodex Tools").enabled= _
not activeworkbook is nothing
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions