Code for App level event (not firing)

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
----------------------------------------------------------

>-----Original Message-----
>It would be useful if you posted the code you are using.
>
>
>--
>Cordially,
>Chip Pearson
>Microsoft MVP - Excel
>Pearson Software Consulting, LLC
>www.cpearson.com
>
>
>
>"Chris w." <[email protected]> wrote in
message
>news:[email protected]...
>> Excel level Event not firing. I followed the directions
>> given in Excel Help to initialize the App level events.
>> But the event is not firing. I created a class object
for
>> the Application object. I put an initialize subroutine
>> code in a workbook level module, and I call it in the
>> openworkbok event. All of these are in Personal.xls.
Can
>> someone give me assistance. Is there a scope problem?
>> Please help.

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


"Chris W" <[email protected]> wrote in message
news:[email protected]...
> 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
> ----------------------------------------------------------
>
>>-----Original Message-----
>>It would be useful if you posted the code you are using.
>>
>>
>>--
>>Cordially,
>>Chip Pearson
>>Microsoft MVP - Excel
>>Pearson Software Consulting, LLC
>>www.cpearson.com
>>
>>
>>
>>"Chris w." <[email protected]> wrote in
> message
>>news:[email protected]...
>>> Excel level Event not firing. I followed the directions
>>> given in Excel Help to initialize the App level events.
>>> But the event is not firing. I created a class object
> for
>>> the Application object. I put an initialize subroutine
>>> code in a workbook level module, and I call it in the
>>> openworkbok event. All of these are in Personal.xls.
> Can
>>> someone give me assistance. Is there a scope problem?
>>> Please help.

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

In article <[email protected]>,
[email protected] says...
> 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
> ----------------------------------------------------------
>
> >-----Original Message-----
> >It would be useful if you posted the code you are using.
> >
> >
> >--
> >Cordially,
> >Chip Pearson
> >Microsoft MVP - Excel
> >Pearson Software Consulting, LLC
> >www.cpearson.com
> >
> >
> >
> >"Chris w." <[email protected]> wrote in
> message
> >news:[email protected]...
> >> Excel level Event not firing. I followed the directions
> >> given in Excel Help to initialize the App level events.
> >> But the event is not firing. I created a class object
> for
> >> the Application object. I put an initialize subroutine
> >> code in a workbook level module, and I call it in the
> >> openworkbok event. All of these are in Personal.xls.
> Can
> >> someone give me assistance. Is there a scope problem?
> >> Please help.
>