Reputation: 31
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
Upvotes: 1
Views: 952
Reputation: 71157
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
Upvotes: 2
Reputation: 2892
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.
Upvotes: 2
Reputation: 29421
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"
Upvotes: 2