Use string variable to set object variable in VBA? (Excel 2013)

Question

I have a number of ActiveX controls/buttons on a page, and I would like to modify several of the parameters of the buttons (in a loop function).

I am fine with writing the loop function to achieve this, but cannot find a way to refer to the object using a string variable. I have set up an object variable (as per below), and a string variable to be used to change the reference for the object variable - but can't find a way to get it to work.

This is the code that does NOT work:

Private Sub TrialCode_Click()

Dim ButtonObj As Object
Dim ButtonCaption As String
Dim ButtonString As String

ButtonString = "CommandButton1"
Set ButtonObj = ButtonString

ButtonCaption = "Something"

ButtonObj.Caption = ButtonCaption  'example of the kind of parameters I want to change

End Sub

The Set ButtonObj = ButtonString is the command that fails, reporting a Type Mismatch error.

I'm working in Excel 2013.

I really hope there is some way to do this. Any help will be really appreciated!!!

brettdj · Accepted Answer · 2014-12-23 05:56:45Z

3

The CommandButton belongs to an OLEObject

try

ButtonString = "CommandButton1"
Set ButtonObj = ActiveSheet.OLEObjects(ButtonString)

ButtonCaption = "Something"
ButtonObj.Object.Caption = ButtonCaption  'example of the kind of parameters I want to change

Note that some properties occur directly under ButtonObj, others such as Caption sit below Object

enter image description here

answered Dec 23, 2014 at 5:56

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

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

1 Comment

peege Over a year ago

Using the Locals window to see the hierarchy of the properties is an excellent point.

Collectives™ on Stack Overflow

Use string variable to set object variable in VBA? (Excel 2013)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related