onAction events on NewMenu

[triaz]

Hi,

I have a custom menu that is re-created each time the excel app is
opened. This custom menu is part of an addin.

Since I need my menu to enable and disable menu items depending on
whether a workbook is open, or on other conditions such as the sheet
name, I thought by having a procedure which is triggered by the
NewMenu' onAction event, which would then check against these
conditions before the menu displays, would be perfect.

I tested the procedure before adding to the onAction event and it
worked fine, but when I placed this procedure on the NewMenu' onAction
event nothing worked.

I did have an Application level event which did this but it was erratic
ie did not work when there were no workbooks open.

Here's the code I have:

Sub CreateMenu()
'some code here
With NewMenu
.Caption = "Storyboard"
.onAction = "checkMyStatus"
End With
'menuitems added here ...
End Sub

Sub checkMyStatus()
Call checkWorkbook
Call checkWorksheet
End Sub

Sub checkWorkbook()
On Error Resume Next
If Workbooks.Count < 2 Then
CommandBars(1).Controls("Storyboard").Controls("&Add
Topic").Enabled = False
CommandBars(1).Controls("Storyboard").Controls("&Edit Course
Info").Enabled = False
CommandBars(1).Controls("Storyboard").Controls("&Tools").Enabled =
False
CommandBars(1).Controls("Storyboard").Controls("&Insert").Enabled =
False
CommandBars(1).Controls("Storyboard").Controls("&Word
Count").Enabled = False
CommandBars(1).Controls("Storyboard").Controls("&Print
Storyboard").Enabled = False
Else: Call EnableMenuItems
End If
End Sub

Sub checkWorksheet()
similar to checkWorkbook
End Sub

Sub EnableMenuItems()
On Error Resume Next
CommandBars(1).Controls("Storyboard").Controls("&Add
Topic").Enabled = True
CommandBars(1).Controls("Storyboard").Controls("&Edit Course
Info").Enabled = True
CommandBars(1).Controls("Storyboard").Controls("&Tools").Enabled =
True
CommandBars(1).Controls("Storyboard").Controls("&Insert").Enabled =
True
CommandBars(1).Controls("Storyboard").Controls("&Word
Count").Enabled = True
CommandBars(1).Controls("Storyboard").Controls("&Print
Storyboard").Enabled = True
End Sub

What am I doing wrong?

Thanks in advance.

Regards

T.

 

[Bob Phillips]

Surely, you have to have application events to handle this. Your approach
needs the menu item to be clicked when anything changes, not good.

What problems did you find with app events?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[triaz]

The application event would not work when there were no workbooks.

Regards

T.

 

[Bob Phillips]

So you put some code in the BeforeClose that counts the workbooks.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[triaz]

I have a class module with the following code amongst other things:

Private WithEvents xlApp As Excel.Application

Private Sub Class_Initialize()
Set xlApp = Excel.Application
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal wb As Workbook, Cancel As
Boolean)
Call CreateMenu
Call CheckMenu
End Sub

Private Sub xlApp_NewWorkbook(ByVal wb As Workbook)
Call CreateMenu
Call CheckMenu
End Sub

Private Sub xlApp_WorkbookActivate(ByVal wb As Workbook)
Call CreateMenu
Call CheckMenu
End Sub

Private Sub xlApp_WorkbookDeactivate(ByVal wb As Workbook)
Call CreateMenu
Call CheckMenu
End Sub

Private Sub xlApp_WorkbookOpen(ByVal wb As Workbook)
Call CreateMenu
Call CheckMenu
End Sub

In my standard module I have:

Dim objXLEvents As classApps


Public Sub Autpen()
Set objXLEvents = New classApps
End Sub

Public Sub Auto_Close()
Set objXLEvents = New classApps
End Sub

When I start the excel application or open a new workbook, the menu
works as planned, the problem is that it doesn't work when I close all
open workbooks.

What am I doing wrong?

Regards

T.

 

[Chip Pearson]

Are you sure the WorkbookBeforeClose event isn't running? Put a
break point on the first line of code. It seems to me that you
would want different code (e.g., a DeleteMenu procedure) when
closing than when opening.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[triaz]

Hi Chip,

The WorkbookBeforeClose event is running and is part of the addin (in
fact all the above code is in the addin), the problem is, how do I test
for the condition that when the current workbook is closed the only
remaining workbook is the addin (which is not active but hidden).

At the moment when the test is performed in WorkbookBeforeClose the
workbook that triggers this is still open, hence WorkbookBeforeClose.

Does this makes sense? or am I plunging to the depths of gibberish?

Thanks for any feedback.

Regards

Tal.