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.
Posts
i created a command button in excel using vba, however it creates the button everytime i click on the command object. how do i make it so that it only creates button once?
ReplyDeletemake oOLE static, and check it every time sub is run.
ReplyDeleteHow can I assign a macro to the button?
ReplyDeleteRight click it and choose "Assign Macro"
ReplyDeleteNot from interface but programmatically!!
ReplyDeleteyes i want the button placed on sheet and macro assigned
ReplyDeleteall thsi shud be done in vb itself also add the button to multiple pages
is there a sample code u can illustrate
.. hey.. can some1 help me add events to my commandbuttons that were added at runtime?
ReplyDeleteThis works for me.
ReplyDeleteActiveSheet.Buttons(1).Text = "AÑADIR REGISTROS A" & Chr(10) & "CHECKLIST"
ActiveSheet.Buttons(1).Name = btnname
ActiveSheet.Buttons(1).OnAction = "import_chgs"
How can we specifically tell the location of the command button button.. For example, I want command button show up in cell (3,4)
ReplyDeleteoOle.Top=ActiveSheet.Cells(3,4).Top
DeleteoOle.Left=ActiveSheet.Cells(3,4).Left
oOle.Height=ActiveSheet.Cells(3,4).Height
oOle.Width=ActiveSheet.Cells(3,4).Width
i copy this codes :
ReplyDeleteSub 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
...
and i paste it on module and i use call function .
its create the button but the problem is it has error . " Object variable or With block variable not set " . what is it mean ?
hi i am shuja and i want to make a button on excel for save the data , and i want that when i click on the button so the form that i filled up that save automatically in the next sheet and make the reste button as well how it wil be , my email is shujaanwar@ymail.com
ReplyDeleteVery helpful. Would you elaborate on the shape and code name of the Object. What does the NAME property change? I create 2 buttons, but the first keeps having code name set as "CommandButton1", but the shape name is what I assigned with NAME property. The 2nd button is created the same way, but shape and code names are the same (as I assigned). Strange stuff.
ReplyDelete