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
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
I put the workbook in Box. Pretty big, over 6MB.
Here is link: Box


On the Home worksheet please see the following:

1591415554694.png

I put code to open a user form called, "UserForm1" ...

Also on the home page you will see this ...

1591415659475.png

Click the Control Panel to open the Control Panel userform with 11 tabs

Some details:
  • Two class modules.
    - Cls_combo applies to the "controlpanel" userform
    - Cls_combo2 applies to the test userform called "UserForm1"

  • Two macros. Both in module called, "CP_subs".
    - Public Sub control_panel --- used to launch the controlpanel
    - Public Sub Show_USF --- used to launch UserForm1

Launching UserForm1 works as intended. Launching controlpanel does not work. Essentially the same code for both with same class ... just different name.

I do appreciate any comments.

Thanks

Steve
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Norie

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

Which of the 18 userforms should we be looking at?
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
Just on a hunch decided to try something.

Seems that this technique of using class objects to group like controls on an userform does not like it if the userform's ShowModal = False. I changed the property of the userform called "controlpanel" to ShowModal = True and the technique works fine.

This userform in my workbook needs to be ShowModal = False. Is there a way to make this work?

Steve
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153

ADVERTISEMENT

After some more experimenting, I have resigned myself that using a class module to define an event for a group of user form controls is not going to work for me as the use of the class module seems to require that the user form runs in Modal form. Will not work with non-modal user form.

I have created individual subs for each control that will kick off when the event for that control is triggered.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Sorry, I've not had a chance to have a closer look at the workbook you uploaded - lot of userforms and code.:)

I've never heard of an issue where the modality of the form prevented the functionality of a class module but I'll take a look into it.

As for creating separate subs for each combo, are you sure you need to do that?

You could have a single sub to handle things and that could be called from the click event of each combobox, not ideal but might be worth a shot.
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153

ADVERTISEMENT

Hello Norie,

To your point ... I do have a single sub to handle the actions for each combo box, that makes it easier as I can just copy the code for each control sub event. It took a bit of time, but working ok now.

You can test this out for yourself.

Open a blank workbook in Excel
Create a Worksheet ...
1591482798662.png


code for the worksheet command button ...
VBA Code:
Private Sub CommandButton1_Click()
    Application.Run ("Show_USF")
End Sub


Create an userform like this ... name it, "UserForm1"

1591481487751.png


with two combo boxes and one text box. The close button optional.
VBA Code:
Private Sub closebutton_Click()
    Unload Me
End Sub


Create a Class Module. Name it "Cls_Combo". Insert this code into the class module ...

VBA Code:
Option Explicit

Public WithEvents ComboBoxGroup    As MSForms.ComboBox

Private Sub ComboBoxGroup_Click()
    UserForm1.TextBox1.Value = ComboBoxGroup.Value
    UserForm1.closebutton.SetFocus 'Change focus to the userform Close Button
End Sub


Insert a public module.
Create a Public Sub ...
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


Set the Combo Box control sources to "target1" for ComboBox1 and "target2" for ComboBox2
Set the Row Source for both combo boxes to "comboboxtext"

Launch the user form. Should work using the class object.

Change the user form property from Show Modal = True to Show Modal = False

Launch the user form again and observe that the class module code for the click event no longer works.

Regards,

Steve
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Hi Bisel,
I was unable to respond sooner. My post #2 code is just an example (one of many .....). I used the MouseMove event because the Click event is only triggered with a populated combo box. In this example the separate class instances for each combo box are declared in the same procedure as in which the modal userform is launched, so the class instances stay in scope.
As you've already discovered, code has to be amended to work within a userform itself and to keep the class instances in scope. Long story short, the way in which a userform is launched (modeless or modal) and the module in which the class instances are declared ultimately determine how the code in total should look like to get it working.
In the example below the class captures the events of three different types of controls, on a modeless userform as well. I've added the "Usf" property to the class, so the class can be used in all userforms within your workbook if you like, as shown on the attached image.
ScreenShot106.png

