Using Class Module with Multiple Controls on Userform

[bisel]

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.

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]

I put the workbook in Box. Pretty big, over 6MB.
Here is link: Box


On the Home worksheet please see the following:

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

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

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

 

[Norie]

Steve

Which of the 18 userforms should we be looking at?

 

[bisel]

The one named, contropanel

 

[bisel]

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]

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]

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]

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 ...

code for the worksheet command button ...

Private Sub CommandButton1_Click()
    Application.Run ("Show_USF")
End Sub

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

with two combo boxes and one text box. The close button optional.

Private Sub closebutton_Click()
    Unload Me
End Sub

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

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 ...

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]

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.


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

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

This goes in an userform module

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]

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]

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:

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 :

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.