Geek Answers Handbook

Disable / Enable Button in Excel / VBA

I am trying to execute 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 disable button does not work (it remains on on the excel worksheet) I tried to experiment with DoEvents and delays, but no luck. Any ideas? Thanks!

+7
source share
6 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

Remember that .enabled = False does not sedate the button.

The font color must be set explicitly so that it is gray.

+14
source

... I don't know if you use the activex button or not, but when I paste the 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 ...

+2
source

too good!!! it works and quickly resolved my one day problem.

 Dim b1 As Button Set b1 = ActiveSheet.Buttons("Button 1") b1.Enabled = False
+1
source

This works for me (Excel 2016) with a new ActiveX button, assigns a control button to you, and you are all set.

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

It changes the Enabled property in the ActiveX Properties field to False, and the button becomes inactive and inactive.

+1
source

Others are right in saying that setting button.enabled = false does not prevent the button from triggering. However, I found that setting button.visible = false works. The button disappears and cannot be pressed until you set visible to true again.

0
source

This is what iDevelop is trying to say. An included property

So you started using enabled because your initial post was enable ..

You can 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
-1
source

More articles:


All Articles