Create a form button
 

I'm developing a macro to create buttons and want to feed the process with
variables for placement and size. I'm using this command:

ActiveSheet.Buttons.Add(355, 56, 60, 9).Select

where I'm trying to substitute a variable or variables for the values. When
I do I get an error telling me that this isn't optional. Is there any way to
pass these values through a variable?

Thanks

Create a form button
 

I like to position buttons over the cells. So I'll pick out a range and plop
buttons over those cells:

Option Explicit
Sub testme01()
Dim myRng As Range
Dim myCell As Range
Dim myBTN As Button

With ActiveSheet
.Buttons.Delete 'nice for testing
Set myRng = .Range("a1:A5")
For Each myCell In myRng.Cells
With myCell
Set myBTN = .Parent.Buttons.Add(Top:=.Top, _
Height:=.Height, _
Left:=.Left, _
Width:=.Width)
End With
With myBTN
.Name = "BTN_" & .TopLeftCell.Address(0, 0)
.Caption = "Click Me!"
.OnAction = "'" & ThisWorkbook.Name & "'!myMacroHere"
End With
Next myCell
End With
End Sub
Sub mymacrohere()
Dim myBTN As Button
Set myBTN = ActiveSheet.Buttons(Application.Caller)
With myBTN
MsgBox .Name & vbLf & .Caption & vbLf & .TopLeftCell.Address(0, 0)
End With
End Sub

BB Ivan wrote:

I'm developing a macro to create buttons and want to feed the process with
variables for placement and size. I'm using this command:

ActiveSheet.Buttons.Add(355, 56, 60, 9).Select

where I'm trying to substitute a variable or variables for the values. When
I do I get an error telling me that this isn't optional. Is there any way to
pass these values through a variable?

Thanks

--

Dave Peterson

Create a form button
 

That's great! Thanks!

"Dave Peterson" wrote:

I like to position buttons over the cells. So I'll pick out a range and plop
buttons over those cells:

Option Explicit
Sub testme01()
Dim myRng As Range
Dim myCell As Range
Dim myBTN As Button

With ActiveSheet
.Buttons.Delete 'nice for testing
Set myRng = .Range("a1:A5")
For Each myCell In myRng.Cells
With myCell
Set myBTN = .Parent.Buttons.Add(Top:=.Top, _
Height:=.Height, _
Left:=.Left, _
Width:=.Width)
End With
With myBTN
.Name = "BTN_" & .TopLeftCell.Address(0, 0)
.Caption = "Click Me!"
.OnAction = "'" & ThisWorkbook.Name & "'!myMacroHere"
End With
Next myCell
End With
End Sub
Sub mymacrohere()
Dim myBTN As Button
Set myBTN = ActiveSheet.Buttons(Application.Caller)
With myBTN
MsgBox .Name & vbLf & .Caption & vbLf & .TopLeftCell.Address(0, 0)
End With
End Sub

BB Ivan wrote:

I'm developing a macro to create buttons and want to feed the process with
variables for placement and size. I'm using this command:

ActiveSheet.Buttons.Add(355, 56, 60, 9).Select

where I'm trying to substitute a variable or variables for the values. When
I do I get an error telling me that this isn't optional. Is there any way to
pass these values through a variable?

Thanks

--

Dave Peterson