PDA

View Full Version : Copying Custom Menu to another Computer



ssinghal
05-08-2007, 07:59 AM
I have created a custom Menu on Excel 2003 that uses macros from the Personal Macro Workbook. I know how to copy the Personal Macro Workbook to another machine, but need to find out how to also copy the custom menu to another machine. Any ideas?

Simon Lloyd
05-08-2007, 08:17 AM
If by custom menu you mean custom menu bar then try this, the first 3 lines turn standard menubars off, the 4th you need to insert your menu bar name and the next 2 lines (which you can repeat) are to assign subs to buttons when the workbook is opened (modify with your names). don't forget the things you turn off (False) in workbook_open need to be turned back on (True) in your workbook_close


Sub Workbook_Open()

With Application
.CommandBars.ActiveMenuBar.Enabled = False
.CommandBars("Formatting").Visible = False
.CommandBars("Standard").Visible = False
.CommandBars("your menubar name").Visible = True
.CommandBars("your menubar name").Controls("your button name").OnAction = "your sub name"
.CommandBars("your menubar name").Controls("your button name").OnAction = "your sub name"
.DisplayFullScreen = False
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
End Sub


Hope this helps!
Regards,
Simon

lucas
05-08-2007, 08:26 AM
Check out this thread.......your issue begins at post#7
Don't be put off by the title of the thread as it evolves into a discussion of menu's
http://www.vbaexpress.com/forum/showthread.php?t=12670

Paul_Hossler
05-08-2007, 07:21 PM
FWIW, what I do is to

1. take the Personal.xls macros that I need and and put them into another workbook (e.g. UtilityMacros.xls)

2. Create a new ToolBar with the menus, icons, etc.

3. Attach that ToolBar to the new WB (View, Toolbars, Customize, Attach) and delete them from the left side

4. Make UtilityMacros.xls hidden (Windows, Hide)

5. In the WB open and close events make it visible, and delete to get it out of the main .XLB (Excel's toolbar file). I only want the toolbar to show when my XLS is loaded.

6. Put that XLS into XLSTART

Picture worth 1K words ... the attachment is a hidden XLS, and you can see what I like to do, (only one of many valid ways) to see if it gives you any ideas

Paul

lucas
05-08-2007, 08:21 PM
Hi Paul,
Great idea....
You are basically creating an addin which is what I generally do and put it in xlstart. Never thought of just using xls for the same thing.

There are indeed many valid ways but you have to admit that this would be daunting for a novice...the original poster here didn't even know to put his/her macro's in the workbook they are using and how to attach the custom menu to that workbook....they are lucky folks like you are willing to share what you have learned the hard way....trial & error..

johnske
05-09-2007, 01:32 AM
FWIW, what I do is to

1. take the Personal.xls macros that I need and and put them into another workbook (e.g. UtilityMacros.xls)

2. Create a new ToolBar with the menus, icons, etc.

3. Attach that ToolBar to the new WB (View, Toolbars, Customize, Attach) and delete them from the left side

4. Make UtilityMacros.xls hidden (Windows, Hide)

5. In the WB open and close events make it visible, and delete to get it out of the main .XLB (Excel's toolbar file). I only want the toolbar to show when my XLS is loaded.

6. Put that XLS into XLSTART

Picture worth 1K words ... the attachment is a hidden XLS, and you can see what I like to do, (only one of many valid ways) to see if it gives you any ideas

PaulI call that a Startup item - see here (http://vbaexpress.com/forum/showthread.php?t=9747)

Paul_Hossler
05-09-2007, 05:25 AM
Lucas,

"Trial & error & error & error & error & error ":doh:

You are 100% right, can be confusing to pull the different parts togeather. Took me 4-5 false starts before I began to figure out what it was I was really trying to do

BTW, if it were just for me, I would also make it an add-in. I normally use this round about technique because I have a series of templates (.XLTs), each with different macros and toolobars, and my "less sophisticated" users usually just end up using the XLS from the XLT and changing the last version. My experience is that I can explain how to copy a file into c:\documents and setting\<userid>\Application Data\Microsoft\Excel\XLSTART a lot easier that installing an add-in

I was hoping that the example would demo it clearly, if not I'll be glad to answer any questions about why I did something.

Paul

lucas
05-09-2007, 07:38 AM
I'm adding that to my list of references about menu's John....I had forgotten about it.