Multiple Multi-Select ListBoxes Loop

[richh]

Hey all,

Quick question summmary: My loop won't do anything, even though it works like a charm in other program.

I have two surveys I'm conducting. One works great, the other... not so much. I can't get the loop to work in one of them. Is there a reason as to why having too many listboxes on a form to cause a problem?

I have a loop that is supposed to mark an X in a worksheet depending on which boxes the user's check off. There are four listboxes on the form. Each box has a row source with the corresponding column number written in for instance...


FirstList | Corresponding Column
OptionA | 3
OptionB | 4
OptionC | 5

SecList | CorresondingCol
OptionD | 9
OptionE | 10
OptionF | 11

etc...

The list boxes all have column count 2 and the second column is hidden via width. The following is the loop I use in an almost identical form:

Dim ctl As Control
Dim i As Integer
Dim ws As Worksheet

Set ws = Worksheets("Data")

With ws

' Loop through the list box controls to populate the selected items on the visitor list
For Each ctl In Me.Controls
' Check for listbox controls
Select Case TypeName(ctl)
Case "ListBox"
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) = True Then
' Populate the cell with an x in the appropriate column
intCol = ctl.Column(1, i)

' Populate the cell
.Cells(lRow, intCol).Value = "X"

' Reset the col variable to 0
intCol = 0
End If
Next i
End Select
Next ctl

...

End With

 

[Norie]

How is the code not working?

Is the data not going where you expect it to?

Is there no data at all?

 

[richh]

The other information does, except that which should come from the loop. I have optionButtons and their if statements write fine, the basic .Cells(lRow, 1).Value = Me.blahblah.Value work as well. these loops, however, just aint havin it.

 

[Norie]

What do you mean by 'aint havin it'?

Are you getting errors?

Unexpected results?

Have you tried stepping through the code with F8 to see what's happening?

 

[richh]

I don't get any errors and the rest of the form writes to the worksheet successfully.

When I step through, it gets to here

If ctl.Selected(i) = True Then

and never goes into the next line, it just skips to the End If, cycles through to the end of the list count, cycles through the controls, finds the next listbox, and then does the same.

 

[richh]

I found the issue. I have a loop that is stepped through to verify the users have filled in a response. All required fields are tagged with r. If the control is not filled/selected, the control is highlighted in yellow, if it's filled, the background is white. Making the color white seems to have emptied the listboxes.

For Each ctl In Me.Controls
If ctl.Tag = "r" Then
' This is a required entry
If ctl.Value = "" Then
' Required entry is blank so set the backcolor to yellow
ctl.BackColor = vbYellow
' Capture the name of the cotrol to display to user
strMsg = strMsg & ctl.Name & vbCrLf
Else
' Required entry is populated so set the backcolor to white
ctl.BackColor = vbWhite
End If
End If
Next

' Alert the user if required entries are missing
If Len(strMsg) > 0 Then
MsgBox "Please enter the required data", vbInformation, "Required Entry"
GoTo submit_Click_Exit
End If