If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Button availability
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 |
#2
|
|||
|
|||
Button availability
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 |
#3
|
|||
|
|||
Button availability
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 |
#4
|
|||
|
|||
Button availability
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 |
Thread Tools | |
Display Modes | |
|
|