CODEWITHSUNDEEP
vbaexcelvbscript
user4819461
user4819461

Reputation:

VBA Excel Toggle Button "Latching"

I have the following code corresponding to three ToggleButtons in a VBA Program in Excel. When one button is clicked, it is supposed to stay "pressed" and the other two buttons are supposed to "release". However, with the code I have, I have to click on one button TWICE, once to release the other buttons and the other to keep the original one pressed.

However, if I add "ToggleButtonX.Value = True" to each of the Subs, when one button is clicked, it cannot release even after clicking another button. How could one configure this so that when one clicks on one button, that button stays pressed AND the other buttons get released?

EDIT: I would like to KEEP TOGGLEBUTTONS. 

Private Sub ToggleButton1_Click()
    ToggleButton2.Value = False
    ToggleButton3.Value = False
End Sub

Private Sub ToggleButton2_Click()
    ToggleButton1.Value = False
    ToggleButton3.Value = False
End Sub

Private Sub ToggleButton3_Click()
    ToggleButton1.Value = False
    ToggleButton2.Value = False
End Sub

Upvotes: 1

Views: 10486

Answers (3)

Scott Holtzman
Scott Holtzman

Reputation: 27249

In addition to the OptionButton answer, which is probably easier and cleaner to implement, there's another way to do it as well by using logic that will only change the values of the other two buttons when one button is clicked. The code for the other buttons Click event will fire, but the variable sButton will determine that the code will only fire on the button that was physically pressed by the user.

Also note the use of Not Me.ToggleButton1.Value. This will ensure that button 2 and 3 are the opposite of button 1 with each click. The way your code was written, it would always revert the other buttons to False no matter what if the clicked button was True or False.

Option Explicit

Public sButton As String

Private Sub ToggleButton1_Click()

ButtonLoad 1

End Sub

Private Sub ToggleButton2_Click()

ButtonLoad 2

End Sub

Private Sub ToggleButton3_Click()

ButtonLoad 3

End Sub

Sub ButtonLoad(iButton As Integer)

Select Case iButton
    Case 1
        If sButton = "" Then
            sButton = "1" 'set so that other buttons don't trigger
            Me.ToggleButton2.Value = Not Me.ToggleButton1.Value
            Me.ToggleButton3.Value = Not Me.ToggleButton1.Value
            sButton = "" 'reset for next button click
        End If
    Case 2
        If sButton = "" Then
            sButton = "2"
            Me.ToggleButton1.Value = Not Me.ToggleButton2.Value
            Me.ToggleButton3.Value = Not Me.ToggleButton2.Value
            sButton = ""
        End If
    Case 3
        If sButton = "" Then
            sButton = "3"
            Me.ToggleButton2.Value = Not Me.ToggleButton3.Value
            Me.ToggleButton1.Value = Not Me.ToggleButton3.Value
            sButton = ""
        End If
End Select
    
End Sub

Upvotes: 3

Carrosive
Carrosive

Reputation: 899

By calling ToggleButtonx.Value = False you are simulating a click on that button and so it own code will run, and set the value of the button you just clicked to false.

Use instead the MouseDown event:

Private Sub ToggleButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ToggleButton2.Value = False
    ToggleButton3.Value = False
End Sub
Private Sub ToggleButton2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ToggleButton1.Value = False
    ToggleButton3.Value = False
End Sub
Private Sub ToggleButton3_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ToggleButton1.Value = False
    ToggleButton2.Value = False
End Sub

Upvotes: 3

RGA
RGA

Reputation: 2607

This is easily done with Option Buttons (If that works with the rest of your implementation)

Private Sub OptionButton1_Click()
    OptionButton1.Value = True
    OptionButton2.Value = False
    OptionButton3.Value = False
End Sub

Private Sub OptionButton2_Click()
    OptionButton1.Value = False
    OptionButton2.Value = True
    OptionButton3.Value = False
End Sub

Private Sub OptionButton3_Click()
    OptionButton1.Value = False
    OptionButton2.Value = False
    OptionButton3.Value = True
End Sub

Upvotes: 0

Related Questions