Is it impossible to edit captions while adding?

[wut]

With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
        DisplayAsIcon:=False, Left:=378, Top:=130.5, Width:=31.5, Height:=15.75)
        .Name = "a"
        .Caption = "a"
End With

Error occurs on the red line. I can edit the caption just fine, but I have to do it in a separate block. The name sets fine, why won't the caption?

 

[diddi]

the problem is in your With implementation... try this

With ActiveSheet.OLEObjects
    .Add(ClassType:="Forms.CommandButton.1", Link:=False, _
        DisplayAsIcon:=False, Left:=378, Top:=130.5, Width:=31.5, Height:=15.75)
    .Name = "a"
    .Caption = "a"
End With

 

[wut]

I actually had it that way originally. It errors out with "Expected "="".

And I didn't realize when I wrote the OP that, in fact, I can't set the caption in the same macro at all. The caption line fails unless I put it into another procedure entirely.

 

[wut]

It doesn't even work if I call the "Caption Renaming" line from another procedure or module... Which means that I can't create a button and redefine its caption in the same call.

I literally recorded myself creating a command button and all I'm trying to do is to change the caption using VBA.

Someone must have encountered the same problem...? Try it, you'll experience exactly the same issue.

 

[wut]

Look, here's the code I'm using. I'm going to lunch here. I'd appreciate it if somebody would at least try this out and verify that I'm not crazy.

    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
        DisplayAsIcon:=False, Left:=290.25, Top:=34.5, Width:=102.75, Height:=32.5)
        .Name = "UpdateMedia"
[COLOR=red]        .Caption = "Update Media List"
        .Font.Bold = True[/COLOR]
    End With

The first code fails at the red lines. So cut the red lines into a separate procedure and try running that procedure separately, like so:

With ActiveSheet.UpdateMedia
    .Caption = "Update Media List"
    .Font.Bold = True
End With

 

[diddi]

what if you tried a doevents in the middle of the sub?

    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
        DisplayAsIcon:=False, Left:=290.25, Top:=34.5, Width:=102.75, Height:=32.5)
        .Name = "UpdateMedia"
        DoEvents
        .Caption = "Update Media List"
        .Font.Bold = True
    End With

 

[wut]

No dice. I appreciate your effort. I can't help but wonder if you were on the right track in the first place.

When I try to step through the code, it tries to enter break mode, but says it "can't at this time". I even re-recorded the macro to get it back in its exact state.

sub Macro1()
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=415.5, Top:=96.75, Width:=102.75, Height _
:=39).Select
End Sub

I originally balked at the idea of having to "select" the button I had just created, which was why I replaced the "select" with "with-end with". There's no difference though. Before the code even finishes running it fails.

Earlier today somebody mentioned a bug having to do with these command buttons. I'm wondering if this is remotely related. Maybe I'll try asking there.

Thanks again for your effort.

 

[kolle_hond]

With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
        DisplayAsIcon:=False, Left:=378, Top:=130.5, Width:=31.5, Height:=15.75)
        .Name = "a"
        .Object.Caption = "a"
End With

Just figured this out myself

 

[wut]

Hell of a first post. Thanks a million.