I have several macros in a workbook and I want to assign them to buttons to
make it easier for new people to use the workbook. However I commonly am
required to add new worksheets and would like the buttons to be available on
the new sheets. Is there any way to make the buttons available on all
worksheets without the need to locate them on a worksheet dedicated for that
sole purpose?
--
Whether you think you can or cannot, you are right. - Henry Ford

go to ALT+F11, Project - VBA Project tab
click on ThisWorkbook

and put the following code there

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name < "Sheet1" Then
Sheets("Sheet1").Shapes("Button 2").Copy
ActiveSheet.Paste
End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If ActiveSheet.Name < "Sheet1" Then
For Each Shape In ActiveSheet.Shapes
Shape.Delete
Next
End If
End Sub

HIH


On 11 Gru, 14:58, Jeff Parrott
wrote:
I have several macros in a workbook and I want to assign them to buttons to
make it easier for new people to use the workbook. However I commonly am
required to add new worksheets and would like the buttons to be available on
the new sheets. Is there any way to make the buttons available on all
worksheets without the need to locate them on a worksheet dedicated for that
sole purpose?
--
Whether you think you can or cannot, you are right. - Henry Ford

Rather than put the buttons on a worksheet, put them on a custom toolbar that
you dock up top with the other standard toolbars.

Use the Open event macro to display this custom toolbar.
--
Gary''s Student - gsnu200819


"Jeff Parrott" wrote:

I have several macros in a workbook and I want to assign them to buttons to
make it easier for new people to use the workbook. However I commonly am
required to add new worksheets and would like the buttons to be available on
the new sheets. Is there any way to make the buttons available on all
worksheets without the need to locate them on a worksheet dedicated for that
sole purpose?
--
Whether you think you can or cannot, you are right. - Henry Ford

Great idea, I hadn't thought of that. Thanks!
--
Whether you think you can or cannot, you are right. - Henry Ford


"Gary''s Student" wrote:

Rather than put the buttons on a worksheet, put them on a custom toolbar that
you dock up top with the other standard toolbars.

Use the Open event macro to display this custom toolbar.
--
Gary''s Student - gsnu200819


"Jeff Parrott" wrote:

I have several macros in a workbook and I want to assign them to buttons to
make it easier for new people to use the workbook. However I commonly am
required to add new worksheets and would like the buttons to be available on
the new sheets. Is there any way to make the buttons available on all
worksheets without the need to locate them on a worksheet dedicated for that
sole purpose?
--
Whether you think you can or cannot, you are right. - Henry Ford