disable an activex button in VBA code

Question

I have a few activex buttons on a worksheet, I want to disable one named butInsertRow on certain conditions. What is the VBA code for this?

Thanks

Answer 1

Build the disable mechanism into the button code.  For example, for a button named buttton code like:

Private Sub buttton_Click()

If StopMe Then

    Exit Sub

End If

MsgBox "Good Morning Louise"

End Sub

and in a standard module:

Public StopMe As Boolean

Sub MAIN()

StopMe = False

'  do more stuff

End Sub

Sub routine()

' disable the button code

StopMe = True

End Sub

Running MAIN enables the button logic and running routine disables it.

The advantage to this approach is that the user can see the state of the button by examining the Boolean.

Answer 2

Try code like

ThisWorkbook.Worksheets("Sheet1"). _

    OLEObjects("butInsertRow").Enabled = False