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.

I have read that there is a bug in Excel 2010, is that the version you're using? On this page are some suggestions that you could try out, please let me know if one of them worked: answers.microsoft.com/en-us/office/forum/… — Sun
– Sun, Commented May 23, 2016 at 9:58
I am using office 2013. I saw the thread and the solution is not too optimal... — Twi
– Twi, Commented May 23, 2016 at 10:41
@Sun Thanks! I am using office 2013. I saw the thread and the solution is not too optimal. — Twi
– Twi, Commented May 24, 2016 at 8:06
Unfortunately, I also only know the code that Mrig has written in his answer below. I'm also still on Excel 2007. — Sun
– Sun, Commented May 24, 2016 at 8:16

yakobom · Accepted Answer · 2017-02-14 15:33:28Z

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

edited Feb 14, 2017 at 15:33

yakobom

2,7111 gold badge27 silver badges35 bronze badges

answered Feb 14, 2017 at 14:40

Working button

462 bronze badges

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

Comments

Mrig · Accepted Answer · 2016-05-23 12:27:00Z

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

edited May 23, 2016 at 12:27

answered May 23, 2016 at 9:56

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

7 Comments

Twi Over a year ago

No, I am using Form controls

Mrig Over a year ago

@Twi - Then 'ActiveSheet.Shapes("Button 1").ControlFormat.Enabled = true' you mentioned in your question should work.

Mrig Over a year ago

@Twi - If you can share your file I can look into it.

Twi Over a year ago

Thanks but it's not working in Excel 2013. I mean it's color the button inactive, but the disable property is not active somehow. The messagebox text message still pop up after disabling.

Twi Over a year ago

Which Office version do you use?

|

Dschuli · Accepted Answer · 2019-01-25 21:21:30Z

0

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.

answered Jan 25, 2019 at 21:21

Dschuli

3793 silver badges12 bronze badges

Comments

Tavious · Accepted Answer · 2021-02-02 11:20:30Z

Only tested on Excel 2016 x86
I continued to receive errors utilizing .ControlFormat. solutions.
After much searching I found another solution that worked great for my needs of disabling Shapes/Buttons.

To mimic the .Enabled property of a uf control, you might toggle the .OnAction property of a shape.

Function ShapeIsEnabled(aShape As Shape) As Boolean
    ShapeIsEnabled = (aShape.OnAction <> "")
End Function

Sub EnableShapeMacro(aShape As Shape)
    aShape.OnAction = aShape.AlternativeText
End Sub

Sub DisableShapeMacro(aShape As Shape)
    aShape.AlternativeText = aShape.OnAction
    aShape.OnAction = vbNullString
End Sub

Note the use of the .AlternativeText property to store the macro name.
source: mikerickson
https://www.excelforum.com/excel-programming-vba-macros/1267897-disable-action-of-macro-enabled-shape.html#post5080833

Collectives™ on Stack Overflow

Disable button using Excel VBA

4 Answers 4

Comments

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related