score:1

Accepted answer

If you want to work with only one food at a time, you should use Option Buttons instead of Commandbuttons. Option Buttons are mutually exclusive - if you select Cheese, Bacon is automatically deselected.

If you want to process multiple foods at once, you should use Toggle Buttons. Toggle Buttons are either up or down, but they're not mutually exclusive. They have the added benefit of looking like Command Buttons.

If you want the mutual exclusivity of Option Buttons, but you must have something that looks and works like a Commandbutton, then you have a couple of options, none of them optimal:

  1. You could use a Tab Strip and hide everything except the tabs.
  2. You could set a module-level variable that remembers the last button pushed. You would probably want to add code that would change the color of the button so the user knows which they pressed. And if you did that, you probably don't need the module-level variable, you could just read which button had the color.
  3. You can make Toggle Buttons mutually exclusive through code. I'd personally go with this one so you get the visual effect of the button being pressed.

Here's some code to get you started

Private mbEventsDisabled As Boolean

Public Property Let EventsDisabled(ByVal bEventsDisabled As Boolean): mbEventsDisabled = bEventsDisabled: End Property
Public Property Get EventsDisabled() As Boolean: EventsDisabled = mbEventsDisabled: End Property

Private Sub tgBacon_Click()

    If Not Me.EventsDisabled Then ClearToggles Me.tgBacon

End Sub


Private Sub tgCheese_Click()

    If Not Me.EventsDisabled Then ClearToggles Me.tgCheese

End Sub

Private Sub tgTomato_Click()

    If Not Me.EventsDisabled Then ClearToggles Me.tgTomato

End Sub

Public Sub ClearToggles(tg As ToggleButton)

    Me.EventsDisabled = True

        Me.tgBacon.Value = Me.tgBacon.Name = tg.Name
        Me.tgCheese.Value = Me.tgCheese.Name = tg.Name
        Me.tgTomato.Value = Me.tgTomato.Name = tg.Name

    Me.EventsDisabled = False

End Sub

If you had more than three toggles, you'd want to refactor the ClearToggles sub to loop instead of calling them out individually.

Public Sub ClearToggles(tg As ToggleButton)

    Dim ctl As Control

    Me.EventsDisabled = True

        For Each ctl In Me.Controls
            If TypeName(ctl) = "ToggleButton" And ctl.Name <> tg.Name Then
                ctl.Value = False
            End If
        Next ctl

    Me.EventsDisabled = False

End Sub

Related Query

More Query from same tag