Disable a button via a function?

Question

Currently using vba with excel 2007...

I am currently testing the capabilities of functions and am a little stuck using buttons. I have two buttons, named ONE and TWO. Pressing either calls up a function Calc with each button passing the variable of the others name. As follows:

Private Sub ONE_Click()
Calc TWO
End Sub

Private Sub TWO_Click()
Calc ONE
End Sub

Function Calc(B As CommandButton)
B.Enabled = False
End Function

My understanding is that pressing button ONE passes the variable TWO to the Function Calc, and then disables button TWO.

I also have a button labeled Reset which serves as follows:

Private Sub Reset_Click()
ONE.Enabled = True
TWO.Enabled = True
End Sub

The results of this, if I press button ONE, button TWO greys out and appears "disabled". However when I hit the reset button, it stays grey. Investigating the properties of the button reveals that it doesn't actually get disabled. I installed another button that directly disables button two, i.e. TWO.Enabled = False, instead of using a variable to do so.

When the button is directly disabled using the direct button, resetting enables the button as it should, and the properties reflect that the button is disabled.

Would anyone know why using a variable to disable a button like this results in an illusionary disabling? And better yet, how to overcome the issue?

Answer 1

If you have trouble calling through an indirrect reference, then use a static parameter.

Private Sub ONE_Click()
    Calc "TWO"
End Sub

Private Sub TWO_Click()
    Calc "ONE"
End Sub

Now you can use that parameter to act on the object.

Function Calc(B As String)
If B = "ONE" Then
    ONE.Enabled = False
ElseIf B = "TWO" Then
    TWO.Enabled = False
Else
    Exit Function
End If
End Function