CODEWITHSUNDEEP
vbaexcel
Jon V
Jon V

Reputation: 536

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!

Upvotes: 11

Views: 197608

Answers (7)

Hasitha
Hasitha

Reputation: 1

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

Upvotes: 0

gi_jimbo
gi_jimbo

Reputation: 43

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.

Upvotes: 2

Jonaithan
Jonaithan

Reputation: 11

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.

Upvotes: 1

Axel Kemper
Axel Kemper

Reputation: 11332

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.

Upvotes: 24

mansi
mansi

Reputation: 61

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

Upvotes: 2

bonCodigo
bonCodigo

Reputation: 14361

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

Upvotes: -1

John Bustos
John Bustos

Reputation: 19574

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

Upvotes: 3

Related Questions