Creating a Menu

[StevenD]

Hi,

I would like to be able to create a menu that will appear everytime i open a new workbook (open excel).

How would I do this?

Thanks

Steve

 

[GlennUK]

Have you looked at the help? What would you put on the menu? Do you have experience of VBA?

 

[Ste_Moore01]

If you have any experience with macros check this topic.
http://www.mrexcel.com/board2/viewtopic.php?t=176653&highlight=

All you need to do is save the menumakr file you download in your XLSTART folder and hide the workbook.

If you need any further help don't hesitate to ask.

 

[StevenD]

Hi Glenn,

Yes i do have experience of VBA.

I use the following code normally, but don't think it would work without this book open.

Public Sub SetupMenu()
Dim newMenu, newMenuItem
newMenu.Caption = "&Projects"
Set newMenuItem = newMenu.Controls.Add(Type:=msoControlButton)
newMenuItem.Caption = "&Open Project Log"
newMenuItem.OnAction = "Log"
With CommandBars("worksheet menu bar")
Set newMenu = .Controls.Add(Type:=msoControlPopup, before:=.Controls("window").Index, temporary:=True)
End With
End Sub

Do i need to write this code and the sub it calls (Log), in the default workbook that the menu is there ecerytime you open excel?

thanks

Steve

 

[Ste_Moore01]

I'm not sure about the VBA code, but when I made my own Menu I used the menu maker at this link. http://www.j-walk.com/ss/excel/tips/tip53.htm

When I had modified the menu to my own things, I changed the code so that the message about creating a new menu didn't come up everytime you opened the menu maker file.

Once I'd done that, I saved the menu maker file in the XLStart folder in my Office files in Program Files so that it opens everytime I open Excel and automatically makes my menu.

The only problem with doing that is, you see the menu maker everytime excel opens so I clicked Window and Hide and saved it when I closed. Now the menu maker doesn't appear when I load Excel but it loads my menu.

I know it's not the most professional way to make a menu but it works.

I'm sorry I can't help you with the code because I'm not too good with VBA code... that's why I come here for help everyday!

 

[StevenD]

Many thanks for your help.

I also found I had to make excel point to the XLstart folder, by going to tools, options, general.

 

[Ste_Moore01]

Oh?

I never had to do that myself, it must've been set up automatically.

Glad I could help though!