For Each Combobox

  • Thread starter Thread starter jlclyde
  • Start date Start date
J

jlclyde

Can you use a For each statement in VBA to go through the comboboxes
on a form and tell you how many are filled in? This woudl be easier
then listing them all in the code and doing if statements for each
one.

Thanks,
Jay
 
If you named them nicely and knew how many there were, you could use:

dim cCtr as long
for cctr = 1 to 5 '5 comboboxes
with me.controls("combobox" & cctr)
if .listindex < 0 then
msgbox "combobox" & cctr & " is empty"
end if
end with
next cctr

If you didn't know how many there were or they weren't named nicely:

Dim ctrl as control
for each ctrl in me.controls
if typeof ctrl is msforms.combobox then
if ctrl.listindex < 0 then
msgbox ctrl.name & " is empty"
end if
end if
next ctrl
 
You need to traverse through all of the controls and determine which ar combo
boxes something like this...

Dim ctl As Control
Dim lng As Long

For Each ctl In Me.Controls
If TypeOf ctl Is msforms.ComboBox Then lng = lng + 1
Next ctl
MsgBox lng
 
This is what I ended up with. I needed it to count how many had
things in it. I may not have been as clear as I needed to be. Thanks
for your help and Dave's.
Thanks,
Jay

Dim ctl As Control
Dim lng As Long

For Each ctl In Me.Controls
If TypeOf ctl Is msforms.ComboBox Then
If ctl.Value <> "" Then lng = lng + 1
End If

Next ctl
MsgBox lng
 
Back
Top