Setting up a personal menu using VBA

[Guest]

Anyone know where I can get some code or directions for setting up a personal
menu of macros? What I am trying to accomplish is that when a user opens this
workbook the code kicks in and creates a menu with a few options on it.

 

[Terry V]

In the workbook, you can customize the toolbar and add a menu option that
drops down, just like the file menu etc.

Right click the toolbar, choose customize, choose New Menu from the commands
tab, drag the "new menu" (on the right side) to the menu bar. From there,
Select macro from the Commands tab and all your macros will show up on the
right. Drag the "Customer Menu Item" to the New Menu and hold it there for
a moment. It will drop down a small square where you can drop the Customer
Menu Item into it.
Click on "Custom Menu" on the toolbar and you will see the Custom Menu item
pop up below it. Click on it once and notice the "Modify Selection" button
shows up in the Customize box. Click on it and select "Assign Macro". A
list of macros will show up and you can simply assign it to the button.
Then you can click where it says "Name" and name it whatever you want.
Do this for each macro you want in your menu (Just add more Custom Menu
Items and assign macros to each).

HTH
Terry V

 

[Terry V]

In the workbook, you can customize the toolbar and add a menu option that
drops down, just like the file menu etc.

Right click the toolbar, choose customize, choose New Menu from the commands
tab, drag the "new menu" (on the right side) to the menu bar. From there,
Select macro from the Commands tab and Drag the "Customer Menu Item" to

the New Menu and hold it there for

 

[Guest]

Yes this information I know.....What I need is for the menu to go with the
file. I have users all over and I don't and can't run around to each machine
and make a custom menu. Thats why I need the code so that when they open the
file the menu appears.

 

[Bob Phillips]

Private Sub Workbook_Open()
AddMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteMenu
End Sub

Public Sub AddMenu()
Dim oCB As CommandBar
Dim oCBCtl As CommandBarControl

DeleteMenu

Set oCBCtl = Application.CommandBars.FindControl(ID:=30007)

With oCBCtl
With .Controls.Add(Type:=msoControlPopup, temporary:=True)
.BeginGroup = True
.Caption = "Menu"
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.BeginGroup = True
.Caption = "Submenu1"
.FaceId = 23
.Style = msoButtonIcon
.OnAction = "myMacro1"
End With
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "Submenu2
.FaceId = 23
.Style = msoButtonIcon
.OnAction = "myMacro2"
End With
End With
End With

End Sub

Public Sub DeleteButton()

On Error Resume Next
Application.CommandBars("Standard").Controls("Menu").Delete
On Error GoTo 0

End Sub

 

[Dave Peterson]

I like the way John Walkenbach did it in his menumaker.xls workbook.
http://j-walk.com/ss/excel/tips/tip53.htm

It's easy to update and looks really professional.

 

[Gord Dibben]

cg

One place to look for code and examples of Menu building.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;830502

Also investigate the use of John Walkenbach's free downloadable MENUMAKR.XLS

http://www.j-walk.com/ss/excel/tips/tip53.htm

Gord Dibben Excel MVP

 

[Gord Dibben]

cg

See one answer to your earlier posting.

Gord Dibben Excel MVP