Sizing Buttons

  • Thread starter Thread starter Microsoft Communities
  • Start date Start date
M

Microsoft Communities

I have several command button on several worksheets in a workbook. I would
like to size them so that they are all the same size and in the same spot of
each sheet. Is there a way to do this?
I tried to work with the formatting section of each button but just can not
seem to get them all to look the same.
 
Are all the names of each of the buttons that should be resized and repositioned
named the same on each page?

Commandbutton1 on sheet1, sheet2, sheet3, ...?
Commandbutton2 on sheet1, sheet2, sheet3, ...?
Commandbutton3 on sheet1, sheet2, sheet3, ...?
???

If yes...

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim MstrWks As Worksheet
Dim OLEObj As OLEObject
Dim CmdBtnName As String
Dim TestCmdBtn As OLEObject

'change this to the sheet that's laid out like you like
Set MstrWks = Worksheets("Sheet1")

For Each OLEObj In MstrWks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CommandButton Then
CmdBtnName = OLEObj.Name
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = MstrWks.Name Then
'skip it
Else
Set TestCmdBtn = Nothing
On Error Resume Next
Set TestCmdBtn = wks.OLEObjects(CmdBtnName)
On Error GoTo 0
If TestCmdBtn Is Nothing Then
MsgBox CmdBtnName & " doesn't exist on:" & wks.Name
Else
If Not (TypeOf TestCmdBtn.Object _
Is MSForms.CommandButton) Then
MsgBox CmdBtnName & " isn't a commandbutton on " _
& wks.Name
Else
With TestCmdBtn
.Top = OLEObj.Top
.Left = OLEObj.Left
.Width = OLEObj.Width
.Height = OLEObj.Height
End With
End If
End If
End If
Next wks
End If
Next OLEObj

End Sub
 
Back
Top