Disable/Enable button in Excel/VBA

Question

I'm trying the following function in VBA/Excel:

Sub function_name()
  button.enabled=false
  Call Long_Function       ' duration: 10sec
  button.enabled=true
End Sub

For some reason, this button disabling does not work (it stays enabled in the excel work sheet) I tried experimenting with DoEvents and delays, but no luck there. Any ideas? Thanks!

Axel Kemper · Accepted Answer · 2015-03-30 19:00:47Z

24

The following works for me (Excel 2010)

Dim b1 As Button

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

b1.Font.ColorIndex = 15
b1.Enabled = False
Application.Cursor = xlWait
Call aLongAction
b1.Enabled = True
b1.Font.ColorIndex = 1
Application.Cursor = xlDefault

Be aware that .enabled = False does not gray out a button.

The font color has to be set explicitely to get it grayed.

edited Mar 30, 2015 at 19:00

answered Jan 2, 2013 at 15:01

Axel Kemper

11.5k2 gold badges35 silver badges60 bronze badges

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

4 Comments

ForceMagic Over a year ago

Good answer! The last sentence could be at the beginning of the post, I had to come twice because I didn't read all of it! Silly me :P

Kes Perron Over a year ago

I think it's important to note that, in Excel 2010, setting b1.Enabled = False does not stop the actions associated with that button from occurring if it is clicked.

Andreas Covidiot Over a year ago

... and to deactivate the action (event), it must be unassigned (Excel 2010) or one creates two buttons on the same spot switching their visibility as described here: stackoverflow.com/a/566965/1915920

Chris Peh Dec 17, 2024 at 4:51

For commandbutton it needs to read b1.ForeColor = 15

John Bustos · Accepted Answer · 2013-01-02 13:37:01Z

4

... I don't know if you're using an activex button or not, but when I insert an activex button into sheet1 in Excel called CommandButton1, the following code works fine:

Sub test()

   Sheets(1).CommandButton1.Enabled = False

End Sub

Hope this helps...

answered Jan 2, 2013 at 13:37

John Bustos

19.7k18 gold badges102 silver badges162 bronze badges

1 Comment

Jon V Over a year ago

this works perfect, i think it has to do with 'screen refresh' or something while the 'long function' is running

Antonio · Accepted Answer · 2014-04-22 07:37:11Z

2

too good !!! it's working and resolved my one day old problem easily

Dim b1 As Button

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


b1.Enabled = False

edited Apr 22, 2014 at 7:37

Antonio

9,89215 gold badges63 silver badges91 bronze badges

answered Apr 22, 2014 at 7:14

mansi

611 bronze badge

Comments

RobC · Accepted Answer · 2019-08-08 18:16:42Z

2

Others are correct in saying that setting button.enabled = false doesn't prevent the button from triggering. However, I found that setting button.visible = false does work. The button disappears and can't be clicked until you set visible to true again.

edited Aug 8, 2019 at 18:16

RobC

25.3k21 gold badges87 silver badges88 bronze badges

answered Aug 8, 2019 at 17:44

gi_jimbo

435 bronze badges

Comments

Jonaithan · Accepted Answer · 2017-07-21 22:08:46Z

1

This is working for me (Excel 2016) with a new ActiveX button, assign a control to you button and you're all set.

Sub deactivate_buttons()

     ActiveSheet.Shapes.Item("CommandButton1").ControlFormat.Enabled = False

End Sub

It changes the "Enabled" property in the ActiveX button Properties box to False and the button becomes inactive and greyed out.

answered Jul 21, 2017 at 22:08

Jonaithan

111 bronze badge

Comments

Hasitha · Accepted Answer · 2020-11-03 19:47:58Z

0

I'm using excel 2010 and below VBA code worked fine for a Form Button. It removes the assigned macro from the button and assign in next command.

To disable:

ActiveSheet.Shapes("Button Name").OnAction = Empty
ActiveSheet.Shapes("Button Name").DrawingObject.Font.ColorIndex = 16

To enable:

ActiveSheet.Shapes("Button Name").OnAction = ActiveWorkbook.Name & "!Macro function Name with _Click"
ActiveSheet.Shapes("Button Name").DrawingObject.Font.ColorIndex = 1

Pls note "ActiveWorkbook.Name" stays as it is. Do not insert workbook name instead of "Name".

answered Nov 3, 2020 at 19:47

Hasitha

1

Comments

bonCodigo · Accepted Answer · 2013-01-02 13:47:05Z

-1

This is what iDevelop is trying to say Enabled Property

So you have been infact using enabled, coz your initial post was enable..

You may try the following:

Sub disenable()
  sheets(1).button1.enabled=false
  DoEvents
  Application.ScreenUpdating = True

  For i = 1 To 10
    Application.Wait (Now + TimeValue("0:00:1"))
  Next i

  sheets(1).button1.enabled = False
End Sub

edited Jan 2, 2013 at 13:47

answered Jan 2, 2013 at 13:38

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

2 Comments

bonCodigo Over a year ago

@Jonathan it seems like your waiting function doesn't seem to pull the desired trigger to change button properties. So please give this a try.

Jon V Over a year ago

I found this too online, but this does not do the trick. I think this 'trigger' indeed is the problem. If I break after my function, the button does gets inactive.

Collectives™ on Stack Overflow

Disable/Enable button in Excel/VBA

7 Answers 7

4 Comments

1 Comment

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

1 Comment

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related