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!

Answer 1

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.