Is there a way to copy a control (button) to multiple sheets? 1

[PPettit]

Is there any way to copy a control to multiple sheets within a workbook? (I want the button to appear in a particular 2x2 area and snap to the edges of the cells. Data is already present.)

I've got a workbook with multiple sheets. I've got a macro that I want available from each page (via a button). To get the button on each sheet, I really don't want to have to display each page, paste the control, then modify it so that it's sized and positioned the way I want it. Is there not a way to set up the button once and then copy it to all pages at the same time?

 

[SkipVought]

Hi,

Is there not a way to set up the button once and then copy it to all pages at the same time?

Turn on your macro recorder and record setting up your button exactly as you like. Then do a copy and paste on ONE SHEET.

Post back with your recorded code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PPettit]

Skip, this is what I wound up with:

Sub MakeButton()
'
' MakeButton Macro
'

'
    ActiveSheet.Buttons.Add(577.5, 8.25, 72, 72).Select
    Selection.OnAction = "ThisWorkbook.ToggleColumns"
    Selection.ShapeRange.ScaleWidth 1.39, msoFalse, msoScaleFromBottomRight
    Selection.ShapeRange.ScaleHeight 1.52, msoFalse, msoScaleFromBottomRight
    Selection.ShapeRange.ScaleWidth 1.51, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.09, msoFalse, msoScaleFromTopLeft
    Range("A1").Select
    ActiveSheet.Shapes("Button 57").Select
    Selection.Copy
    Sheets("C").Select
    Range("L1").Select
    ActiveSheet.Buttons.Add(559.5, 0, 96, 26.25).Select
    ActiveSheet.Paste
End Sub

 

[SkipVought]

Sub Main()
    MakeButton  'do this one time
    CopyButton  'copy button to all other sheets
End Sub
Sub MakeButton()
'
' MakeButton Macro
'

'
    With Sheets("Sheet1").Buttons.Add(577.5, 8.25, 72, 72)
        .OnAction = "ThisWorkbook.ToggleColumns"
        .Name = "ButtonTemplate"
        .ShapeRange.ScaleWidth 1.39, msoFalse, msoScaleFromBottomRight
        .ShapeRange.ScaleHeight 1.52, msoFalse, msoScaleFromBottomRight
        .ShapeRange.ScaleWidth 1.51, msoFalse, msoScaleFromTopLeft
        .ShapeRange.ScaleHeight 1.09, msoFalse, msoScaleFromTopLeft
    End With
    
End Sub

Sub CopyButton()
'
    Dim ws As Worksheet
'

'
    For Each ws In Worksheets
        If ws.Name <> "Sheet1" Then
            Sheets("Sheet1").Shapes("ButtonTemplate").Copy
            With ws
                .Activate
                .[L1].Select
                .Paste
            End With
        End If
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PPettit]

Thanks for code, Skip. This did copy the button to each page. However, the horizontal positioning is off on almost all of the pages. Any idea how to address this?

 

[SkipVought]

Your method is not the way I would position. I would explicitly assidn the Top and Left proipreties of the shape...

            With ws
                .Paste
                with .Shapes(.Shapes.count)
                   .top = SomeNumber
                   .left = someothernumber
                end with
            End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PPettit]

Ok. I'll look into that. Thanks.

 

[SkipVought]

Drag the ButtonTemplate button to the place you want each button to be positioned.

dim nTop, nLeft

with sheets("Sheet1").Shapes("ButtonTemplate")
  nTop = .top
  nLeft = .left
end with

then use those variables to apply to each new shape.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PPettit]

After messing with this for a bit, I'm sure that I have no clue as to how to implement your suggestion. However, I think I understand that your approach and what I want are different.

If I understand your suggestion correctly, it's supposed to position the button at a fixed location on the sheet, relative to the top and left edges of the sheet. This doesn't work for me because the column widths are not the same on each sheet. This is why I want the button to always be positioned over a specific 2x2 area (L1:M2). In other words, I want the button positioned relative to specific cells, not the sheet edges. The more I investigate this, the more it seems like this isn't possible.

 

[SkipVought]

range L1 has a top and left property
[tt]
With ws
.Paste
with .Shapes(.Shapes.count)
.top = .[L1].top
.left = .[L1].left
end with
End With
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[intrigrant]

Maybe this might lead you to a solution:

http://books.google.nl/books?id=Vne...snum=1&ved=0CAgQ6AEwADgK#v=onepage&q=&f=false

With the above AND the tipsSkipVought gave you it