VBA loop through buttons on UserForm

Question

I have not used VBA so quite new - but all searches have not given me the answer

its a simple question really. I have a group of buttons in an Excel Form. The code is very similar when each one is pressed, and for each pressed button, I would like the colour of the button to change. So in reality, I have something like this for each button

UserForm2.CommandButton17.BackColor = RGB(255,255,0)

I would like to go through each button. Check if it is pressed, and then set the colour accordingly.

I actually want to say something like

for counter in 1 to 100
if (ispressed((CommandButton & counter )) then

I have found the following construct:

Dim ctrl as Control
For Each ctrl in UserForm1.Controls
  ctrl.BackColor = RGB(255,0,0)
end for

this construct works - but I cant figure out how to check if the button is pressed.

Some of the answers show the above construct, with ctrl.Value = True but those are for checkboxes and radio buttons. I don't even get the ctrl.Value option with buttons, so I can't use it anyway

Every example of code I have found glosses over this requirement.

Can someone help

Answer 1

I think that the best thing is to work with event and to intercept Press Button in defining Click() event for all buttons like this

'Form variable to define at begin on code
Dim pbPressed as Control
Dim pbLastPressed as Control

Private Sub pbButton(X)_Click()
    'restore previous color only to last pressed 
    Set pbLastPressed.BackColor = RGB(0,0,155)
    Set pbPressed = pbButton(X)

    'assign color-pressed to button pressed
    pbPressed = RGB(255,0,0)
End sub

where (X) must be replaced by a number as 1 or 2 or 10 !

You can make a fonction, and you obtain

Private Sub pbButton1_Click()
    Call ChangeButtonsColor(pbButton1)
End Sub 

Private Sub pbButton2_Click()
    Call ChangeButtonsColor(pbButton2)
End Sub 

Private Sub pbButton3_Click()
    Call ChangeButtonsColor(pbButton3)
End Sub 

Private Sub ChangeButtonsColor(pb as Button)
    'restore previous color only to last pressed 
    Set pbLastPressed.BackColor = RGB(0,0,155)
    Set pbPressed = pb

    'assign color-pressed to button pressed
    pbPressed = RGB(255,0,0)
End sub

Don't forget to add other event as KeyPress() that can make same action than clicking the Button.

If you have more than 10 buttons, you can perhaps create the buttons dynamically.

Answer 2

Use a ToggleButton instead of a CommandButton if you want it to represent a state.

To initialize a state for each toggle button you can loop through the control.

Dim ctrl As Control
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "ToggleButton" Then
        ctrl.Value = True 'set button state to pressed
    End If
Next ctrl

This sets the state as pressed for every toggle button on the form.

Note that the .Value does not show up in the IntelliSense box because ctrl is of type Control which doesn't have a .Value. If you need IntelliSense then you could workaround like that:

Dim ctrl As Control
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "ToggleButton" Then
        Dim tggl As ToggleButton
        Set tggl = ctrl

        tggl.Value = True
    End If
Next ctrl

// Edit

Everytime a toggle button gets clicked it triggers a _Click event for that button. So you will need such an event for each button.

Private Sub ToggleButton1_Click()
    With Me.ToggleButton1
        If .Value = True Then
            .BackColor = RGB(255, 0, 0)
        Else
            .BackColor = -2147483633 'switch to original color
        End If
    End With
End Sub

Or if you have many buttons, do it more efficiently

'this procedure handles all buttons
Private Sub ToggleButtonClick(ByRef tggl As ToggleButton)
    With tggl
        If .Value = True Then
            .BackColor = RGB(255, 0, 0)
        Else
            .BackColor = -2147483633 'switch to original color
        End If
    End With
End Sub

'so you just need to call that function on every _Click event
Private Sub ToggleButton1_Click()
    ToggleButtonClick Me.ToggleButton1
End Sub

Private Sub ToggleButton2_Click()
    ToggleButtonClick Me.ToggleButton2
End Sub

But you still need a _Click() event for every button to call that procedure.

You can also evaluate the .Value state of each button in the _Click() event to set/unset your asterisk.

Answer 3

I would suggest to implement a class named TglBtn like that

Option Explicit

Private WithEvents m_ToggleButton As MSForms.ToggleButton

Private Sub m_ToggleButton_Click()

    With m_ToggleButton
        If .Value Then
            .BackColor = RGB(255, 255, 0)
        Else
            .BackColor = &H8000000F
        End If
    End With

End Sub

Public Property Set Btn(tb As MSForms.ToggleButton)
    Set m_ToggleButton = tb
End Property

In the Userform you can use the following code

Option Explicit

Dim mTgBtns As Collection

Private Sub UserForm_Initialize()
Dim sngControl As MSForms.Control
Dim mTglBtn As tglBtn
    Set mTgBtns = New Collection

    For Each sngControl In Me.Controls
        If TypeName(sngControl) = "ToggleButton" Then
            Set mTglBtn = New tglBtn
            Set mTglBtn.Btn = sngControl
            mTgBtns.Add mTglBtn
        End If
    Next sngControl

End Sub

When you click on one of togglebuttons on your userform the class will take care of the background color.

EDIT If you want to access the caption of the Togglebutton you could add the following property to the class

Public Property Get Caption() As String
    Caption = m_ToggleButton.Caption
End Property

EDIT2 Just as an example of using the property, you could change the Click event as below and everytime you click a MsgBox with the caption of the button will appear

Private Sub m_ToggleButton_Click()

    With m_ToggleButton
        If .Value Then
            .BackColor = RGB(255, 255, 0)
        Else
            .BackColor = &H8000000F
        End If
    End With

    MsgBox "You pressed the button with the caption " & Me.Caption

End Sub