PDA

View Full Version : Can't Add Command Button



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.

Bob Phillips
02-17-2012, 03:37 PM
The first one works fine here (Win7, Excel 2010).

Paul_Hossler
02-17-2012, 03:48 PM
Shot in the dark, but what happens if you try it without the .Object?

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


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


Paul

bassnsjp
02-18-2012, 01:47 PM
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???:banghead:

frank_m
02-19-2012, 12:32 PM
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.