bassnsjp
02-17-2012, 01:27 PM
I'm operating on Windows XP using MS Office 2003. Unfortunately the computer I'm using cannot connect to the internet so I'm limited in providing example code.
I want to add a command button in a spreadsheet dynamically via VBA and am having difficulty getting it to work. I searched the web and used various examples to noavail. I also recorded a macro inserting a command button and attempted to re-use the macro to noavail. I continually get this error message regardless of the method I try.
"Object does not support this property or method"
Here is code for one attempt:
Dim myCmdBtn as OLEObject
Set myCmdBtn = Worksheets(Wksdisplay).OLEObjects.Add(Classtype:="Forms.CommandButton.1", _
Link:=False, DisplayasIcon:=False, Left:=903, Top:=26, Width:=71, Height:=32)
With myCmdBtn
.Name = "ReturntoMM"
.Object.Caption = "Return to" & vbNewline & "Main Menu"
.Object.Font.Size = 12
.Object.Font.Bold = True
.Object.Backcolor = vbRed
.Object.Forecolor = vbWhite
End With
Funny thing is even though I get the error the button caption and the back and fore ground colors are set. But the bold and font size is not.
Here is another example:
Dim myActBtn as CommandButton
ActiveSheet.Cells(2, 14).Select
With ActiveCell
Set myActBtn = ActiveSheet.CommandButton.Add(Left:=903, Top:=26, Width:=71, Height:=32)
End With
With myActBtn
.Name = "ReturntoMM"
.Object.Caption = "Return to" & vbNewline & "Main Menu"
.Object.Font.Size = 12
.Object.Font.Bold = True
.Object.Backcolor = vbRed
.Object.Forecolor = vbWhite
End With
Again the same attributes stated above apply to the button on the worksheet. The code to be assigned to the button is already
This is very frustrating and I've spent hours trying to get this to execute without error. Any assistance would be greatly appreciated.
I want to add a command button in a spreadsheet dynamically via VBA and am having difficulty getting it to work. I searched the web and used various examples to noavail. I also recorded a macro inserting a command button and attempted to re-use the macro to noavail. I continually get this error message regardless of the method I try.
"Object does not support this property or method"
Here is code for one attempt:
Dim myCmdBtn as OLEObject
Set myCmdBtn = Worksheets(Wksdisplay).OLEObjects.Add(Classtype:="Forms.CommandButton.1", _
Link:=False, DisplayasIcon:=False, Left:=903, Top:=26, Width:=71, Height:=32)
With myCmdBtn
.Name = "ReturntoMM"
.Object.Caption = "Return to" & vbNewline & "Main Menu"
.Object.Font.Size = 12
.Object.Font.Bold = True
.Object.Backcolor = vbRed
.Object.Forecolor = vbWhite
End With
Funny thing is even though I get the error the button caption and the back and fore ground colors are set. But the bold and font size is not.
Here is another example:
Dim myActBtn as CommandButton
ActiveSheet.Cells(2, 14).Select
With ActiveCell
Set myActBtn = ActiveSheet.CommandButton.Add(Left:=903, Top:=26, Width:=71, Height:=32)
End With
With myActBtn
.Name = "ReturntoMM"
.Object.Caption = "Return to" & vbNewline & "Main Menu"
.Object.Font.Size = 12
.Object.Font.Bold = True
.Object.Backcolor = vbRed
.Object.Forecolor = vbWhite
End With
Again the same attributes stated above apply to the button on the worksheet. The code to be assigned to the button is already
This is very frustrating and I've spent hours trying to get this to execute without error. Any assistance would be greatly appreciated.