Disable button using Excel VBA

Question

I want to disable a button with VBA code like this:

ActiveSheet.Shapes("Button 1").ControlFormat.Enabled = False

I tried:

Set b1 = ActiveSheet.Buttons("Button 1")
b1.Enabled = False

And:

Me.Shapes("Button 1").ControlFormat.Enabled = False

My button name is correct, because it doesn't give me an error message, so the code is completely run through.

After this script I can click on that button and the assigned macro runs. Nothing should happen when I click on it.

Answer 1

I suggest to create a shadow button/shape with exactly same size/position, but different color (fill and/or text to your liking) and no macro/action attached. Then just change the .visible property of your primary shape. Visible = button is active; not visible button is e.g. grayed out and has no action/is passive.

Answer 2

Probably you are using ActiveX Button. Try this:

Sheets("Sheet1").CommandButton1.Enabled = False   '--->change sheet name as required

EDIT: ______________________________________________________________________________

For a Form control Button the following line

ActiveSheet.Shapes("Button 1").ControlFormat.Enabled = False

disables the button i.e. click event will no longer work but the appearance of the button does not change which gives an impression that the button is still active. So work around for that is to change the color of the text of the button as follows:

Sub disable_button_2()
    Dim myshape As Shape: Set myshape = ThisWorkbook.Worksheets("Sheet1").Shapes("Button 2")
    With myshape
       .ControlFormat.Enabled = False    '---> Disable the button
       .TextFrame.Characters.Font.ColorIndex = 15    '---> Grey out button label
    End With
End Sub

And to bring back button to its original state write:

Sub activate_button_2()
    Dim myshape As Shape: Set myshape = ThisWorkbook.Worksheets("Sheet1").Shapes("Button 2")
    With myshape
       .ControlFormat.Enabled = True    '---> Enable the button
       .TextFrame.Characters.Font.ColorIndex = 1    '---> Highlight button label
    End With
End Sub

Answer 3

Disabling a Form button (not talking ActiveX here) does not prevent the assigned macro to run and does not gray out the button. The code below does exactly that based on the version got from Excel. If you did not assign a name to your Form button, you can also use (Buttons(1).

If Excel version = 16 or higher the button is "enabled" by making it black and assigning my macro, else the button is "disabled" by making it gray and assigning no action to it.

Code can e.g. reside in Private Sub Worksheet_Activate() within sheet "Test Sheet"

If Application.Version < 16 Then

    Worksheets("Test Sheet").Buttons("button_name").Font.Color = 8421504
    Worksheets("Test Sheet").Buttons("button_name").OnAction = ""

Else

    Worksheets("Test Sheet").Buttons("button_name").Font.Color = 0
    Worksheets("Test Sheet").Buttons("button_name").OnAction = "'Name of the workbook.xlsm'!my_macro_name"

End If