Button availability

[Jeff Parrott]

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?

 

[Jarek Kujawa]

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

 

[Gary''s Student]

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.

 

[Jeff Parrott]

Great idea, I hadn't thought of that. Thanks!