Regarding the class code, note the "Group" suffix in both the event declarations and the event procedures. Note also the "Usf" property, to be used to reference the userform which is using the class and regarding the latter, the (private) mUSF variable, which indicates in each event procedure to which userform the relevant instance of the class relates.

This goes in a class module, to be renamed: Cls_CtlEvents
VBA Code:
Private mUSF    As Object

Public WithEvents ComboBoxGroup     As MSForms.ComboBox
Public WithEvents TextBoxGroup      As MSForms.TextBox
Public WithEvents LabelGroup        As MSForms.Label

Public Property Set Usf(ByRef argUsf As Object)
    Set mUSF = argUsf
End Property

Public Property Get Usf() As Object
    Set Usf = mUSF
End Property

Private Sub ComboBoxGroup_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    mUSF.Label1.Caption = mUSF.Name & " > " & ComboBoxGroup.Name
End Sub

Private Sub LabelGroup_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    mUSF.Label1.Caption = mUSF.Name & " > " & LabelGroup.Name
End Sub

Private Sub TextBoxGroup_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    mUSF.Label1.Caption = mUSF.Name & " > " & TextBoxGroup.Name
End Sub


ScreenShot106.png
This goes in an userform module
VBA Code:
Private ControlGroup()  As New Cls_CtlEvents

Private Sub UserForm_Initialize()

    Dim CtlCount    As Integer
    Dim ctl         As Control

    ReDim ControlGroup(Me.Controls.Count - 1)
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "ComboBox" Then
            Set ControlGroup(CtlCount).ComboBoxGroup = ctl
            Set ControlGroup(CtlCount).Usf = Me
            CtlCount = CtlCount + 1
        End If
        If TypeName(ctl) = "TextBox" Then
            Set ControlGroup(CtlCount).TextBoxGroup = ctl
            Set ControlGroup(CtlCount).Usf = Me
            CtlCount = CtlCount + 1
        End If
        If TypeName(ctl) = "Label" Then
            Set ControlGroup(CtlCount).LabelGroup = ctl
            Set ControlGroup(CtlCount).Usf = Me
            CtlCount = CtlCount + 1
        End If
    Next ctl
End Sub
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
Hello GWteB.

Thanks for your reply. I tried your example and was having problems making it work. But, not to worry.

At this point, I have created the subs in the userform module that are tied to the specific events associated with each control on the userform. It is working the way I want. Yes, it is not as efficient as I would like, but it is working and I believe I should leave it as it is.

Thanks,

Steve
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Hi Steve,
I think there should be a solution to your problem. From what I understand from your first post and the follow-up on this thread, a separate procedure is invoked which I suspect also changes properties of other controls on the same userform. If that's the case, the solution is relatively simple. Convert your individual procedure in such a way that it accepts arguments, such as the same way many Excel Event procedures do (the ByRef argUsf As Object part on the first line), for example:
VBA Code:
Public Sub Update_ControlPanel(ByRef argUsf As Object)
    With argUsf
        .CommandButton1.SetFocus
        .ComboBox3.Clear
        .ComboBox9.AddItem "Some new item"
        .ComboBox6.RowSource = myCB6_Range
        .LabelMessage.Caption = "Control Panel has been updated..."
        ' whatever you want
    End With
End Sub


Within the VBE you can use Find & Replace (Ctrl-F) to find all references to your userform in that separate procedure and replace it with the name of the argument passed from the class module by the ComboBoxGroup_Click event procedure (the argUsf:=mUSF part within the call to the separate sub), for example :
VBA Code:
Private Sub ComboBoxGroup_Click()
    'Change focus to the userform Close Button
    mUSF.close_button.SetFocus
    ' Refresh the control panel
    Call Update_ControlPanel(argUsf:=mUSF)
End Sub

If you get stuck, let me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
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