Programatically adding and modifying command buttons (active X control) - Excel VBA

Question

Hello this have been giving me a headache for a while.

I want to simply be able to remove existing buttons and add new ones. I already have ready made functions xbutton_click() and ybutton_click(). I tried adding buttons like this:

Set btn= ActiveSheet.Buttons.Add(Range("B3").Left, Range("B3").Top, Range("B3").Width, Range("B3").Height)
With openForm
    .OnAction = "new_Click"
    .Caption = "new"
    .Name = "newButton"
End With

but those seem to only work with macros which is not what I want. I realized that when I manually add a button, I add the active X control button. I want that. It has a name property, which can be changed, and its function is basically name_click(), perfect!

Now when it comes to adding them its a headache. I have surfed the internet but it is full of outdated solutions (I am using 2013). I could only add new command buttons, but couldn't change their caption, name, nothing.

this is the code for adding a "command button" (not "button"):

Set objBtn = ws.OLEObjects.Add(ClassType:="Forms.CommandButton.1", link:=False, _
    displayasicon:=False, Left:=celLeft, Top:=celTop, Width:=celWidth, Height:=celHeight)

Adding the ActiveX Command Button works, but I cannot change its properties like "Caption". Can someone please guide me through this maze?

Edit

objBtn.Name = "newName" works. But objBtn.Caption = "newCaption" gives an error 438.

Form controls can be assigned any given macro. Therefore, you have the option with them to assign a macro to them upon creation using the .OnAction. Yet, ActiveX controls do not come with this option. The macro (when clicking on CommandButton1 for example) must be named CommandButton1_Click and must reside on the sheet where the ActiveX command button resides. So, what you are essentially asking for is a macro (VBA code) to change the VBA code residing on a particular sheet. — Ralph
– Ralph, Commented Jun 27, 2016 at 13:15
Sorry, what is it you want, to add ActiveX buttons or a form button. The ActiveX code is listed above — Nathan_Sav
– Nathan_Sav, Commented Jun 27, 2016 at 13:16
There are solutions out there to change the VBA code (with VBA code) for a module in a particular Excel file. Yet, I have not yet come across a solution to change the VBA code on a worksheet using VBA code. Maybe someone here has a solution for that one. — Ralph
– Ralph, Commented Jun 27, 2016 at 13:20
You should be able to use objBtn.Name = "newButton" to set the name of your ActiveX Command Button after it is created. This has worked for me in 2010 and 2016 without issue — JerryT
– JerryT, Commented Jun 27, 2016 at 13:59
In order to change the caption you need to use objBtn.Object.Caption = "" You can find this by adding a 'watch' on your command button and looking at the hierarchy of properties — JerryT
– JerryT, Commented Jun 27, 2016 at 17:27

Sgdva · Accepted Answer · 2016-06-27 19:03:54Z

2

For OLEObjects, you can't do it just a caption this should solve it

Dim objBtn As OLEObject
Set objBtn = ws.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
    DisplayAsIcon:=False)
    objBtn.Object.Caption = "Example"

Notice you were missing .Object.

answered Jun 27, 2016 at 19:03

Sgdva

2,8103 gold badges19 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Programatically adding and modifying command buttons (active X control) - Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related