Workbook Specific Toolbar Menus
I was proud of myself in creating a new toolbar menu and sub-menu and
assigning macros to the menu items until I discovered that the menu appears on the toolbar always now, regardless of the spreadsheet I open. It appears that when I select a custom menu item, it's going out the the spreadsheet where it is stored and executing it against the spreadsheet I have open (great, but that's not what I want). How do I create toolbar menus that only appear for a specific spreadsheet when it is open? -- Steve |
Steve,
The best option is to create the commandbar on the fly, when the workbook is opened, and delete the commandbar when the workbook is closed. Follow these instructions and example code. In the workbook's Thisworkbook object code module, place the following code: Private Sub Workbook_BeforeClose(Cancel As Boolean) DeleteCommandbar End Sub Private Sub Workbook_Open() CreateCommandbar End Sub Private Sub Workbook_WindowActivate(ByVal Wn As Window) On Error GoTo NotThere Application.CommandBars("My Bar").Visible = True Exit Sub NotThe CreateCommandbar End Sub Private Sub Workbook_WindowDeactivate(ByVa*l Wn As Window) On Error Resume Next Application.CommandBars("My Bar").Visible = False End Sub In a regular code module, place the following: Dim myBar As CommandBar Dim myButton As CommandBarButton Sub CreateCommandbar() On Error Resume Next DeleteCommandBar Set myBar = Application.CommandBars.Add("M*y Bar") With myBar .Position = msoBarTop .Visible = True .Enabled = True Set myButton = .Controls.Add(Type:=msoControl*Button, ID:=23) With myButton .Caption = "Hello" .Style = msoButtonIcon .FaceId = 137 .Enabled = True .OnAction = "SayHello" End With End With End Sub Sub DeleteCommandBar() 'Delete the commandbar if it already exists On Error Resume Next Application.CommandBars("My Bar").Delete End Sub Sub SayHello() MsgBox "Hello there" End Sub You can add as many buttons or other menu items as you like. HTH, Bernie MS Excel MVP "sjschmidtky" wrote in message ... I was proud of myself in creating a new toolbar menu and sub-menu and assigning macros to the menu items until I discovered that the menu appears on the toolbar always now, regardless of the spreadsheet I open. It appears that when I select a custom menu item, it's going out the the spreadsheet where it is stored and executing it against the spreadsheet I have open (great, but that's not what I want). How do I create toolbar menus that only appear for a specific spreadsheet when it is open? -- Steve |
All times are GMT +1. The time now is 01:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com