I have command buttons on one worksheet within a workbook spreadsheet. I
want these same buttons to remain as I move from one worsheet to another
within the same spreadsheet. Has anyone done this? Thanks

Place them in the top row, and freeze the top row.
*******************
~Anne Troy

www.OfficeArticles.com


"Rick" wrote in message
...
I have command buttons on one worksheet within a workbook spreadsheet. I
want these same buttons to remain as I move from one worsheet to another
within the same spreadsheet. Has anyone done this? Thanks

Hi,

One way would be to create a toolbar instead.

Second way:
To do this, i think it is easier with button from the Forms toolbar instead
of the Control Toolbox toolbar assuming that every Button1 on each sheet call
the same macro:
- Buttons from Forms: you link them to a m,acro that is in a code module.
That is several button can call the same macro easily. No code in the sheet
module.
- buttons from Control Toolbox: they react to a clik and the _Click sub has
to be in the sheet containing the button. Some code has to be in the sheet.

Assuming you are using these buttons from the Forms toolbar:
- link to macros:
-right click each button, choose Assign Macro and pick a macro from the
list (public macros in modules)
- Group them together:
- from the Drawing toolbar, click the Arrow icon to enter Drawing mode,
so that you can select multiple shapes at once.
- grab the buttons, they become selected
- from the Drawing Toolbar, choose menu Draw Group
- click the Arrow from the Drawing toolbar again to leave the drawing mode.
The buttons are now grouped.
- Copy/Paste to other sheets:
- right the group of buttons, choose Copy from the pop-up menu
- go to another sheet and paste
The buttons are now pasted in the other sheet and they have the same
functionality (they call the same macros when clicked)

i hope this helps,
--
Regards,
Sébastien


"Rick" wrote:

I have command buttons on one worksheet within a workbook spreadsheet. I
want these same buttons to remain as I move from one worsheet to another
within the same spreadsheet. Has anyone done this? Thanks

Rick,

If you don't want to use the tool bar idea, you can paste additional command
buttons on each sheet. Have all of the buttons execute a common subroutine
like this...

Sheet 1 looks like this:
Private Sub CommandButton1_Click()

DoSomeWork 1

End Sub

Public Sub DoSomeWork(SheetIndex As Integer)

Sheets(SheetIndex).Range("E5") = "This is a test"

End Sub

Sheet 2 looks like this:
Private Sub CommandButton1_Click()

DoSomeWork 2

End Sub

and Sheet 3 looks like this:
Private Sub CommandButton1_Click()

DoSomeWork 3

End Sub

I put the public sub right in sheet 1. Sheet 1, 2 and 3 have identical
command buttons with matching left, top, height and width specs, so they
appear to move from one sheet to another as you change sheets. When a
particular sheet's button is clicked, the current sheet index is passed to
the common procedure. You just have to be careful to properly reference
whatever you will be doing in the common procedure off of the sheet index.

The above worked fine in my simple 3 sheet test.

Roy

"Rick" wrote:

I have command buttons on one worksheet within a workbook spreadsheet. I
want these same buttons to remain as I move from one worsheet to another
within the same spreadsheet. Has anyone done this? Thanks

Hello Anne,
I am crossing your way everywhere I go: EE, VBAExpress, Google Answer, ...
and now MS newsgroups :-)
How are you doing?
--
Regards,
Sébastien

Hi Sebastienm. Your suggestion worked. Thanks for the help.
Rick

"sebastienm" wrote:

Hi,

One way would be to create a toolbar instead.

Second way:
To do this, i think it is easier with button from the Forms toolbar instead
of the Control Toolbox toolbar assuming that every Button1 on each sheet call
the same macro:
- Buttons from Forms: you link them to a m,acro that is in a code module.
That is several button can call the same macro easily. No code in the sheet
module.
- buttons from Control Toolbox: they react to a clik and the _Click sub has
to be in the sheet containing the button. Some code has to be in the sheet.

Assuming you are using these buttons from the Forms toolbar:
- link to macros:
-right click each button, choose Assign Macro and pick a macro from the
list (public macros in modules)
- Group them together:
- from the Drawing toolbar, click the Arrow icon to enter Drawing mode,
so that you can select multiple shapes at once.
- grab the buttons, they become selected
- from the Drawing Toolbar, choose menu Draw Group
- click the Arrow from the Drawing toolbar again to leave the drawing mode.
The buttons are now grouped.
- Copy/Paste to other sheets:
- right the group of buttons, choose Copy from the pop-up menu
- go to another sheet and paste
The buttons are now pasted in the other sheet and they have the same
functionality (they call the same macros when clicked)

i hope this helps,
--
Regards,
Sébastien


"Rick" wrote:

I have command buttons on one worksheet within a workbook spreadsheet. I
want these same buttons to remain as I move from one worsheet to another
within the same spreadsheet. Has anyone done this? Thanks

I'm good, Sebastien. And it's very good to see you!
*******************
~Anne Troy

www.OfficeArticles.com


"sebastienm" wrote in message
...
Hello Anne,
I am crossing your way everywhere I go: EE, VBAExpress, Google Answer, ...
and now MS newsgroups :-)
How are you doing?
--
Regards,
Sébastien