Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menumaker.xls Create Macro on User Toolbar
I have a hidden workbook that comes up each time I start
the Excel 2000 program with a toolbar attached to it called Bruce's tools. This custom toolbar contains a few specialized shortcuts that I use on a regular basis. Apart from this, I use a variation of J Walkenbach's menumaker.xls in which I may have two or more open workbooks at any given time that have customized menu trees. If one workbook is closed, then the menu tree goes away by the very nature of design in menumaker.xls. I don't really wish to alter his work because I want it to work properly in all my cases. What I want to do is to add a button to my toolbar that would call up the Create Menu macro in the Active Workbook. Right now if I close a workbook containing the before_close method that deletes the menu, then if I switch to another workbook that also has its custom macro call "Create Menu" , then I have to go to its worksheet labeled Menusheet and click on the button called "Create Menu" which calls up the code listed below. The Menusheet worksheet in the active workbook also contains a menu tree specific to the that workbook's needs. There may be several workbooks open at a time that contain a sheet named Menusheet with customized menutrees for that particular workbook. What I want to have the toolbar item do is to take the place of having to go to the active workbook's sheet named Menusheet and click on the button named "Create Menu". This macro code found in menumaker.xls I am speaking of is listed below if you need to refer to it. I don't wish to change that code itself because I want it to work as well in the past as it has when I open any given workbook and it automatically does it thing when opened. This workaround is just a shortcut to reestablish the menu tree for the active workbook whenever a previous workbook being closed has also removed the customized menu tree for that particular workbook. Sub CreateMenu() ' This sub should be executed when the workbook is opened. ' NOTE: There is no error handling in this subroutine Dim MenuSheet As Worksheet Dim MenuObject As CommandBarPopup Dim MenuItem As Object Dim SubMenuItem As CommandBarButton Dim Row As Integer Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId '''''''''''''''''''''''''''''''''''''''''''''''''' '' ' Location for menu data Set MenuSheet = ThisWorkbook.Sheets("MenuSheet") '''''''''''''''''''''''''''''''''''''''''''''''''' '' ' Make sure the menus aren't duplicated Call DeleteMenu ' Initialize the row counter Row = 2 ' Add the menus, menu items and submenu items using ' data stored on MenuSheet Do Until IsEmpty(MenuSheet.Cells(Row, 1)) With MenuSheet MenuLevel = .Cells(Row, 1) Caption = .Cells(Row, 2) PositionOrMacro = .Cells(Row, 3) Divider = .Cells(Row, 4) FaceId = .Cells(Row, 5) NextLevel = .Cells(Row + 1, 1) End With Select Case MenuLevel Case 1 ' A Menu ' Add the top-level menu to the Worksheet CommandBar Set MenuObject = Application.CommandBars (1). _ Controls.Add(Type:=msoControlPopup, _ Befo=PositionOrMacro, _ temporary:=True) MenuObject.Caption = Caption Case 2 ' A Menu Item If NextLevel = 3 Then Set MenuItem = MenuObject.Controls.Add (Type:=msoControlPopup) Else Set MenuItem = MenuObject.Controls.Add (Type:=msoControlButton) MenuItem.OnAction = PositionOrMacro End If MenuItem.Caption = Caption If FaceId < "" Then MenuItem.FaceId = FaceId If Divider Then MenuItem.BeginGroup = True Case 3 ' A SubMenu Item Set SubMenuItem = MenuItem.Controls.Add (Type:=msoControlButton) SubMenuItem.Caption = Caption SubMenuItem.OnAction = PositionOrMacro If FaceId < "" Then SubMenuItem.FaceId = FaceId If Divider Then SubMenuItem.BeginGroup = True End Select Row = Row + 1 Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to create a toolbar? | Excel Discussion (Misc queries) | |||
First Time Macro User--Stop Recording Toolbar | Excel Discussion (Misc queries) | |||
Can I create a command box fora macro to be stored on my toolbar | New Users to Excel | |||
Can I create a command box fora macro to be stored on my toolbar | New Users to Excel | |||
Create dictionary of terms, create first time user site | New Users to Excel |