Creating buttons using VBA

  • Thread starter Thread starter CT
  • Start date Start date
C

CT

I am trying to create a button and put it onto a spreadsheet using VBA.

Dim cmdButton As Object
Set cmdButton = CreateObject("Forms.CommandButton.1")
cmdButton.Top = 0
Occurs on cmdButton.Top = 0

I am trying to set the position of the top of the new command button.
Where am I going wrong?

Thanks,
Tim
 
Sub NewButton()

Dim cmdButton As Object

With Range("G2")

Set cmdButton = _
Sheet1.Shapes.AddFormControl(xlButtonControl, _
.Left, .Top, .Width, .Height)



End With


Patrick Molloy
Microsoft Excel MVP
End Sub>-----Original Message-----
 
Try this.

Sub AddButton()
Dim btn1 As Object
Set btn1 = ActiveSheet.Buttons.Add(0, 0, 100, 66)
With btn1
.OnAction = "RunSub"
.Characters.Text = "Caption"
.ShapeRange.Top = 0
.Name = "ButtonName"
End With
End Sub
 
you have not added the button to the form's controls collection.
try this:
Private Sub UserForm_Initialize()
Dim Mycmd As Control
Set Mycmd = Me.Controls.Add("Forms.CommandButton.1", CommandButton1, True)
Mycmd.Top = 100
End Sub
 
Just to add another:

Recording a macro is pretty useful in this case -

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=144.75, _
Top:=88.5, _
Width:=71.25,
Height:=18).Select

You can drop the select

Sub Macro2()
Dim cmbButton As MSForms.CommandButton
Dim oButton As OLEObject

Set oButton = _
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=144.75, _
Top:=88.5, _
Width:=71.25, _
Height:=18)
oButton.Top = 0
Set cmbButton = oButton.Object
cmbButton.Caption = "MyButton"

End Sub

You could set the top property in the ADD method. I have left it as
recorded and then moved the button just to illustrate. The Top property is
an attribute of the OleObject container. The commandbutton itself is the
OleObject.Object (thus my use of two variables).
 
Thanks, Tom. If you could, a bit more help, please ...

Tried the following ...

Sub DA_Create_CMDButtons()
Dim OLEButton As OLEObject
Dim cmdButton As Object

With Range("I1")
Set OLEButton =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
link:=False, _
displayasicon:=False, _
Left:=.Left, _
Top:=.Top, _
Width:=.Width, _
Height:=.Height)
End With
Set cmdButton = OLEButton.Object
cmdButton.Caption = "New"
cmdButton.Name = "cmdNew"
MsgBox ("Here")
End Sub

This pastes the command button nicely inside cell I1 (Thanks, Patrick),
but pukes at >>> cmdButton.Name = "cmdNew" <<< with anNow I could swear a button has a Name property, but it is set off in
parenthesis in the properties window. Why is that, and how do I change it?

More thanks,
-Tim
 
This is how I dim'd cmbButton in my example

Dim cmbButton As MSForms.CommandButton

that works

You changed it to the more appealing cmdButton, but dim't it as object.
This doesn't work. You need to Dim it as MSForms.Commandbutton

Dim cmdButton as MSForms.CommandButton

If it did work, there would be no reason to have the separate variable
cmdButton. It is only for the Name property that this is necessary.
 
Thanks, Tom! Sorry to make you re-hash this bit.
Tom Ogilvy said:
This is how I dim'd cmbButton in my example

Dim cmbButton As MSForms.CommandButton

that works

You changed it to the more appealing cmdButton, but dim't it as object.
This doesn't work. You need to Dim it as MSForms.Commandbutton

Dim cmdButton as MSForms.CommandButton

If it did work, there would be no reason to have the separate variable
cmdButton. It is only for the Name property that this is necessary.
Click to expand...
 
Back
Top