Use VBA to add a command button to a spreadsheet 2

[YerMom]

I have the following VBA code that adds a command button to a spreadsheet:

indexWorksheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", Left:=0, Top:=0, Width:=126.75, Height:=25.5

But this code defaults the button caption to "CommandButton1" and I want to change the caption. I tried the following, but it produced an error message when I ran it:

indexWorksheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", Caption:="Click Me", Left:=0, Top:=0, Width:=126.75, Height:=25.5

How can I set the caption on the button?


Thanks.

 

[SkipVought]

Hi,

with indexWorksheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", Left:=0, Top:=0, Width:=126.75, Height:=25.5
  .caption = "Click Me"
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveInIowa]

If you're creating the button via VBA, how do you wire it up to its click event? If manual intervention is needed to do that, you may as well set its caption manually via its properties page as well.

Here's another option for creating a button:

    Dim xlBtn As Excel.Button

    Set xlBtn = indexWorksheet.Buttons.Add(0, 0, 126.75, 25.5)
    With xlBtn
        .Caption = "YourCaption"
        .OnAction = "YourMacro"
    End With

 

[YerMom]

Thanks Skip and Dave. I have questions for both of you.

Skip:
I tried your solution, but the ide gave me the following compile error:

Expected: end of statment

Did you mean something like this:

With indexWorksheet.OLEObjects.Add
    .ClassType = "Forms.CommandButton.1"
    .Left = 90
    .Top = 0
    .Width = 126.75
    .Height = 25.5
    .Caption = "Click Me"
End With

But even that produced a run-time error.

Dave:
Your solution works.
After adding your solution, I typed indexWorksheet. and the code completion popup appeared, but it did not contain "Buttons" do you have any idea why?

 

[PHV]

I think that Skip meant this:

With indexWorksheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=0, Top:=0, Width:=126.75, Height:=25.5)
  .Caption = "Click Me"
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

This format seems to work...

Sheet1.OLEObjects.Add ClassType:="Forms.CommandButton.1", Left:=0, Top:=0, Width:=126.75, Height:=25.5
Sheet1.Shapes(Sheet1.Shapes.Count).OLEFormat.Object.Object.Caption = "Click Me"

"Discovered" using faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[YerMom]

Thanks Skip. That final form of the command worked.