Toolbar with Macros
I'm so confused!!!
Some history... I have a workbook that I created maybe eight years ago, that has some simple macros that are run ONLY from this workbook, and are accessed by clicking on Tools menu, where the macros are listed at the bottom of the normal list. This macros (and the items on the Tools menu) are ONLY available for this particular workbook. I have tried to duplicate this action in two ways: 1. I have simply dragged an item from the toolbar customize box to the Tools menu, and this works fine, but then it is present on any workbook I open from then on, and of course, I only want it to be present for this workbook. 2. I have saved the original workbook as a new workbook, then deleted everything in the new workbook, including one of the Tools Menu items, and reassigned new macros to the Tools Menu items. I save this new workbook, close it, reopen it, and the Tools menu looks just as it did without saving it. What I want to do is create macros, assign them to a toolbar (especially if it is a Tools menu item), and make these menu items (or toolbar buttons) available ONLY to this workbook. I am having no luck accomplishing this. Can anyone explain why I'm having these problems? Thanks, Bernie |
Toolbar with Macros
One way to place a item in the tools menu
Run the macros in this events in the Thisworkbook module for example Private Sub Workbook_Activate() MenuBar_Item_Item End Sub Private Sub Workbook_Deactivate() MenuBar_Item_Item_Delete End Sub Sub MenuBar_Item_Item() Dim MenuItem As CommandBarControl MenuBar_Item_Item_Delete Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu If MenuItem Is Nothing Then Exit Sub With MenuItem.Controls.Add(msoControlButton, 1, , , True) .Caption = "&Ron de Bruin" .OnAction = ThisWorkbook.Name & "!TestMacro" .BeginGroup = True .Tag = "MenuItemTag" End With Set MenuItem = Nothing End Sub Sub MenuBar_Item_Item_Delete() Dim MenuItem As CommandBarControl Set MenuItem = Application.CommandBars.FindControl(Tag:="MenuItem Tag") If Not MenuItem Is Nothing Then MenuItem.Delete End If Set MenuItem = Nothing End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "bw" wrote in message ... I'm so confused!!! Some history... I have a workbook that I created maybe eight years ago, that has some simple macros that are run ONLY from this workbook, and are accessed by clicking on Tools menu, where the macros are listed at the bottom of the normal list. This macros (and the items on the Tools menu) are ONLY available for this particular workbook. I have tried to duplicate this action in two ways: 1. I have simply dragged an item from the toolbar customize box to the Tools menu, and this works fine, but then it is present on any workbook I open from then on, and of course, I only want it to be present for this workbook. 2. I have saved the original workbook as a new workbook, then deleted everything in the new workbook, including one of the Tools Menu items, and reassigned new macros to the Tools Menu items. I save this new workbook, close it, reopen it, and the Tools menu looks just as it did without saving it. What I want to do is create macros, assign them to a toolbar (especially if it is a Tools menu item), and make these menu items (or toolbar buttons) available ONLY to this workbook. I am having no luck accomplishing this. Can anyone explain why I'm having these problems? Thanks, Bernie |
Toolbar with Macros
Ron,
I haven't tried your suggestion yet. But this seems very confusing and a lot of effort to do what I have asked. I didn't have to go through all this when I placed these items on the Tools menu eight years ago, so I don't know why I have to now? I'm clearly missing something important here, but I'm not sure what... Bernie On Wed, 30 Jul 2003 18:38:58 +0200, "Ron de Bruin" wrote: One way to place a item in the tools menu Run the macros in this events in the Thisworkbook module for example Private Sub Workbook_Activate() MenuBar_Item_Item End Sub Private Sub Workbook_Deactivate() MenuBar_Item_Item_Delete End Sub Sub MenuBar_Item_Item() Dim MenuItem As CommandBarControl MenuBar_Item_Item_Delete Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu If MenuItem Is Nothing Then Exit Sub With MenuItem.Controls.Add(msoControlButton, 1, , , True) .Caption = "&Ron de Bruin" .OnAction = ThisWorkbook.Name & "!TestMacro" .BeginGroup = True .Tag = "MenuItemTag" End With Set MenuItem = Nothing End Sub Sub MenuBar_Item_Item_Delete() Dim MenuItem As CommandBarControl Set MenuItem = Application.CommandBars.FindControl(Tag:="MenuItem Tag") If Not MenuItem Is Nothing Then MenuItem.Delete End If Set MenuItem = Nothing End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "bw" wrote in message news:1105_1059582525 @news.cableone.net... I'm so confused!!! Some history... I have a workbook that I created maybe eight years ago, that has some simple macros that are run ONLY from this workbook, and are accessed by clicking on Tools menu, where the macros are listed at the bottom of the normal list. This macros (and the items on the Tools menu) are ONLY available for this particular workbook. I have tried to duplicate this action in two ways: 1. I have simply dragged an item from the toolbar customize box to the Tools menu, and this works fine, but then it is present on any workbook I open from then on, and of course, I only want it to be present for this workbook. 2. I have saved the original workbook as a new workbook, then deleted everything in the new workbook, including one of the Tools Menu items, and reassigned new macros to the Tools Menu items. I save this new workbook, close it, reopen it, and the Tools menu looks just as it did without saving it. What I want to do is create macros, assign them to a toolbar (especially if it is a Tools menu item), and make these menu items (or toolbar buttons) available ONLY to this workbook. I am having no luck accomplishing this. Can anyone explain why I'm having these problems? Thanks, Bernie |
Toolbar with Macros
You posted in Programming so I give you a macro solution.
-- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "bw" wrote in message ... Ron, I haven't tried your suggestion yet. But this seems very confusing and a lot of effort to do what I have asked. I didn't have to go through all this when I placed these items on the Tools menu eight years ago, so I don't know why I have to now? I'm clearly missing something important here, but I'm not sure what... Bernie On Wed, 30 Jul 2003 18:38:58 +0200, "Ron de Bruin" wrote: One way to place a item in the tools menu Run the macros in this events in the Thisworkbook module for example Private Sub Workbook_Activate() MenuBar_Item_Item End Sub Private Sub Workbook_Deactivate() MenuBar_Item_Item_Delete End Sub Sub MenuBar_Item_Item() Dim MenuItem As CommandBarControl MenuBar_Item_Item_Delete Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu If MenuItem Is Nothing Then Exit Sub With MenuItem.Controls.Add(msoControlButton, 1, , , True) .Caption = "&Ron de Bruin" .OnAction = ThisWorkbook.Name & "!TestMacro" .BeginGroup = True .Tag = "MenuItemTag" End With Set MenuItem = Nothing End Sub Sub MenuBar_Item_Item_Delete() Dim MenuItem As CommandBarControl Set MenuItem = Application.CommandBars.FindControl(Tag:="MenuItem Tag") If Not MenuItem Is Nothing Then MenuItem.Delete End If Set MenuItem = Nothing End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "bw" wrote in message news:1105_1059582525 @news.cableone.net... I'm so confused!!! Some history... I have a workbook that I created maybe eight years ago, that has some simple macros that are run ONLY from this workbook, and are accessed by clicking on Tools menu, where the macros are listed at the bottom of the normal list. This macros (and the items on the Tools menu) are ONLY available for this particular workbook. I have tried to duplicate this action in two ways: 1. I have simply dragged an item from the toolbar customize box to the Tools menu, and this works fine, but then it is present on any workbook I open from then on, and of course, I only want it to be present for this workbook. 2. I have saved the original workbook as a new workbook, then deleted everything in the new workbook, including one of the Tools Menu items, and reassigned new macros to the Tools Menu items. I save this new workbook, close it, reopen it, and the Tools menu looks just as it did without saving it. What I want to do is create macros, assign them to a toolbar (especially if it is a Tools menu item), and make these menu items (or toolbar buttons) available ONLY to this workbook. I am having no luck accomplishing this. Can anyone explain why I'm having these problems? Thanks, Bernie |
Toolbar with Macros
Bernie
Thanks for the feedback. Ron's code will help you get started with creating Toolbars and Menus "on the fly". Gord On Wed, 30 Jul 2003 22:42:34 GMT, bw wrote: Thanks Gord, This is what I was looking for (hoping for). I'm pretty sure you are right about Excel 5.0, and you have described the problem I'm having very well. I'll try some of the macros provided by others to try to accomplish the same thing that Excel 5.0 did. Progress I guess... Thanks again, Bernie On Wed, 30 Jul 2003 10:42:02 -0700, Gord Dibben wrote: Bernie "eight years ago" sounds like the workbook was created using XL95 The problem you are having with that particular workbook would be...... Excel 5.0 used the "Menu Editor" to build menus. This is no longer available in versions newer than 5.0. You can't use the "delete" method or "reset" to get rid of the custom menu items. To get rid of the custom menu download Jim Rech's REMOVEMENUS.ZIP file from Stephen Bullen's site. See instructions and d/l file from: http://www.bmsltd.co.uk/MVP/Default.htm Gord Dibben Excel MVP - XL97 SR2 & XL2002 On Wed, 30 Jul 2003 16:28:45 GMT, bw wrote: I'm so confused!!! Some history... I have a workbook that I created maybe eight years ago, that has some simple macros that are run ONLY from this workbook, and are accessed by clicking on Tools menu, where the macros are listed at the bottom of the normal list. This macros (and the items on the Tools menu) are ONLY available for this particular workbook. I have tried to duplicate this action in two ways: 1. I have simply dragged an item from the toolbar customize box to the Tools menu, and this works fine, but then it is present on any workbook I open from then on, and of course, I only want it to be present for this workbook. 2. I have saved the original workbook as a new workbook, then deleted everything in the new workbook, including one of the Tools Menu items, and reassigned new macros to the Tools Menu items. I save this new workbook, close it, reopen it, and the Tools menu looks just as it did without saving it. What I want to do is create macros, assign them to a toolbar (especially if it is a Tools menu item), and make these menu items (or toolbar buttons) available ONLY to this workbook. I am having no luck accomplishing this. Can anyone explain why I'm having these problems? Thanks, Bernie |
All times are GMT +1. The time now is 03:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com