Making a macro-triggering custom menu button "travel" with a file

I've done a model in which I use custom menu buttons to trigger macros. I want to share the file with someone else.

Is there a way I can ensure that he'll have the buttons available too? If not, is there a way I can get him to have the buttons without causing him to go through a lot of hassle? (The recipient is a bit of a technophobe ).

I'd be grateful for any advice.

Dominic,

Thanks for your help. Unfortunately I seem to be doing something wrong. I'vve adapted your code as follows. This code all exists in the spreadsheet's only standard module; it comprises three basic macros and my take on your code. There is nothing in the file's ThisWorksheet module.

When I open the workbbok, no toolbar appears. Any ideas?

Regards,

Lingyai

Please Login or Register  to view this content.

Delete Dominic's code from Module and paste it into the code window for ThisWorkbook.

Dominic's code is "event" driven. Event driven procedures (macros) need to be in the code window for the "object" which triggers the "event". The "object" in this case is ThisWorkbook. The "Events" are Workbook_Open and Workbook_BeforeClose.

Thanks. I did cut his code into ThisWorkbook, leaving my own macros in the standard module, saved and closed. But on reopening I got a "variable not defined" error message; the debugger highlighted "TB" in Dominic's code. Did I do something wrong?

Did I do something wrong?

I do not think so. If my belief is correct, then you are doing it exactly "right" and most of the rest of us do it "wrong".

Go back to the code window where you pasted Dominic's code (the second time); namely, the code window for the ThisWorkbook object. Do you see this:

Please Login or Register  to view this content.

near the top of the code window?

If so, and if you did not type it there yourself, then you have set your default properties to ALWAYS add this option to every code window that you use. This is the "right" thing to do. But, Excel does not start out that way. And most of us are too lazy to turn this option "on".

Just for a test, delete the line that says "Option Explicit", and try again.

If that works, then you need to decide if you are going to:
1. turn off this Option, or
2. change Dominic's code by adding Dim statements for every variable used

To turn off this option (and live dangerously, like most of us) do the following:
1. in the VB Editor menu, select Tools >> Options, this opens the Options dialog
2. in the Options dialog, select the "Editor" tab
3. in the section for "Code Settings", click "off" the entry for "Require Variable Declaration"
4. Click "OK"

You are right that you are wrong and I am right. What I mean is ... Dominic's code worked fine when I deleted the option explcit line. So I suppose this means that one of the first things I should do when someone esle's code goes buggy on my machine is to delete that line, yes? (I don't think I want to disable that feature; VBA is challenging enough without the added "danger".

Man, there's so much to learn when moving from simply recording macros to writing code!

Thanks very much, both to you and Dominic.

one of the first things I should do when someone esle's code goes buggy on my machine is to delete that line, yes?

Yes ... if ... you ... get ...

a "variable not defined" error message