Excel 2010 - Disable button

Question

I got 3 different form buttons on my spreadsheet. I want to disable 2 of them while one is pressed. Is that possible?

In C# the button can be true or false, but I can't find any examples of this in VBA Excel 2010?

Thanks in advance

Answer 1

You cannot disable Form Buttons. If you want to use that functionality then use the ActiveX button.

However there is an alternative. Create 2 Public Boolean Variables and then in the click event of Button 1 Set the variables to True or False. Depending on the Boolean variables, the other 2 buttons will run their code or not. For example

Option Explicit

Dim enableB2 As Boolean, enableB3 As Boolean

Sub Button1_Click()
    If enableB2 = False Then
        enableB2 = True: enableB3 = True
    Else
        enableB2 = False: enableB3 = False
    End If

    '
    '~~> Rest of the code
    '
End Sub

Sub Button2_Click()
     If enableB2 = True Then
        '
        MsgBox "Hello You clicked Button 2"
        '
     End If
End Sub

Sub Button3_Click()
     If enableB3 = True Then
        '
        MsgBox "Hello You clicked Button 3"
        '
     End If
End Sub

Answer 2

I just wanted to offer an alternative approach for anyone hitting this from Google (that prefers not to use ActiveX buttons): rather than using global Boolean variables, you can grey the text in the button when it is disabled, and check the text colour in the macro assigned to the button before running the macro.

For example, if you have the following sub:

Public Sub SetFormButtonEnabled(ByVal oWks As Object, ByVal sName As String, ByVal bValue As Boolean) As Boolean
    If blnValue Then
       ' Enabled: black text
       oWks.Shapes(sName).TextFrame.Characters.Font.ColorIndex = 1
    Else
       ' Disabled: grey text
       oWks.Shapes(sName).TextFrame.Characters.Font.ColorIndex = 16
    End If
End Sub

and function:

Public Function GetFormButtonEnabled(ByVal oWks As Object, ByVal sName As String) As Boolean
    ' Enabled if text colour is black, otherwise it is disabled
    GetFormButtonEnabled = (oWks.Shapes(sName).TextFrame.Characters.Font.ColorIndex = 1)
End Function

then you can use the SetFormButtonEnabled function in the same way as you'd use the ActiveX button's button.Enabled property, and use GetFormButtonEnabled to check the button is enabled before executing the click macro.

To use these to answer the initial question, with a buttons named btnTest1-3 (I name the buttons with this sort of convention after creating, but Button 1-3 would also work fine) and click macros btnTest1_Click (again mimicing the ActiveX convention, but can be named anything):

Public Sub btnTest1_Click()
    If Not GetFormButtonEnabled(Me, "btnTest1") Then Exit Sub
    SetFormButtonEnabled Me, "btnTest2", False
    SetFormButtonEnabled Me, "btnTest3", False
    ' Do some work...
End Sub

Public Sub btnTest2_Click()
    If Not GetFormButtonEnabled(Me, "btnTest2") Then Exit Sub
    ' Do some work...
End Sub

Public Sub btnTest3_Click()
    If Not GetFormButtonEnabled(Me, "btnTest3") Then Exit Sub
    ' Do some work...
End Sub