Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
onAction events on NewMenu
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("&A dd Topic").Enabled = False CommandBars(1).Controls("Storyboard").Controls("&E dit Course Info").Enabled = False CommandBars(1).Controls("Storyboard").Controls("&T ools").Enabled = False CommandBars(1).Controls("Storyboard").Controls("&I nsert").Enabled = False CommandBars(1).Controls("Storyboard").Controls("&W ord Count").Enabled = False CommandBars(1).Controls("Storyboard").Controls("&P rint 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("&A dd Topic").Enabled = True CommandBars(1).Controls("Storyboard").Controls("&E dit Course Info").Enabled = True CommandBars(1).Controls("Storyboard").Controls("&T ools").Enabled = True CommandBars(1).Controls("Storyboard").Controls("&I nsert").Enabled = True CommandBars(1).Controls("Storyboard").Controls("&W ord Count").Enabled = True CommandBars(1).Controls("Storyboard").Controls("&P rint Storyboard").Enabled = True End Sub What am I doing wrong? Thanks in advance. Regards T. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
onAction events on NewMenu
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" wrote in message oups.com... 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("&A dd Topic").Enabled = False CommandBars(1).Controls("Storyboard").Controls("&E dit Course Info").Enabled = False CommandBars(1).Controls("Storyboard").Controls("&T ools").Enabled = False CommandBars(1).Controls("Storyboard").Controls("&I nsert").Enabled = False CommandBars(1).Controls("Storyboard").Controls("&W ord Count").Enabled = False CommandBars(1).Controls("Storyboard").Controls("&P rint 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("&A dd Topic").Enabled = True CommandBars(1).Controls("Storyboard").Controls("&E dit Course Info").Enabled = True CommandBars(1).Controls("Storyboard").Controls("&T ools").Enabled = True CommandBars(1).Controls("Storyboard").Controls("&I nsert").Enabled = True CommandBars(1).Controls("Storyboard").Controls("&W ord Count").Enabled = True CommandBars(1).Controls("Storyboard").Controls("&P rint Storyboard").Enabled = True End Sub What am I doing wrong? Thanks in advance. Regards T. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
onAction events on NewMenu
The application event would not work when there were no workbooks.
Regards T. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
onAction events on NewMenu
So you put some code in the BeforeClose that counts the workbooks.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "triaz" wrote in message oups.com... The application event would not work when there were no workbooks. Regards T. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
onAction events on NewMenu
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 Auto_Open() 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
onAction events on NewMenu
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" wrote in message oups.com... 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 Auto_Open() 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
onAction events on NewMenu
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OnAction events intermittently failing on Shapes in Dialogsheets | Excel Discussion (Misc queries) | |||
OnAction? | Excel Programming | |||
NewMenu | Excel Programming | |||
OnAction | Excel Programming | |||
OnAction | Excel Programming |