One button, one macro, several sheets

[Henk]

I have a workbook with a lot of simular sheets. On each sheet I want to have
the same button that directs the user to a central sheet (where, of course,
is a button which redirects the user to where he/she came from).

To create one button is not the problem. To copy the button to all the other
sheets, whithout having to rename the "Commandbutton1" and to copy the VBA
code to all the other sheets is the problem.

So, I am looking for a central place for the macro (This Worksheet??) and a
way to in the first place and a way to "tell" my commandbutton where to look
for that macro.

Thanks for your help in advance,

Henk

 

[Gary''s Student]

Create your sub in a module. The sub should be Public and accessible from
any sheet. Then assign the button on each sheet to the same sub. This will
work for both a Forms or a Controls button

 

[Henk]

Thanks for your quick answer.

It is exactly how I thought it should work, but ................ it does not.

As far as I know the only way to assign a macro to a command button, is to
double click the button in Design Mode. But then you are directed to a
Private sub with the name of the command button in the sheet where the button
is. I have not found a way yet to tell the button where to look for the macro
it has to execute.

Using a Form control on my worksheet, results in a button that is assigned
to the right macro, but can not be invoked whether it is in Design Mode or
not.

The logic is clear to me, but I do not know how to do it.

 

[Gary''s Student]

1. first create a macro using, for example, the Recorder
2. bring up the Forms ToolBar
3. put a button on a sheet

the Assign Macro dialog box will appear.

4. under Macro name, ignore Button1_Click and select the sub created in step
#1
5. click outside the button to exit Edit mode

 

[paul.robinson]

hi
Or if button already exists, right click it and Assign Macro...
regards
Paul

 

[Henk]

Wonderful!

It is working with the "Form control command-button".

It is not necessary anymore, but if you know a way to get it working with an
"Active X-control Command-button" please let me know.

tHENKs!