Can't Add Command Button

[bassnsjp]

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:

[VBA]
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
[/VBA]

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:
[VBA]
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
[/VBA]


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.

[Bob Phillips]

The first one works fine here (Win7, Excel 2010).

[Paul_Hossler]

Shot in the dark, but what happens if you try it without the .Object?

I don't have 2003, so this is just guessing

[VBA]
With myActBtn
.Name = "ReturntoMM"
.Caption = "Return to" & vbNewline & "Main Menu"
.Font.Size = 12
.Font.Bold = True
.Backcolor = vbRed
.Forecolor = vbWhite
End With
[/VBA]

Paul

[bassnsjp]

Ok, the first example works on my home PC, which I'm running Win XP and MS Excel 2002. It's strange that I recorded the macro using MS Excel 2003 and it doesn't run on that system but does on a 2002 system. Frustrating. I wonder if it has anything to do with security settings???

[frank_m]

If Wksdisplay is a sheet name variable, then the first code example works fine on my XP machine with Excel 2003

But I seem to recall having better consistency declaring the Button as an Object, rather than an OLEObject.

And just for the heck of it, I tried Pauls guess about perhaps removing Object from before Caption, Fontsize, Bold and Color. - That doesn't work.