asked on

Excel VBA - UserForm control click

I have 14 CheckBoxes on a UserForm. 'chk1AM', chk1PM .... to ckh7AM, chk7PM.

I want to avoid having 14 _Click events. Would it be possible to have an 'event' that will be triggered if any of these are clicked? (So then I determine which one was clicked as per the other question I asked here earlier)

Thanks

Martin Liss

That's not possible with VBA, but why not put all those values in one control like a combobox and have them pick from there? Can you attach your workbook?

Martin Liss

Other than that I think the only thing you can do is to create a Sub that does what you want, and add one line of code to each checkbox that calls that sub passing the name of the control.

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hindersaliva

ASKER

Rgonzo, on my workbook I get this error. (however, your example works)

This is what's causing it, but I don't see why. I'm using Excel 2016, and I can see there is a reference to Microsoft Forms 2.0 Object Library.

What could it be? Thanks

Rgonzo1971

Could you send a dummy?

Rgonzo1971

then try

Dim CheckBoxesColl As Collection
Private Sub UserForm_Initialize()
Dim CheckBoxHandler As ClsCheckBoxEvent

Set CheckBoxesColl = New Collection

For Each ctitem In Me.Controls
    If TypeName(ctitem) = "CheckBox" Then
        Set CheckBoxHandler = New ClsCheckBoxEvent
        Set CheckBoxHandler.CheckGroup = ctitem
        CheckBoxesColl.Add CheckBoxHandler
    End If
Next
End Sub

Open in new window

hindersaliva

ASKER

Rgonzo, now I don't get the error but the Checkbox click doesn't produce the message box.

Rgonzo1971

a dummy would be helpful

hindersaliva

ASKER

See attached. Ignore the sheet, go to UserForm.
EE-troubleshoot-controls-click-clas.xlsm

Rgonzo1971

see remark

'Dim CheckBoxesColl As Collection 'should be and is already defined as global
    Dim CheckBoxHandler As clsCheckboxEvent
    
    Set CheckBoxesColl = New Collection
    
    For Each ctitem In Me.Controls
        If TypeName(ctitem) = "CheckBox" Then
            Set CheckBoxHandler = New clsCheckboxEvent
            Set CheckBoxHandler.CheckGroup = ctitem
            CheckBoxesColl.Add CheckBoxHandler
        End If
    Next

Open in new window

Rgonzo1971

your captions are all the same then use name

Option Explicit
 
Public WithEvents CheckGroup As MSForms.CheckBox

Private Sub CheckGroup_Click()
 
    MsgBox CheckGroup.Name
 
End Sub

Open in new window

hindersaliva

ASKER

Thanks Rgonzo. That worked beautifully!