Rename an Excel button caption from VBA

[garybug]

I have dynamically created a command button in VBA & have assigned code to the click event, but I can't seem to rename the Caption from the default CommandButton1. I have tried ActiveSheet.CommandButton1.Caption:="Top 20", but it gives me a runtime error '438', object doesnt support this property or method' - but bizarrely, if I create a macro with the same line, it works!

Here is my code
' Create Button
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=679.5, Top:=154.5, _ Width:=96, Height:= 24.75).Select

Selection.Verb Verb:=xlPrimary

' Rename Button - *** ERROR LINE ***
ActiveSheet.CommandButton1.Caption = "Run"

Many Thanks

Gary

 

[Bowers74]

This should help:

Sub CreateButton()
Dim oleobj As OLEObject
' Create Button
Set oleobj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
    DisplayAsIcon:=False, Left:=679.5, Top:=154.5, Width:=96, Height:=24.75)
' Name Button's VBA Reference and change caption
With oleobj
    .Name = "cmdRun"

.Object.Caption = "Run"

End With
Set oleobj = Nothing
End Sub

You just needed to reference the Object and not the Shape!

I hope this helps.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[garybug]

Mike, you are a star

Ta much