Issue with trying to pass name of command button as a variable

[smk224]

Hello All,

I'm trying to pass name of the command button as a variable to ButtonName first before setting cmdButton.

The name of the button is "Button_CalculationMode"

Sub LoadCalcMode()

Dim cmdButton As CommandButton
Dim ButtonName As String

ButtonName = "Button_CalculationMode"

Set cmdButton = ActiveSheet.ButtonName

cmdButton.Caption = "This is the new caption."

End Sub

Alternatively, if I were to set the button name directly, such as below, it works.

Sub LoadCalcMode()

Dim cmdButton As CommandButton

Set cmdButton = ActiveSheet.Button_CalculationMode

cmdButton.Caption = "This is the new caption."

End Sub

No matter what I've tried from researching online, I keep getting various run-time errors such as 438: object doesn't support this property or method., or type mismatch errors.

I am fairly new to vba. Thanks in advance!

[mikerickson]

Try using this syntax

Set cmdButton = ActiveSheet.OLEObjects(buttonName).Object

[smk224]

Try using this syntax

Set cmdButton = ActiveSheet.OLEObjects(buttonName).Object

That worked! Awesome Thank you!