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!
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 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
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
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".
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.
Source: Stackoverflow.com