Copying a workbook with custom menus and macros


G

Guest

I set up an application in Excel with custom menus, VB programming and some macros. My intent was to distribute it at work. When I copied it onto the computer at work, the custom menu disappeared and the macros did not work. I noticed it changed the name of the macro to look in the original file for the macro. I changed the macros to be recorded in "This workbook", but still ran into problems. Is there a way to copy a workbook and have it exactly the way it was set up originally? I also need to be sure that it works properly even if I change the name of the file

I am used to working in Access where you can copy the database and it will be identical to the original file. Surely there must be a way to do this with Excel?

Thanks for any help.
 
Ad

Advertisements

J

Jan Karel Pieterse

Hi Pam,

<<I set up an application in Excel with custom menus, VB programming and some macros. My intent was to distribute it
at work. When I copied it onto the computer at work, the custom menu disappeared and the macros did not work. I
noticed it changed the name of the macro to look in the original file for the macro. I changed the macros to be recorded
in "This workbook", but still ran into problems. Is there a way to copy a workbook and have it exactly the way it was set
up originally? I also need to be sure that it works properly even if I change the name of the file.>>

In Excel, commandbar edits are stored locally in a file called Excel.xlb (filename depends on Excel
version, extension always xlb). Custom menus are kept there too.
So if one wishes to distribute a workbook with custom menus, one has to create code that creates them at load.

One can also have a toolbar attached to a workbook.

Here is my standard text about toolbars and distributing utilities:

Many users tend to put their macros in their personal.xls file. Nothing wrong with that, because that is what it is there for. But what if you like your macros so much you decide others might benefit?

You might be tempted to copy your personal.xls onto a floppy and give that to others. Don't!
Also don't copy your xlb file to others to give them your toolbars, you will overwrite their customisations..

I would be very distressed if you would come and hijack my personal.xls and excel.xlb files!

What you should do is create a new workbook with all your code *and toolbars* and distribute *that* file (maybe saved as add-in).

About toolbars:

You can attach a toolbar to a workbook. When this workbook is loaded, XL checks if the toolbar is on the system. If not, it copies the toolbar from the workbook to the system.

After creating *or changing* the toolbar, you should attach the toolbar to your workbook:

- activate the workbook to which you want to attach the toolbar
- Rightclick the toolbar, select 'customize'
- Click 'Attach' (Toolbars Tab)
- If the workbook already contains a toolbar by that name, delete it first by clicking on it on the righthand side and choosing Delete.
- Select your toolbar (on the left) and press 'copy'
- Save the workbook (optionally: save_as an add-in).

Also, You should include code that deletes the toolbar when your workbook or add-in is closed, so that when you deliver a new version of your workbook the new toolbar will be used i.s.o the old one. You can do that in the Thisworkbook module, using the Workbook_beforeClose event:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
On Error Resume Next 'In case Toolbar is absent
Application.CommandBars("YourBarsName").Delete
End Sub


Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top