Using Class Module with Multiple Controls on Userform

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
Hello All,

I have an 11 tab multipage userform in my workbook. Each page has either several text boxes, combo boxes or a combination of both.

I want to use the combo boxes as an example.

Each combo box is tied to a control source so that when the user selects a value, the cell in the workbook gets that value. So far so good.

After the combo box is clicked, the exact same VBA code is run for each ... here is an example using the combo box called, "dues1_combox". There are a few dozen combo boxes all with unique names.

VBA Code:
Private Sub dues1_combobox_Click()
    Me.close_button.SetFocus 'Change focus to the userform Close Button
    Call update_controlpanel ' Refresh the control panel 
End Sub

As the code to be executed after clicking is the same for each of the several combo boxes, rather that write a click event for each combo box, I would like to use the class module and then whenever any combo box that is a member of that class is clicked, run the same VBA.

I have been reading the forums on how one can do this, but frankly am getting confused.

As I understand it, the steps are:
  1. Create class module
  2. Add objects (in this case the combo boxes) to the class
  3. Create Sub for the class with the code above
Do I have this correct? How do I add objects to the class? Is there anything I am missing?

Thanks for your help.

Steve
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
With these steps you are able to achieve what you want. For demo open a new created workbook. Be sure to have a UserForm1 with at least two combo boxes and a TextBox1.
Insert a standard module and paste the following code:
VBA Code:
Public Sub Show_USF()
    Dim CB_Group()  As New Cls_Combo
    Dim ComboCount  As Integer
    Dim ctl         As Control

    ComboCount = 0
    For Each ctl In UserForm1.Controls
        If TypeName(ctl) = "ComboBox" Then
            ComboCount = ComboCount + 1
            ReDim Preserve CB_Group(1 To ComboCount)
            Set CB_Group(ComboCount).ComboBoxGroup = ctl
        End If
    Next ctl
    UserForm1.Show
End Sub

Insert a class module with the name Cls_Combo and paste the following code:
VBA Code:
Option Explicit

Public WithEvents ComboBoxGroup    As MSForms.ComboBox

Private Sub ComboBoxGroup_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    UserForm1.TextBox1.Value = ComboBoxGroup.Name
End Sub

Run Show_USF and move your mouse over the combo boxes and watch the text box. In this way every event of a combo box can be captured without the need of a few dozen of event procedures.
 
Last edited:

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
Thanks for your reply, GWteB,

I am trying your suggestion and I get an error when I attempt to open the userform ... "Compile error: Expected array"

here is the code that I created ...

Inserted Class Module with name of combobox_cls

VBA Code:
Option Explicit

Public WithEvents ComboBoxGroup As MSForms.ComboBox

Private Sub ComboBoxGroup_Click()
    controlpanel.close_button.SetFocus 'Change focus to the userform Close Button
    Application.Run ("update_controlpanel") ' Refresh the control panel
End Sub


Inserted this code as part of the userform code for the Initialize event

VBA Code:
Private Sub UserForm_Initialize()
Dim ctl As Control
Dim ComboBoxGroup As New combobox_cls

Dim combocount As Integer

combocount = 0

For Each ctl In controlpanel.Controls
    If TypeName(ctl) = "ComboBox" Then
            combocount = combocount + 1
            ReDim Preserve ComboBoxGroup(1 To combocount)
            Set ComboBoxGroup(combocount).ComboBoxGroup = ctl
    End If
Next ctl

End Sub

When I go to launch the userform, I get the error that is was expecting an array and it highlights the line

VBA Code:
            ReDim Preserve ComboBoxGroup(1 To combocount)

Can you see what I am doing incorrectly?

Thanks,

Steve
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
I figured out what was wrong to avoid the expected array error. I did not include the parenthesis.

I used this ... Dim ComboBoxGroup As New combobox_cls

and should have used this ... Dim ComboBoxGroup() As New combobox_cls

At least the userform loads now, but clicking the combox does not do what I expected. Need to work on this a bit more.

Thanks,

Steve
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153

ADVERTISEMENT

I am still needing some help, please. I inserted this code ...

Class Module = combobox_cls
VBA Code:
Option Explicit

Public WithEvents ComboBoxGroup As MSForms.ComboBox

Private Sub ComboBoxGroup_Click()
    controlpanel.close_button.SetFocus 'Change focus to the userform Close Button
    Application.Run ("update_controlpanel") ' Refresh the control panel
End Sub

In the code for the userform ...
VBA Code:
Private Sub UserForm_Initialize()
    Dim ctl As Control
    Dim CB_Group() As New combobox_cls
    Dim combocount As Integer

    combocount = 0
   
    For Each ctl In controlpanel.Controls
        If TypeName(ctl) = "ComboBox" Then
                combocount = combocount + 1
                ReDim Preserve CB_Group(1 To combocount)
                Set CB_Group(combocount).ComboBoxGroup = ctl
        End If
    Next ctl
End Sub

When I launch the userform, it loads OK. But when I click a combo box, it is as if there is no event code associated with that combo box. It does not execute the VBA in the class module.

Any thoughts?

Thanks,

Steve
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
CB_Group() should be declared at the module level. So you should move the following declaration so that it appears at the top of the userform module before any and all procedures...

VBA Code:
Dim CB_Group() As New combobox_cls
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153

ADVERTISEMENT

Hello Domenic,

I tried that. Here is the code that is part of the userform module:

VBA Code:
' This Dim statement at top of the userform module
Dim CB_Group() As New combobox_cls

Private Sub UserForm_Initialize()
    Dim ctl As Control
    
    Dim combocount As Integer
    
    combocount = 0

    For Each ctl In controlpanel.Controls
        If TypeName(ctl) = "ComboBox" Then
                combocount = combocount + 1
                ReDim Preserve CB_Group(1 To combocount)
                Set CB_Group(combocount).ComboBoxGroup = ctl
        End If
    Next ctl
    
End Sub

And here is the code for the class module:

VBA Code:
Option Explicit

Public WithEvents ComboBoxGroup As MSForms.ComboBox

Private Sub CB_Group()
    controlpanel.close_button.SetFocus 'Change focus to the userform Close Button
    Application.Run ("update_controlpanel") ' Refresh the control panel
End Sub

I have tried placing the code in public module and that made no difference. Also placed in the userform activate event and no difference there either.

I think I am close to making this work, but no cigar yet.

Thanks,

Steve
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Steve

In the class module the sub should be an event of ComboBoxGroup, in this case tthe Click event.
VBA Code:
Option Explicit

Public WithEvents ComboBoxGroup As MSForms.ComboBox

Private Sub ComboBoxGroup_Click()
    controlpanel.close_button.SetFocus 'Change focus to the userform Close Button
    Application.Run ("update_controlpanel") ' Refresh the control panel
End Sub
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
Steve

In the class module the sub should be an event of ComboBoxGroup, in this case tthe Click event.
VBA Code:
Option Explicit

Public WithEvents ComboBoxGroup As MSForms.ComboBox

Private Sub ComboBoxGroup_Click()
    controlpanel.close_button.SetFocus 'Change focus to the userform Close Button
    Application.Run ("update_controlpanel") ' Refresh the control panel
End Sub

Hi Norie,

I did catch that, thanks. Still no joy.

I created a blank workbook and created an userform, class module and public module as GWteB suggested. Substituted a click even for the the even he was showing. Worked exactly as I was looking for. But, when I added that code into my workbook with the 11-tab userform, no joy. Nothing. Nada. When I click on the combo box, no event gets triggered.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Any chance you could upload the workbook to a file sharing site and post a link to it?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,015
Messages
5,599,351
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top