Pinning Form Control Buttons to Multiple Worksheets

Question

I have a workbook with multiple worksheets each with a series of buttons. I want to pin the buttons to specific locations for each worksheet so that every time a user opens the workbook the buttons locate to the correct spot. Problem is the code being used only works when placed in 'ThisWorkbook' and I want to be able to code the buttons by sheet. I've tried to drop the code into the worksheet area but it just doesn't work and I can't figure out what I'm missing here to get it working.

EDIT - each worksheet in the workbook is unique to itself with different macros assigned to each button and each button being located in a different position for each worksheet. The sheets themselves share button names ("button 1", "button 2", etc.) so I think the buttons need to be 'pinned' to each individual worksheet to allow for this variation in location and use since button 1 on sheet 1 is totally unique from button 1 on sheet 2.

My code below operates the workbook and not the worksheet which has already been identified as screw up #1 on my part.

Private Sub Workbook_Open()
Dim rng As Range
Set rng = ActiveSheet.Range("D5")
With ActiveSheet.Shapes("Button 1")
    .Top = rng.Top
    .Left = rng.Left
    .Width = rng.Width
    .Height = rng.RowHeight
End With
End Sub

EDIT#2 Using Mat's suggestion I am trying to identify each button within the sheet but I do not know how to use the public property get and identify bot the "Button Name" and the "Range" of each button so that I can locate more than one button per sheet

    Option Explicit
Public Property Get ButtonName() As String
ButtonName = "Button 1"
End Property
Public Property Get ButtonAnchor() As Range
Set ButtonAnchor = Me.Range("B2")
End Property

Answer 1

iterate over workbook's worksheets collection:

Private Sub Workbook_Open()
    Dim rng As Range
    Dim sht As Worksheet

    For Each sht In Worksheets
        With sht
            Set rng = .Range("D5")
            With .Shapes("Button 1")
                .Top = rng.Top
                .Left = rng.Left
                .Width = rng.Width
                .Height = rng.RowHeight
            End With
        End With
    Next sht
End Sub

of course this assumes that every sheet has a button called "Button 1"

Answer 2

user3598756 has the correct answer (as Mat's Mug pointed out). If you want something to happen when the user opens the workbook, it goes in the workbook_open event handler which gets located in the ThisWorkbook code module. That's just the way Excel VBA works. If I want my car to work, but refuse to put gas in the gas tank and demand to put the gas in the tires instead, guess what's going to happen?

You can still work with individual worksheets in the Thisworkbook code module however. Slightly tweaking user3597856's code:

Private Sub Workbook_Open()
Dim rng As Range
Dim sht As Worksheet

For Each sht In Worksheets
    With sht
        Select Case .Name
            Case "Sheet 1"
                Set rng = .Range("D5")
                With .Shapes("Button 1")
                    .Top = rng.Top
                    .Left = rng.Left
                    .Width = rng.Width
                    .Height = rng.RowHeight
                End With
            Case "Sheet 2"
                Set rng = .Range("G425")
                With .Shapes("Button 1")
                    .Top = rng.Top
                    .Left = rng.Left
                    .Width = rng.Width
                    .Height = rng.RowHeight
                End With
            Case Else
                'What to do when its not a sheet we care about? Nothing.
        End Select
    End With
Next sht
End Sub

The benefit of doing it this way is that all the code to set all the button placements is in one nice location, so as you add or remove sheets, you only have to go to one spot to update your code. Another benefit is that this code is only execute once: when the workbook opens. It does have the drawback of not meeting your requirement that the code cannot loop through each sheet.

Another (less useful) option that doesn't involve cycling through the sheets to set button placement is to use the Worksheet_Activate event. This resides in each individual worksheets code module so you can have an infinite number of subroutines to update each time your code changes. This event is fired every time the user clicks on a sheet's tab. Tweaking your code a bit:

Private Sub Worksheet_Activate()
Dim rng As Range
Set rng = ActiveSheet.Range("D5")
With ActiveSheet.Shapes("Button 1")
    .Top = rng.Top
    .Left = rng.Left
    .Width = rng.Width
    .Height = rng.RowHeight
End With
End Sub

As horrible as this idea is, it does have two benefits. First, if user's move the button around on the sheet, it will reset itself every time they sitch away and switch back. The other benefit is that it meets your requirement that the code must go in the individual worksheet code modules and not cycle through the worksheets.

Answer 3

Add a Property Get member on each worksheet that features one such button; for example here would be a worksheet with its button in A1:

Option Explicit

Public Property Get ButtonAnchor() As Range
    Set ButtonAnchor = Me.Range("A1")
End Property

And this would be another where the button is in cell D5:

Option Explicit

Public Property Get ButtonAnchor() As Range
    Set ButtonAnchor = Me.Range("D5")
End Property

Keep adding that member for every worksheet you have a button for, each time specifying what Range object represents where you want the button to be located.

---

Then, in the ThisWorkbook document class module, you will iterate all worksheets and late-bind access the ButtonAnchor member; because this will throw an error in worksheets that don't have that property, you'll wrap it with On Error statements:

Private Sub Workbook_Open()

    Dim sheet As Object
    Dim anchor As Range
    For Each sheet In ThisWorkbook.Worksheets
        On Error Resume Next
            Set anchor = sheet.ButtonAnchor
            Err.Clear
        On Error GoTo 0
        If Not anchor Is Nothing Then
            With sheet.Shapes("Button 1")
                .Top = anchor.Top
                .Left = anchor.Left
                .Width = anchor.Width
                .Height = anchor.RowHeight
            End With
        End If
    Next

End Sub

The anchor range will be Nothing for sheets without the ButtonAnchor property, so we skip these sheets by testing whether anchor Is Nothing. Another assumption, is that the button is named Button 1 on every worksheet that features that button.

You can remove that assumption by adding another property:

Public Property Get ButtonName() As String
    ButtonName = "Button 1"
End Property

And then another sheet could have:

Public Property Get ButtonName() As String
    ButtonName = "Button 42"
End Property

And then instead of going With sheet.Shapes("Button 1") you'd go with this (inside the For Each loop in ThisWorkbook.Workbook_Open):

    If Not anchor Is Nothing Then
        With sheet.Shapes(sheet.ButtonName)
            .Top = anchor.Top
            .Left = anchor.Left
            .Width = anchor.Width
            .Height = anchor.RowHeight
        End With
    End If