How to check if combobox.value is empty when looping in userform controls?

[Sabotage]

Hey Wizzards,

I want to loop through of all controls in a user form and count comboboxes based on two criteria. Criteria 1 is to check if the combobox.name contains "cb_Discription*" and up until this point I am able to count them correctly. Criteria 2 would be to check if the combobox is empty or not? So if I find the first combobox named like that and is the combobox is not empty count add 1 to counter. I've tried the following but no luck:

            Dim CTRL As Control
            Dim COUNTER As Interger

            For Each CTRL In Me.Controls
                If CTRL.Name Like "cb_Discription*" And IsNull(CTRL.Value) = False Then    ' also tried CTRL.Text<>"" 
                    COUNTER = COUNTER + 1
                End If
            Next

I realize that when I declare CTRL as control, the Control object does not have a property .value or .text.
Any thoughts? Thanks in advance...

 

[HaHoBe]

Hi,

Type should be Integer, maybe try:

  Dim objControl    As Control
  Dim intCounter    As Integer
  
  For Each objControl In Me.Controls
    If TypeOf objControl Is MSForms.ComboBox Then
      If objControl.Name Like "cb_Discription*" Then
        If objControl.Value <> 0 And objControl.ListIndex > -1 Then intCounter = intCounter + 1
      End If
    End If
  Next objControl
  If intCounter > 0 Then MsgBox "Found: " & intCounter

Ciao,
Holger

 

[Sabotage]

Hey guys, never mind I have found the solution myself, just wrapped it into another if statement checking for the combo box TypeName first. Also changed the second criteria a bit then carry on with the rest. I still don't know why it is so important to determine if the type name is combo box. I someone happen to have an explanation please tell. Much appreciated.

            Dim CTRL As Control
            Dim COUNTER As Interger

            For Each CTRL In Me.Controls
                If TypeName(CTRL) = "ComboBox" Then
                    If CTRL.Name Like "cb_Discription*" And CTRL.Value <> "" Then
                        COUNTER = COUNTER + 1
                    End If
                End If
            Next

 

[Sabotage]

Hi,

Type should be Integer, maybe try:

  Dim objControl    As Control
  Dim intCounter    As Integer
 
  For Each objControl In Me.Controls
    If TypeOf objControl Is MSForms.ComboBox Then
      If objControl.Name Like "cb_Discription*" Then
        If objControl.Value <> 0 And objControl.ListIndex > -1 Then intCounter = intCounter + 1
      End If
    End If
  Next objControl
  If intCounter > 0 Then MsgBox "Found: " & intCounter

Ciao,
Holger

Thank you Holger,
Just saw your answer after I've figured it out. I can see that you also wrapped it into another if statement where your find the TypeName. Without determining that first the code breaks. Although the second if statement clearly says that if the Name is Like....

Hmmmm still not getting the logic, but it does work so thanks again....

 

[HaHoBe]

Hi Sabotage,

TypeName of COUNTER should be Integer, not Interger. My Excel comes up with an error and will not allow any code to execute from there.

  Dim objControl    As Control
  Dim intCounter    As Integer    '!!!not Interger
  
  For Each objControl In Me.Controls
    'check for ComboBoxes only
    If TypeOf objControl Is MSForms.ComboBox Then
      'check the name of the ComboBox
      If objControl.Name Like "cb_Discription*" Then
        'check if any value in the CB is selected
        If objControl.ListIndex > -1 Then intCounter = intCounter + 1
      End If
    End If
  Next objControl
  If intCounter > 0 Then MsgBox "Found: " & intCounter

Any of the checks of the third If will deliver an addition to the counter in my previous code so just checked here if anything has been chosen in the ComboBox.

Ciao,
Holger