Build a string as part of the VBA command syntax

Question

Dim buttonName As String

buttonName = columnCellLetter & "14"

I have a command:

Me.ActiveSheet.ButtonB14.ForeColor = &HFF&

but I want to make the 'ButtonB14' part to be dynamic.

Like: Me.ActiveSheet.&buttonName&.ForeColor = &HFF&

How do I do that?

Tuhin Paul · Answer

The error occurs because the Shape object may not exist or might not have OLEFormat property. In order to handle such cases you need to add some error handling. One way to do this is to wrap the problematic lines in error handler like try catch block. It will display a message box with detailed information about the error if any occur while executing the macro. Another approach could be to check whether the shape object exists before trying to modify its properties.

Tuhin Paul · Answer

You can use the Controls collection of the worksheet to access the button control dynamically by its name. check the code below: Dim buttonName As String buttonName = columnCellLetter & "14" Dim buttonControl As Microsoft.Office.Interop.Excel.OLEObject buttonControl = Me.ActiveSheet.OLEObjects(buttonName) buttonControl.Object.ForeColor = &HFF& In this example, we first create a string variable buttonName which holds the name of the button control we want to modify. Then, we use the OLEObjects property of the worksheet to get a reference to the button control by its name. Finally, we use the Object property of the control to access its properties, such as ForeColor, and modify them as needed.

First Last · Answer

Did not use any error handling. Just tried your first reply. I get:

First Last · Answer

I'm using the ActiveX Form button code. buttonName = columnCellLetter & "14" Me.ActiveSheet.Shapes(buttonName).OLEFormat.Object.ForeColor = &HFF& I get: Here's the ActiveX button: Here's the line failing:

Rajkiran Swain · Answer

To build a string as part of the VBA command syntax, you can use the following code: Dim buttonName As String buttonName = columnCellLetter & "14" Me.ActiveSheet.Shapes(buttonName).OLEFormat.Object.ForeColor = &HFF& In this code, `Shapes(buttonName)` is used to refer to the button on the worksheet, and `OLEFormat.Object.ForeColor` is used to set the button's foreground color. Alternatively, if you are using form controls instead of ActiveX controls, you can use the following code: Dim buttonName As String buttonName = columnCellLetter & "14" Me.ActiveSheet.Buttons(buttonName).Font.Color = &HFF& In this code, `Buttons(buttonName)` is used to refer to the button on the worksheet, and `Font.Color` is used to set the button's foreground color. Note that the `buttonName` variable should be set to the name of the button you want to reference.