Replicating Form Control Buttons Across Sheets

[Fadedlight1212]

So I am a bit of an Excel neophyte. Sadly, I am also the Network Admin at my worksite and as such am expected to know how to do anything computer related. Our payroll system is done entirely using Excel 2013. There is a workbook for each user and a sheet in each workbook for each pay period. Recently, our Payroll Admin asked me if I could create a button on the sheets that would lock the active sheet so once someone has entered their "time card" it would require a password to edit. Did some Googling, found the info and was able to create a Form Control Button that does what she asked. The problem now is getting that button into every sheet for the 85+ workbooks in use (one for each employee). I know I can copy/paste but as there are already 24 sheets (2 pay periods a month for 12 months) per workbook that is a whole bunch of copy/pasting. Is there anyway to automate the replication of the Form Control Button across all sheets in a workbook?

 

[iliace]

I don't do this sort of thing, so there may be a better way, but this should work. Run the ReplicateButton() proc with the initial sheet activated. Note, whatever the name of the button is in the Name box when you have it selected, is what sButtonName constant should be equal.

Public Sub ReplicateButton()
  Const sButtonName As String = "Button 1"

  Dim wsh As Excel.Worksheet
  Dim wshSource As Excel.Worksheet
  
  Dim shp As Excel.Shape
  
  Set wshSource = Application.ActiveSheet
  
  Set shp = wshSource.Shapes(sButtonName)
  
  For Each wsh In Application.ActiveWorkbook.Worksheets
    If Not (wsh Is wshSource) Then
      shp.Copy
      wsh.Paste
      
      With wsh
        With .Shapes(.Shapes.Count)
          .Left = shp.Left
          .Top = shp.Top
        End With
      End With
    End If
  Next wsh
End Sub

The macro assigned will be the same macro as assigned to the initial button. So, you would modify that macro to use the ActiveSheet to determine what was clicked. For example:

Sub Button1_Click()
  Debug.Print "Button was clicked on " & Application.ActiveSheet.Name
End Sub

 

[Fadedlight1212]

I don't do this sort of thing, so there may be a better way, but this should work. Run the ReplicateButton() proc with the initial sheet activated. Note, whatever the name of the button is in the Name box when you have it selected, is what sButtonName constant should be equal.

Public Sub ReplicateButton()
  Const sButtonName As String = "Button 1"

  Dim wsh As Excel.Worksheet
  Dim wshSource As Excel.Worksheet
  
  Dim shp As Excel.Shape
  
  Set wshSource = Application.ActiveSheet
  
  Set shp = wshSource.Shapes(sButtonName)
  
  For Each wsh In Application.ActiveWorkbook.Worksheets
    If Not (wsh Is wshSource) Then
      shp.Copy
      wsh.Paste
      
      With wsh
        With .Shapes(.Shapes.Count)
          .Left = shp.Left
          .Top = shp.Top
        End With
      End With
    End If
  Next wsh
End Sub

The macro assigned will be the same macro as assigned to the initial button. So, you would modify that macro to use the ActiveSheet to determine what was clicked. For example:

Sub Button1_Click()
  Debug.Print "Button was clicked on " & Application.ActiveSheet.Name
End Sub

thanks for the info, I'l lgive it a shot tonight and let you know how it goes. ^_^