Creating buttons using VBA
 

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

Run time error 438;
Object: doesn't support this property or method
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

Creating buttons using VBA
 

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-----
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

Run time error 438;
Object: doesn't support this property or method
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


.

Creating buttons using VBA
 

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


--
Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in


"CT" wrote in message
nk.net...
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

Run time error 438;
Object: doesn't support this property or method
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

Creating buttons using VBA
 

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

"CT" wrote in message ink.net...
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

Run time error 438;
Object: doesn't support this property or method
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

Creating buttons using VBA
 

Just to add another:

Recording a macro is pretty useful in this case -

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.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.Comma ndButton.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).

--
Regards,
Tom Ogilvy




"CT" wrote in message
nk.net...
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

Run time error 438;
Object: doesn't support this property or method
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

Creating buttons using VBA
 

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.Comma ndButton.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 an
Object doesn't support this property or method <<<.
Now 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

Tom Ogilvy wrote in message
...
Just to add another:

Recording a macro is pretty useful in this case -

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.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.Comma ndButton.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).

--
Regards,
Tom Ogilvy




"CT" wrote in message
nk.net...
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

Run time error 438;
Object: doesn't support this property or method
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

Creating buttons using VBA
 

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.

--
Regards,
Tom Ogilvy

"CT" wrote in message
k.net...
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.Comma ndButton.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 an
Object doesn't support this property or method <<<.
Now 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

Tom Ogilvy wrote in message
...
Just to add another:

Recording a macro is pretty useful in this case -

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.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.Comma ndButton.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).

--
Regards,
Tom Ogilvy




"CT" wrote in message
nk.net...
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

Run time error 438;
Object: doesn't support this property or method
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

Thanks, Tom. Creating buttons using VBA
 

Thanks, Tom! Sorry to make you re-hash this bit.
Tom Ogilvy wrote in message
...
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.

--
Regards,
Tom Ogilvy

"CT" wrote in message
k.net...
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.Comma ndButton.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 an
Object doesn't support this property or method <<<.
Now 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

Tom Ogilvy wrote in message
...
Just to add another:

Recording a macro is pretty useful in this case -

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.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.Comma ndButton.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).

--
Regards,
Tom Ogilvy




"CT" wrote in message
nk.net...
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

Run time error 438;
Object: doesn't support this property or method
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