ADD SAME COMMAND BUTTON TO ALL SHEETS

[rikvny02]

Hello all masters of you craft
I have a workbook that has 100+ sheets. I have added a command button and a combo box(drop down list) to the master sheet. Is there a way to add ComboBox1 and CommandButton1 to all existing sheets.

Id like to add the above command button and combo box to each of the sheets below. All sheets look identical. Location of the buttons would also be key.

This forum has been great. Thanks in advance

 

[Domenic]

So, if I understood you correctly, you have two ActiveX controls, CommandButton1 and ComboBox1, correct?

If so, they can easily be copied to your other sheets. But it would also mean that you would have to copy the event handler for CommandButton1 to the code module for all your copied sheets in your project. However, it would be easier to use a Button from Form controls, to which you can assign a macro. Then you can simply copy and assign the macro.

Alternatively, you could copy your master sheet, which would include CommandButton1 and ComboBox1, 100 times. In this case, the event handler for the commandbutton would also be copied to the code module for each respective worksheet.

How would you like to proceed?

 

[Herakles]

Do you want ActiveX Controls or Form Controls?

Are the controls to be in the same position on each sheet?

What do you want to happnen when an item from the Combo Box is selected?

What do you want to happen when the Control Button is clicked?

Are the actions to be taken depend on values in the worksheet?

 

[rikvny02]

Do you want ActiveX Controls or Form Controls?

Are the controls to be in the same position on each sheet?

What do you want to happnen when an item from the Combo Box is selected?

What do you want to happen when the Control Button is clicked?

Are the actions to be taken depend on values in the worksheet?

Great questions

Do you want ActiveX Controls or Form Controls? ACTIVEX CONTROLS

Are the controls to be in the same position on each sheet? SAME POSTITION

What do you want to happnen when an item from the Combo Box is selected? COMBO BOX IS USED TO NAVIGATE TO EACH PAGE. (150 IN TOTAL).

What do you want to happen when the Control Button is clicked? CONTROL BOX IS JUST A RETURN TO MASTER SHEET

Are the actions to be taken depend on values in the worksheet? NO

 

[Herakles]

I would have a floating Userform which has a number of advantages.

Very easy to set up.
Portable, can be used on any workbook.
Visible irrespective of where on a sheet you are.
Invoked by shortcut keys and loads when the workbook is opened.

 

[rikvny02]

So, if I understood you correctly, you have two ActiveX controls, CommandButton1 and ComboBox1, correct?

If so, they can easily be copied to your other sheets. But it would also mean that you would have to copy the event handler for CommandButton1 to the code module for all your copied sheets in your project. However, it would be easier to use a Button from Form controls, to which you can assign a macro. Then you can simply copy and assign the macro.

Alternatively, you could copy your master sheet, which would include CommandButton1 and ComboBox1, 100 times. In this case, the event handler for the commandbutton would also be copied to the code module for each respective worksheet.

How would you like to proceed?

I currently have it set up where the master will generate a new sheet ( rename, pull both buttons, Update). My issue is Id like to update the 150 sheets that pre-date my update to the workbook. This wouldn't be such an issue except there will be 5 separate workbooks to update from other locations, so 1000 sheets to add buttons too.

 

[rikvny02]

I would have a floating Userform which has a number of advantages.

Very easy to set up.
Portable, can be used on any workbook.
Visible irrespective of where on a sheet you are.
Invoked by shortcut keys and loads when the workbook is opened.

I love using shortcut keys. The workbook will be going to multiple location and let me tell you, the field is not very computer friendly.

 

[rikvny02]

would it be easier if I used shapes instead of activeX controls

 

[Domenic]

Yes, it would be much easier to use a shape instead of an ActiveX control. When a shape is copied, the same macro is automatically assigned to the copied shape. However, with an ActiveX control, the event handler(s) for the control must be copied and inserted into the code module for each sheet in which the control is copied to.

Here's an example that uses a shape. It assumes that the active workbook contains your master sheet, the master sheet contains the shape, etc. It also assumes that the name of your master sheet is "MyMasterSheetName" and that the name of your shape is "MyShapeName". Change these names accordingly.

Option Explicit

Sub test()

    Const MASTER_SHEET_NAME As String = "MyMasterSheetName" 'change the name of the master sheet accordingly
    Const SHAPE_NAME As String = "MyShapeName" 'change the name of the shape accordingly
 
    If ActiveWorkbook Is Nothing Then
        MsgBox "No workbook found!", vbExclamation
        Exit Sub
    End If
 
    Dim sourceWorkbook As Workbook
    Set sourceWorkbook = ActiveWorkbook

    On Error Resume Next
    Dim masterSheet As Worksheet
    Set masterSheet = sourceWorkbook.Worksheets(MASTER_SHEET_NAME)
    If masterSheet Is Nothing Then
        MsgBox MASTER_SHEET_NAME & " not found!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
 
    Application.ScreenUpdating = False
 
    Dim originalShape As Shape
    Set originalShape = masterSheet.Shapes(SHAPE_NAME)
 
    originalShape.Copy
 
    Dim currentActiveSheet As Object
    Set currentActiveSheet = sourceWorkbook.ActiveSheet
 
    Dim currentWorksheet As Worksheet
    For Each currentWorksheet In sourceWorkbook.Worksheets
        If currentWorksheet.Name <> masterSheet.Name Then
            With currentWorksheet
                .Activate
                .Paste
                With .Shapes(.Shapes.Count)
                    .Left = originalShape.Left
                    .Top = originalShape.Top
                End With
                .Cells(1).Select
            End With
        End If
    Next currentWorksheet
 
    currentActiveSheet.Activate
 
    Application.ScreenUpdating = True
 
    MsgBox "Completed!", vbExclamation
 
End Sub