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 = Falsedoes 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
