Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Custom Buttons | Excel Discussion (Misc queries) | |||
creating buttons in worksheet | Excel Discussion (Misc queries) | |||
Creating Buttons using the Forms Tool bar. | Excel Worksheet Functions | |||
Creating Macro Buttons and formatting | Excel Discussion (Misc queries) | |||
Creating navigation buttons on a worksheet | New Users to Excel |