Recursive function...

[rivagelp]

Hi all,

My UserForm.Initialize() call SetControlEvents. This function purpose is to find all my ComboBox and TextBox controls and attach to them, a class module events. I use recursive because Frame and MultiPage are container. My Initial call to this function is :

SetControlEvents(Me.Controls)

At runtime, I obtain this error : "Compile error: Argument not optional"
and the ".Controls" is highlighted

When I try with this argument :

SetControlEvents(Me)

I obtain a "Runtime error 13:Type mismatch" and the line where VBA stops is

                  SetControlEvents (Ctrl.Pages(I))

Here is my complete function code :

Private Sub SetControlEvents(AControls As MSForms.Controls)
    Dim Ctrl As MSForms.Control
    Dim I As Integer
    Dim CtrlEvents As clsControlEvents                   ' Définition de notre Class Module dans l'unité "clsControlEvents"

    For Each Ctrl In AControls
      Select Case TypeName(Ctrl)
          Case "Frame"
              SetControlEvents (Ctrl)
          Case "MultiPage"
              For I = 0 To Ctrl.Pages.Count - 1
                  SetControlEvents (Ctrl.Pages(I))
              Next I
          Case "ComboBox"
              ' Reprendre la boucle du UserForm_Initialize()
              If Left(Ctrl.Name, Len(CBXSECTION) - 1) = CBXSECTION Then
                    Ctrl.Tag = ""
                    If Val(Right(Ctrl.Name, Len(Ctrl.Name) - Len(CBXSECTION))) > 1 Then
                        Ctrl.Enabled = False
                    End If
                        
                    Set CtrlEvents = New clsControlEvents     ' Les variables ComboBox et Form sont déclarées
                    Set CtrlEvents.ComboBox = Ctrl            ' dans notre Class Module "clsControlEvents"
                    Set CtrlEvents.Form = Me                  ' La variable Collection cbxSections est déclarée
                    cbxSections.Add CtrlEvents                ' au niveau de cette UserForm (tout en haut de ce module)
              
              End If
          Case "TextBox"
              If Left(Ctrl.Name, 3) = "tbx" Then
                  ' to become...
              End If
      End Select
    Next Ctrl
End Sub

Thank you for any suggestion/help.

Laurent

 

[Andrew Poulsom]

I can't reproduce you error with:

Call SetControlEvents(Me.Controls)

but it may help to see your Class module code. Also what is CBXSECTION?

 

[rivagelp]

hi Andrew, thank you for your fast response.
Yes, I've forgotten the Call statement... Now, it works without compiler errors.

CBXSECTIONS is a constant string equal to "CbxSection". It's my prefix for some ComboBox that I need to add _change() event.

Once again, thank you for your help.

Laurent

The corrected code now is :

Private Sub SetControlEvents(AControls As MSForms.Controls)
    Dim Ctrl As MSForms.Control
    Dim I As Integer
    Dim CtrlEvents As clsControlEvents                   ' Définition de notre Class Module dans l'unité "clsControlEvents"

    For Each Ctrl In AControls
      Select Case TypeName(Ctrl)
          Case "Frame"
              Call SetControlEvents(Ctrl.Controls)
          Case "MultiPage"
              For I = 0 To Ctrl.Pages.Count - 1
                  Call SetControlEvents(Ctrl.Pages(I).Controls)
              Next I
          Case "ComboBox"
              ' Reprendre la boucle du UserForm_Initialize()
              If Left(Ctrl.Name, Len(CBXSECTION) - 1) = CBXSECTION Then
                    Ctrl.Tag = ""
                    If Val(Right(Ctrl.Name, Len(Ctrl.Name) - Len(CBXSECTION))) > 1 Then
                        Ctrl.Enabled = False
                    End If
                        
                    Set CtrlEvents = New clsControlEvents     ' Les variables ComboBox et Form sont déclarées
                    Set CtrlEvents.ComboBox = Ctrl            ' dans notre Class Module "clsControlEvents"
                    Set CtrlEvents.Form = Me                  ' La variable Collection cbxSections est déclarée
                    cbxSections.Add CtrlEvents                ' au niveau de cette UserForm (tout en haut de ce module)
              
              End If
          Case "TextBox"
              If Left(Ctrl.Name, 3) = "tbx" Then
                  ' to become...
              End If
      End Select
    Next Ctrl
End Sub