• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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
0
hindersaliva
Asked:
hindersaliva
  • 6
  • 4
  • 2
1 Solution
 
Martin LissOlder than dirtCommented:
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?
0
 
Martin LissOlder than dirtCommented:
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.
0
 
Rgonzo1971Commented:
Hi,

It is possible with Class Module
in a class module named clsCheckBoxEvent use this code
Option Explicit
 
Public WithEvents CheckGroup As MSForms.CheckBox

Private Sub CheckGroup_Click()
 
MsgBox CheckGroup.Caption
 
End Sub

Open in new window

then in the userform module
Dim CheckBoxesColl As Collection

Private Sub UserForm_Initialize()
Dim CheckBoxHandler As ClsCheckBoxEvent

Set CheckBoxesColl = New Collection

For Each ctitem In Me.Controls
    If ctitem.DisplayStyle = fmDisplayStyleCheckBox Then
        Set CheckBoxHandler = New ClsCheckBoxEvent
        Set CheckBoxHandler.CheckGroup = ctitem
        CheckBoxesColl.Add CheckBoxHandler
    End If
Next
End Sub

Open in new window

Regards
EE20161113.xlsm
1
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
hindersalivaAuthor Commented:
Rgonzo, on my workbook I get this error. (however, your example works)
Error message
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.
Code where the error occurs
What could it be? Thanks
0
 
Rgonzo1971Commented:
Could you send a dummy?
0
 
Rgonzo1971Commented:
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

0
 
hindersalivaAuthor Commented:
Rgonzo, now I don't get the error but the Checkbox click doesn't produce the message box.
0
 
Rgonzo1971Commented:
a dummy would be helpful
0
 
hindersalivaAuthor Commented:
See attached. Ignore the sheet, go to UserForm.
EE-troubleshoot-controls-click-clas.xlsm
0
 
Rgonzo1971Commented:
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

0
 
Rgonzo1971Commented:
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

0
 
hindersalivaAuthor Commented:
Thanks Rgonzo. That worked beautifully!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now