Creating a Command Button on Sheet using Excel VBA
Sub Create_Command_Button_2007()
‘
‘ Creates a Command button and Positions it
‘ Written by Shasur for http://vbadud.blogspot.com
Dim oOLE As OLEObject
‘ Add a Command Button
oOLE = ActiveSheet.OLEObjects.Add(ClassType:=“Forms.CommandButton.1”, Left:=220, Top:=40, Height:=30, Width:=120)
oOLE.Interior.Color = vbRed
‘ Move and Size with cells
oOLE.Placement = XlPlacement.xlMoveAndSize
oOLE.Object.Caption = “Click Me…”
End Sub
Each OLEObject object represents an ActiveX control or a linked or embedded OLE object.
An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other is not automatically changed to match.
Tags: Add command button to worksheet using VBA, Creating a Command Button on Sheet using Excel VBA, Excel VBA add ActiveX control, Excel VBA add OLEObject object
Leave a comment