Creating a command button in VBA and changing it's name & caption?

Question

I have seen many different pages on creating shapes and buttons in VBA macros but none of them worked and I'm getting extremely frustrated.

I tried to use:

 Dim sortBtn As Object
    Set sortBtn = Worksheets("Main").OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
      Link:=False, DisplayAsIcon:=False, Left:=200, Top:=100, Width:= 100, Height:=35)
    sortBtn.Name = "Test"     'So far this works
    Worksheets("Main").Shapes(Test).DrawingObject.Object.Caption = "Test2"
    'The above line doesn't work

Please do not redirect me to another question, I have read over a dozen on this site and none have helped me further than this point. With this I would also like to choose where the button is placed. I've also tried the shape method:

Dim sortBtn As Shape
Set sortBtn = Worksheets("Main").Shapes.AppShape(CommandButton1)

I've also tried:

With sortBtn.OLEFormat.Object
    .Object.Caption = "Test"
    .Name = "Test"
End With

The above also doesn't work with either declaring used above.

Please save me from troubleshooting!

1 too many objects this works .oleformat.object.caption="test" — Nathan_Sav
– Nathan_Sav, Commented Jun 13, 2017 at 7:05
I took out the second object and it said Object doesn't support this property or method (wouldn't even give me a location of the error). — Sam H.
– Sam H., Commented Jun 13, 2017 at 7:19
activesheet.shapes("button 1").oleformat.object.caption works fine for me. I don't think this is right Set sortBtn = Worksheets("Main").Shapes.AppShape(CommandButton1) — Nathan_Sav
– Nathan_Sav, Commented Jun 13, 2017 at 7:26
I would look at declaration of sortBtn and as you are setting this, you can say sortBtn.oleformat.object.caption — Nathan_Sav
– Nathan_Sav, Commented Jun 13, 2017 at 8:17

jmejias · Accepted Answer · 2019-03-25 16:20:12Z

1

Maybe it's a little too late for this answer but I archive an expected result with the following code:

Call Add_Command_Button(Range("R39:S40"), "Next01_Button", "Next")

Sub Add_Command_Button(rngUbicacionControl As Range, sCommandButtonName As String, sCommandButtonCaption As String)
    Dim objCommandButton As Object
    Set objCommandButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
        Link:=False, _
        DisplayAsIcon:=False, _
        left:=rngUbicacionControl.left, _
        top:=rngUbicacionControl.top, _
        width:=rngUbicacionControl.width, _
        height:=rngUbicacionControl.height)
    objCommandButton.Name = sCommandButtonName
    objCommandButton.Object.Caption = sCommandButtonCaption
End Sub

edited Mar 25, 2019 at 16:20

answered Mar 25, 2019 at 15:08

jmejias

13810 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul O Over a year ago

Thanks for adding your answer @jmejias it was exactly what I needed.

CharlesPL · Accepted Answer · 2019-03-25 15:58:50Z

Using the integrated Macro Recorder, it seems like you can probably look through another way around this.

Here is the complete macro

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Buttons.Add(111.75, 60.75, 258, 75.75).Select
    Selection.OnAction = "WhatEverMacroYouNeedtoRunWhenYouClickOnThisButton"
    Selection.Characters.Text = "Test 2"
    With Selection.Characters(Start:=1, Length:=13).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
End Sub

If you look at this code, you will see that it's not really the same type of object. Yours was OLEobject, this one is Button. Yet, I struggle to find documentation on this object... Therefore, I can't really say what is going to work or not, if this class of object is sufficient or not for your application.

So, if we take this and apply it to your code, we get:

Public Sub InsertButton()

    Dim sortBtn As Object
    Set sortBtn = ThisWorkbook.Worksheets("Main").Buttons.Add(Left:=200, _
        Top:=100, Width:=100, Height:=35)

    sortBtn.Name = "Test"
    sortBtn.Caption = "Test 2"  '<---This one works.
    sortBtn.Characters.Text = sortBtn.Characters.Text & vbCrLf & "Test 3" <-- This one works too!

End Sub

Collectives™ on Stack Overflow

Creating a command button in VBA and changing it's name & caption?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related