Exclude textbox from a control matrix

[kapela2017]

Greetings gentlemen, I found this code, and the truth is that it works very well, it is a class module that creates a matrix for different controls (textboxes, combobox, listbox, etc.) it is really very versatile since you can assign different events For each control, what I'm trying to do is manage to exclude two textboxes (textbox1 and textbox2) from the matrix but I don't know how to do it, if someone can give me an idea I appreciate it, I'll be attentive to any contribution....

'Inside the class module

Private m_PassedControl As MSForms.Control
Private WithEvents chk As MSForms.CheckBox
Private WithEvents cbo As MSForms.ComboBox
Private WithEvents lst As MSForms.ListBox
Private WithEvents opt As MSForms.OptionButton
Private WithEvents spn As MSForms.SpinButton
Private WithEvents txt As MSForms.TextBox

Property Set ctl(PassedControl As MSForms.Control)
Set m_PassedControl = PassedControl

Select Case TypeName(PassedControl)
Case "CheckBox"
    Set chk = PassedControl
Case "ComboBox"
    Set cbo = PassedControl
Case "ListBox"
    Set lst = PassedControl
Case "OptionButton"
    Set opt = PassedControl
Case "SpinButton"
    Set spn = PassedControl
Case "TextBox"
    Set txt = PassedControl
End Select
End Property

Private Sub cbo_Change()
PrintControlName
End Sub

Private Sub chk_Click()
PrintControlName
End Sub

Private Sub lst_Change()
PrintControlName
End Sub

Private Sub opt_Click()
PrintControlName
End Sub

Private Sub spn_Change()
PrintControlName
End Sub

Private Sub txt_Change()
PrintControlName
End Sub

Sub PrintControlName()
Debug.Print m_PassedControl.Name
End Sub



'Inside the userform 
Public collControls As Collection
Private cMultipleControls As clsMultipleControls

Private Sub UserForm_Activate()
Dim ctl As MSForms.Control

Set collControls = New Collection
For Each ctl In Me.Controls
    Set cMultipleControls = New clsMultipleControls
    Set cMultipleControls.ctl = ctl
    collControls.Add cMultipleControls
Next ctl
End Sub

 

[Dan_W]

Greetings gentlemen, I found this code, and the truth is that it works very well, it is a class module that creates a matrix for different controls (textboxes, combobox, listbox, etc.) it is really very versatile since you can assign different events For each control, what I'm trying to do is manage to exclude two textboxes (textbox1 and textbox2) from the matrix but I don't know how to do it, if someone can give me an idea I appreciate it, I'll be attentive to any contribution....

Hi. Thank you for providing the code, and for your explanation above. I suppose there are a few ways of going about it: (1) you could exclude the two controls from being an assigned an event handler, or (2) you can assign the controls the event handler, but then test to see whether or not the triggered control is either TextBox1 or TextBox2.

Assuming the first approach, if would just be a matter of adding a condition to the event handler assignment loop in the userform code. So the userform code would now look like:

'Inside the userform
Public collControls As Collection
Private cMultipleControls As clsMultipleControls

Private Sub UserForm_Activate()
Dim ctl As MSForms.Control

Set collControls = New Collection
For Each ctl In Me.Controls
    If Not ctl.Name = "TextBox1" And Not ctl.Name = "TextBox2" Then
        Set cMultipleControls = New clsMultipleControls
        Set cMultipleControls.ctl = ctl
        collControls.Add cMultipleControls
    End If
Next ctl
End Sub

Here, I have added a 'gatekeeping' IF condition that tests to see if the control name (ctl.Name) is either TextBox1 or TextBox2. Note that the code is case sensitive, meaning that the code in the quotation marks must match the textbox name exactly in order to work. If you would like it to be more flexible, it's easy enough to adjust, just let me know. Hope that makes sense, but do please let me know if you have any problems implementing the above approach.

 

[DanteAmor]

what I'm trying to do is manage to exclude two textboxes (textbox1 and textbox2)

Try this:

'Inside the userform
Public collControls As Collection
Private cMultipleControls As clsMultipleControls

Private Sub UserForm_Activate()
  Dim ctl As MSForms.Control
  
  Set collControls = New Collection
  For Each ctl In Me.Controls
    Select Case ctl.Name
            'textbox to exclude
      Case "TextBox1", "TextBox2", "TextBoxN"
      
      Case Else
        Set cMultipleControls = New clsMultipleControls
        Set cMultipleControls.ctl = ctl
        collControls.Add cMultipleControls
    End Select
  Next ctl
End Sub