Setting up a personal menu using VBA
 

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.

Setting up a personal menu using VBA
 

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
"cghall55" wrote in message
...
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.

Ammended : Setting up a personal menu using VBA
 

"Terry V" wrote in message
...
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
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
"cghall55" wrote in message
...
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.

Setting up a personal menu using VBA
 

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.

"cghall55" wrote:

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.

Setting up a personal menu using VBA
 

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


--

HTH

RP

"cghall55" wrote in message
...
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.

"cghall55" wrote:

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.

Setting up a personal menu using VBA
 

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.

cghall55 wrote:

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.

--

Dave Peterson

Ammended : Setting up a personal menu using VBA
 

cg

See one answer to your earlier posting.

Gord Dibben Excel MVP

On Fri, 08 Oct 2004 18:51:57 GMT, "Terry V" wrote:


"Terry V" wrote in message
...
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
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
"cghall55" wrote in message
...
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.