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

A CommandButton usually has no pressed state it does only call a CommandButton1_Click() event and the pressed state immediately releases after you lift your mouse button. Or did you probably mean a ToggleButton instead (which is a completely different control)? What is your overall goal? What are you trying to do? — Pᴇʜ
– Pᴇʜ, Commented Mar 1, 2018 at 13:02
I have an excel spreadsheet. there are lots of rows which have a requirement, for a system. I want to assign a requirement, based on the wording to a particular user, or set of users. We have come up with a form, which has about 30 buttons (of usernames). The idea is that someone will read a requirement, and then assign it to a one or more users. this will work buy the user pressing the buttons of the users who will then be assigned the requirement. The backend code simply puts an asterisk against the username for each requirement. each user has excel column. the * goes against each user — Ammo
– Ammo, Commented Mar 1, 2018 at 13:09
So if the button should represent a state you would need to use the ToggleButton instead of a CommandButton. Then you can access its state with ToggleButton1.Value which is true if it is pressed and false if not. — Pᴇʜ
– Pᴇʜ, Commented Mar 1, 2018 at 13:18
Ok. I have three toggle buttons now. I am using same construct : ` For Each ctrl in UserForm1.Controls` However, there is no Value option for ctrl. i.e there is no ctrl.Value — Ammo
– Ammo, Commented Mar 1, 2018 at 13:26

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 1, 2018 at 13:26

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ammo Over a year ago

yes. that works. However, if i want to set the background colour of the togglebox when the user presses it. is that possible? i.e if (ctrl.Value = True) then ctrl.Backcolor = RGB (255,0,0)

Ammo Over a year ago

by the way, i would also want to know which button was pressed, so that i can place the asterisk in the appropriate column in the Excel sheet for each row of data

Zac Over a year ago

@Ammo, that seems like a better approach to me. Have a single function that accepts a ToggleButton control and just set the colour in that function. Then, on Click event of your ToggleButton's, just call that function and pass the button into the function. That way if you ever wanted to change the colour or access some other state of the button, you can just do it in one function and it will filter through to all buttons. For your second query, from that function, you can get the name of the button and update appropriate cell in your sheet

Ammo Over a year ago

Thank you for your help.

Deb Over a year ago

Now I have found that I need to declare tggl as 'Dim tggl as MsForms.ToggleButton' and in comparison statement should be 'If TypeOf ctrl is MsForms.ToggleButton then'. It works.

|

schlebe · Accepted Answer · 2018-03-01 14:48:03Z

0

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.

answered Mar 1, 2018 at 14:48

schlebe

3,8395 gold badges44 silver badges60 bronze badges

2 Comments

Ammo Over a year ago

Hi. Sorry, this didn't work for me :( ( Did I mention i am a beginner in VBA?!) the construct Private Sub pbButton(1)_Click() doesnt work for me as the VBA complains about it. Also when trying to run the rest of the code, it fails as i dont think it knows the value of pbButton1 being passed in the Call ChangeButtonsColour. This equates to False during runtime and the Subroutine is expecting pb as Button?

schlebe Over a year ago

Ok, pbButton(1) is incorrect. it is only a syntax to explain that if you have a lot of button, pbButton(X) must be replaced by pbButton1 as written is the second part of my post. Normally, you make that without typing the name of the Button. Excel VBA make that for you. If I remember well, you must rightclick on the Button and request to see View Code. After clicking on it, VBA editor is there with to Combox at the right top of your window. The first display the name of the control (your button) and the second contains all the possible events that you can use. You can select Click() event.

Storax · Accepted Answer · 2018-03-02 10:42:20Z

0

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

edited Mar 2, 2018 at 10:42

answered Mar 1, 2018 at 15:24

Storax

12.7k3 gold badges21 silver badges36 bronze badges

7 Comments

Ammo Over a year ago

Dim mTglBtn As tglBtn throws compile error: User-defined type not defined.

Storax Over a year ago

You have to create a class module, rename it to TglBtn and you have to paste the code from above into it. Just look here how to do that excelmacromastery.com/vba-class-modules/…

Ammo Over a year ago

for some reason vba wont let me rename the class, so i changed your code to Class1 where you hhave tglBtn. The Code is working! Thanks If you remember my original issue, each button press should correspond to placing an asterisk against each column (in this case these are peoples names). is there a way to find out which button, is pressed each time and then map it to a column in Excel. Would that be from the use of the TabIndex?

Storax Over a year ago

Yes, of course, you can find out which button was pressed. You have to modify the class resp. add a property.

Ammo Over a year ago

can you please show an example of this. I tried following your example of get_caption, but the list of items available from intellisense didn't list tabindex etc as an option.

|

Collectives™ on Stack Overflow

VBA loop through buttons on UserForm

3 Answers 3

6 Comments

2 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related