Disable/Enable button in Excel/VBA

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!

7 Answers

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.

... 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...

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 

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.

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.

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".

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