Hard Coding A Button Using "Buttons.Add(x1,x2,x3,x4)"
 

Hi all,

I'm writing a Excel sheet that requires a button to be inserted for a
certain situation in a macro. I didn't know the instructions to create
a button so i recorded a macro that did what i wanted then looked at
the generated code. The recorded macro gave me this:

ActiveSheet.Buttons.Add(x1,x2,x3,x4)

where x1...x4 are numbers. Does anybody know what these numbers are
for? I can't seem to find it in the help menu.

Thanks
Bill

Hard Coding A Button Using "Buttons.Add(x1,x2,x3,x4)"
 

Hi Bill

left
top
width
height


Sub test()
Dim WS As Worksheet
Dim Btn As OLEObject
Set WS = ThisWorkbook.Worksheets("Sheet1")
With WS
Set Btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1" , _
Left:=.Range("C3").Left, Top:=.Range("C3").Top, _
Width:=100, Height:=30)
End With
Btn.Object.Caption = "Click Me"
Btn.Name = "TheButton"
With ThisWorkbook.VBProject.VBComponents(WS.CodeName).C odeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
"Msgbox ""Hi there"" "
End With
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message oups.com...
Hi all,

I'm writing a Excel sheet that requires a button to be inserted for a
certain situation in a macro. I didn't know the instructions to create
a button so i recorded a macro that did what i wanted then looked at
the generated code. The recorded macro gave me this:

ActiveSheet.Buttons.Add(x1,x2,x3,x4)

where x1...x4 are numbers. Does anybody know what these numbers are
for? I can't seem to find it in the help menu.

Thanks
Bill

Hard Coding A Button Using "Buttons.Add(x1,x2,x3,x4)"
 

Thanks!

Ron de Bruin wrote:
Hi Bill

left
top
width
height


Sub test()
Dim WS As Worksheet
Dim Btn As OLEObject
Set WS = ThisWorkbook.Worksheets("Sheet1")
With WS
Set Btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1" , _
Left:=.Range("C3").Left, Top:=.Range("C3").Top, _
Width:=100, Height:=30)
End With
Btn.Object.Caption = "Click Me"
Btn.Name = "TheButton"
With ThisWorkbook.VBProject.VBComponents(WS.CodeName).C odeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
"Msgbox ""Hi there"" "
End With
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message oups.com...
Hi all,

I'm writing a Excel sheet that requires a button to be inserted for a
certain situation in a macro. I didn't know the instructions to create
a button so i recorded a macro that did what i wanted then looked at
the generated code. The recorded macro gave me this:

ActiveSheet.Buttons.Add(x1,x2,x3,x4)

where x1...x4 are numbers. Does anybody know what these numbers are
for? I can't seem to find it in the help menu.

Thanks
Bill