ComboBox in userform

ogo

Board Regular
Joined
Mar 15, 2011
Messages
105
Hi all,
I have a userform with a number of comboboxes of which i have kept some as hidden.
Now i have to check whether the visible comboboxes are empty or not;
When i tried the below code, it was checking the hidden comboboxes also,
Code:
Sub test()
    Dim ctl As Control
 
    For Each ctl In frmRC.Controls
        If TypeName(ctl) = "ComboBox" Then
            If ctl.Value = vbNullString And ctl.Visible Then
 
                MsgBox "Combobox is empty" & ctl.Name
 
            End If
        End If
     Next ctl
End Sub
Is there a way to find the empty visible comboboxes through vba code??
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
You could just check the Visible property of the comboboxes.

Just out of curiosity, why do you have hidden/empty comboboxes?
 
Upvote 0
You could just check the Visible property of the comboboxes.

Just out of curiosity, why do you have hidden/empty comboboxes?

Thanks for replying Norie,

I hide/show some comboboxes depending on the value in first combobox.
I have already tried visible property. Can you please give an example for that. I want to check if there is anything wrong in what i did..
 
Upvote 0
What did you try exactly?

Are you actually using the Visible property?

By the way, what do you mean by 'empty'?

Do you just want to find out if something has been selected?
 
Upvote 0
I used something like
Code:
Dim ctl As Control
For Each ctl In frmRC.Controls
      If ctl.Value = vbNullString And ctl.Visible Then
         msgbox ctl.Name
      End If
Next ctl

I expected the code to display the names of Empty and Visible comboboxes in the form , but it actually gave all the combobox names in userform, even though they were hidden..

By empty i mean even if there are items , none are selected..
 
Upvote 0
There's no Visible in IntelliSense, but in properties of designer this property is shown.
Code:
Sub TestCombo()
    
    Dim ctl As MSForms.Control
    Dim combo As MSForms.ComboBox
 
    For Each ctl In frmRC.Controls
        If TypeOf ctl Is MSForms.ComboBox Then
            Set combo = ctl
            If combo.Visible And combo.ListCount = 0 Then
                MsgBox "Combobox is empty: " & combo.Name
            End If
        End If
     Next
     
End Sub
 
Last edited:
Upvote 0
Oops, never saw that you were using Visible in that code.

As far as I can see that code should work, though it would be for all the controls on the form not just the combos.

How are you making them visible/hidden?
 
Upvote 0
Oops, never saw that you were using Visible in that code.

As far as I can see that code should work, though it would be for all the controls on the form not just the combos.

How are you making them visible/hidden?

Code:
    ComboBox1.Visible = True
    ComboBox2.Visible = True
    ComboBox3.Visible = True

Code:
    ComboBox1.Visible = False
    ComboBox2.Visible = False
    ComboBox3.Visible = False
 
Upvote 0
Where/when are you running the code you posted to test the comboboxes and the code to change their visibility?
 
Upvote 0
Hi all,
The problem is solved,
Code:
'Check if the comboboxes are empty
For Each ctl In frmRC.Controls
    If TypeName(ctl) = "Frame" And ctl.Visible Then
    
        For Each ctl2 In ctl.Controls
            If TypeName(ctl2) = "ComboBox"  Then
                If ctl2.Value = vbNullString And ctl2.Visible Then
                    MsgBox "The field can't be empty", vbExclamation + vbOKOnly, "Validation Failed"
                    ctl2.SetFocus
                    Exit Function
                End If
            End If
        Next ctl2
        
    End If
Next ctl
Thank u all for your help..:)
 
Upvote 0

Forum statistics

Threads
1,224,545
Messages
6,179,432
Members
452,915
Latest member
hannnahheileen

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
Back
Top