none
Disable Button Form Control on Excel 2010 worksheet not working RRS feed

  • Question

  • I've got a legacy Excel 2003 workbook with several button form controls located on a worksheet. In Excel 2003 I am able to disable a button using the following code:

    Worksheets("Sheet 1").Activate
    Worksheets("Sheet 1").Shapes("Button 1").Select
    With Selection
       .Enabled = False ' Disable the button
       .Font.ColorIndex = 15 ' Grey out button label
    End With
    

    However, in Excel 2010, the Enabled property has no effect on the button control. i.e. the button traps the click event and fires the assigned macro. The property is correctly set however, and it can be tested within the destination macro. This is not a satisfactory workaround as the button still exhibits the "depressed" behaviour when clicked. Also worth noting is that the ColorIndex property does behave correctly, i.e. the button label text colour is set to grey.

    I have tried various syntax combinations for setting the Enabled property including:

    ActiveSheet.Shapes("Button 1").OLEFormat.Object.Enabled = False

    and

    ActiveSheet.Shapes("Button 1").ControlFormat.Enabled = False

    both of which have the same effect as the original code. Anyone know if this property has been deprecated in VBA 2010 and/or whether there is a workaround?

     


    • Edited by ssolig Friday, January 20, 2012 8:34 AM
    Friday, January 20, 2012 8:27 AM

Answers

  • I hadn't noticed before but your conclusions appear correct, I'll report it.

    To prevent the code from running try this in the assigned macro

    If ActiveSheet.Buttons(Application.Caller).Enabled = False Then
    ' MsgBox "disabled"
    Exit Sub
    End If
    ' rest of macro here

    I don't see any way to prevent the button being depressed so maybe consider an ActiveX button if that's important

    In passing no need for all that Select and Activate stuff, try something like this

    Dim btn As Button
    Set btn = Worksheets("Sheet1").Buttons("Button 1")
    btn.Enabled = False
    'btn.Font.ColorIndex = 15
    btn.Font.Color = RGB(200, 200, 200)

    In 2010 it might be more effective to change the font's RGB as above rather than colorindex

    Peter Thornton

    Friday, January 20, 2012 9:29 AM
    Moderator