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

User · Answer

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

User · Answer

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

User · Answer

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  quot Button Name quot   OnAction   Empty ActiveSheet Shapes  quot Button Name quot   DrawingObject Font ColorIndex   16  To enable  ActiveSheet Shapes  quot Button Name quot   OnAction   ActiveWorkbook Name  amp   quot  Macro function Name with  Click quot  ActiveSheet Shapes  quot Button Name quot   DrawingObject Font ColorIndex   1  Pls note  quot ActiveWorkbook Name quot  stays as it is  Do not insert workbook name instead of  quot Name quot

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[vba] Disable/Enable button in Excel/VBA

Examples related to vba

Examples related to excel