Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making menu item changes temporary
When I add a custom menu item using the Add method I know
I can make it temporary by setting the Temporary property to True. This is good because I know when my Excel Application Unloads that is it, they don't show up in other workbooks. I need to make some custom changes to Excel Menus (not custom) though like setting the .OnAction property for the "EditDelete Sheet" option to point to my own code. I know how to do this but it affects not only my own application but also all other spreadsheets that are opened. I know I can use the Reset method when I close the application but if for some reason it crashes and the reset is run there is potential for sheets other sheets to be affected and passed around and before you know it.....a mess!! So how can I make the change temporary to begin with? Is it possible? Thanks in advance! RJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making menu item changes temporary
You could do something like this to get the Edit|Delete Sheet control:
Option Explicit Sub testme01() With Application.CommandBars("worksheet menu bar") _ .Controls("edit").Controls("Delete sheet") .OnAction = ThisWorkbook.Name & "!Testme99" End With End Sub And reset it with: Application.CommandBars("worksheet menu bar") _ .Controls("edit").Controls("Delete sheet").Reset But you might miss a few controls (like the one when you right click on the worksheet tab). When I looked at the id for this control, it looked like they were all 847. Sub testme33() Call ModDelete(myReset:=False) 'true to reset it back to normal. End Sub Sub ModDelete(myReset As Boolean) Dim CmdBar As CommandBar Dim myCtrl As CommandBarControl Dim iCtr As Long Dim myId As Variant myId = Array(847) For Each CmdBar In Application.CommandBars For iCtr = LBound(myId) To UBound(myId) Set myCtrl = CmdBar.FindControl(ID:=myId(iCtr), recursive:=True) If Not myCtrl Is Nothing Then If myReset Then myCtrl.Reset Else myCtrl.OnAction = ThisWorkbook.Name & "!testme99" End If End If Next iCtr Next End Sub If you find any more "deletes", you can just add them to the myid = array() line. Randy Johnson wrote: When I add a custom menu item using the Add method I know I can make it temporary by setting the Temporary property to True. This is good because I know when my Excel Application Unloads that is it, they don't show up in other workbooks. I need to make some custom changes to Excel Menus (not custom) though like setting the .OnAction property for the "EditDelete Sheet" option to point to my own code. I know how to do this but it affects not only my own application but also all other spreadsheets that are opened. I know I can use the Reset method when I close the application but if for some reason it crashes and the reset is run there is potential for sheets other sheets to be affected and passed around and before you know it.....a mess!! So how can I make the change temporary to begin with? Is it possible? Thanks in advance! RJ -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making menu item changes temporary
Thanks Dave,
I have looked at that option but if the application crashes for any reason the Reset function never gets a chance to do its work and a possible mess could occur. That is why I wonder if actual property changes could be temporary like the custom options I mentioned. Best Regards, Randy *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making menu item changes temporary
Couldn't use the application.beforecrash event (or the equivalent windows
API)??? I get it now. I think that my suggestion would be to tell the user that if excel/windows crashed, they should open that workbook and close it. Have your auto_close (workbook_beforeclose) code restore the toolbars. If you gave them a workbook that reset these, then they might use it when you didn't want them to. Maybe if you gave them a nice toolbar with your functions on them, they'd find that more useful and you wouldn't have to even reassign the functions. I don't have any better ideas. Randy wrote: Thanks Dave, I have looked at that option but if the application crashes for any reason the Reset function never gets a chance to do its work and a possible mess could occur. That is why I wonder if actual property changes could be temporary like the custom options I mentioned. Best Regards, Randy *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
windows menu item | Excel Discussion (Misc queries) | |||
Menu item disabled | Excel Worksheet Functions | |||
keep new menu item with workbook | Excel Discussion (Misc queries) | |||
How to delete item on right click menu? | Excel Discussion (Misc queries) | |||
Menu items added with menu item editor in older versions | Excel Discussion (Misc queries) |