Solved: Need to verify selection made in multi-select list box

[clhare]

I use the code below to verify that there are no empty controls on a userform before the macros continue. It always works great, but this time I have a single-select listbox and a multi-select list box. The Validate macro is able to tell when I've made a selection in the single-select listbox, but it isn't recognizing when I make a selection in the multi-select list box (regardless of whether I select one or more than one item).

Why isn't the macro recognizing a selection in the multi-select listbox? How do I adjust the code so that it will recoginze the selection?

Sub Validate()
' For each control on the userform
For Each ctl In Me.Controls
' If the control is a textbox or a combobox, continue
If TypeOf ctl Is msforms.TextBox Or TypeOf ctl Is msforms.ComboBox Or _
TypeOf ctl Is msforms.ListBox Then
If ctl.Enabled = True Then
' If the control is empty, continue
If Me.Controls(ctl.Name).Text = "" Then
' Change colors to notify user
With Me.Controls(ctl.Name)
.BackColor = RGB(255, 0, 0)
.ForeColor = RGB(255, 255, 255)
End With
' Keep track of number of empty controls
intEmptyControls = intEmptyControls + 1
' If the control is not empty, continue
Else
' Make sure colors are normal
With Me.Controls(ctl.Name)
.BackColor = RGB(255, 255, 255)
.ForeColor = RGB(0, 0, 0)
End With
End If
End If
End If
' Check next control on the userform
Next
End Sub

Thanks!

[Frosty]

I love commenting on this board, because even though I've got 15 years of programming experience, I still get to learn new things every day. I've never seen TypeOf in an If statement before.

I was going to suggest setting up a Select Case statement-- but you can't use the TypeOf in the Case statement. So I got to learn something new, thank you!

However, the answer to your question is fairly straightforward: your test is too simple. You can't use the .Text property for all types of controls.

For a ListBox which is multiselect, you need to check the .Selected property (which is a 0 based array of all the options in the list box). This also works for a ListBox which is single select.

*HOWEVER* (and this is the biggie for your purposes) it appears (at least in Word 2010), that your methodology of changing the background colors affects the .Selected property of the multiselect listbox in a way which doesn't affect the single select listbox .Selected property (probably because the single select has a bunch of other places to store it's value (.Value, .Text, .BoundValue, etc). Changing the background color actually makes the multi-select listbox BE unselected, whereas it only makes the single select listbox to APPEAR to be unselected.

Couple of additional things I would comment on here...

1. Option Explicit. You really should use it. It leads to all sorts of good things, like use of the Locals Window. It also leads you to using more variables, getting comfortable, and ultimately becoming a bit of a stickler for "correct" variable types. Ultimately, the only way you were going to find the .Selected property here, would have been to have a MSForms.ListBox variable or be really familiar with the Object Browser

2. Selecting items in a listbox actually changes foreground/background colors. So changing everything to red or everything back to white (whether or single select/multi-select) for the entire control makes it appear as if there is nothing selected (even though there is). I would think this would cause issues for your users. In general, the better way to approach this is the same way Microsoft deals with forms (this is a good guide anyway)... disable the control allowing you to proceed, if the form is not filled out correctly. That may be the OK button, or something else. If you're really attached to the visual cue (and jumping to the first "incomplete" control isn't an option), it may be better to utilize linked labels and have those be your red flags.

The following code is a bit of a mock up for you, but just demonstrating the slight re-adjustment without dealing with my primary issue in your methodology (#2) (which you will need to address if you are going to use multi-select listboxes).

In general, I think the best approach would be to have your controls follow a naming convention (with labels of a listbox called lblListBox1 and the list box called lstListBox1 -- to allow your code to extrapolate associations based on your naming convention). That will allow you to skip the TypeOf stuff and still have visual cues without adversely affecting the controls.

Hope this helps.

- Jason

Sub Validate()
Dim ctl As MSForms.Control
Dim intEmptyControls As Integer
Dim oListBox As MSForms.ListBox
Dim i As Integer
Dim bSelectedSomething As Boolean

' For each control on the userform
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.TextBox Or TypeOf ctl Is MSForms.ComboBox Then
'can't this be outside of the If TypeOf block?
If ctl.Enabled = True Then
'format the control appropriately, adding to our list of empty controls
intEmptyControls = intEmptyControls + fValidateFormat(ctl, ctl.text <> "")
End If
'have to do something special for list boxes
ElseIf TypeOf ctl Is MSForms.ListBox Then
If ctl.Enabled = True Then
'assume the worst!
bSelectedSomething = False
'so we can use autocomplete more easily while programming
Set oListBox = ctl
'this is our test for listbox types (works for both multi-select and single select)
'note: multi-select listboxes will not error if you give an index on selected which
' doesn't exist-- wierd
For i = 0 To oListBox.ListCount - 1
If oListBox.Selected(i) = True Then
bSelectedSomething = True
End If
Next
'format the control appropriately
intEmptyControls = intEmptyControls + fValidateFormat(ctl, bSelectedSomething)
End If
End If
' Check next control on the userform
Next
End Sub
'break apart the actual validation formatting process
'return 0 if it was a valid function, 1 if i wasn't (allows us to iterate up)
'may want to use TypeOf or some other test here, as changing background/forground on
'list boxes will be confusing to the end-user (valid controls end up looking unselected)
Private Function fValidateFormat(ctl As MSForms.Control, bValid As Boolean) As Integer
With ctl
If bValid Then
' Make sure colors are normal
.BackColor = RGB(255, 255, 255)
.ForeColor = RGB(0, 0, 0)
'don't add to our invalid control list count
fValidateFormat = 0
Else
' Change colors to notify user
.BackColor = RGB(255, 0, 0)
.ForeColor = RGB(255, 255, 255)
'add to our invalid control list count
fValidateFormat = 1
End If
End With
End Function

[gmaxey]

Jason,

Instead of TypeOf, I suggests using TypeName. That you can use in a Select Case statement:

Sub Validate()
Dim oCtr As MSForms.Control
Dim lngCount As Long
Dim i As Integer
Dim bSelectedSomething As Boolean
'For each control on the userform
For Each oCtr In Me.Controls
Select Case TypeName(oCtr)
Case "TextBox", "ComboBox"
If oCtr.Enabled = True Then
'Format the control appropriately, adding to our list of empty controls.
lngCount = lngCount + fValidateFormat(oCtr, oCtr.Text <> "", 1)
End If
Case "ListBox"
If oCtr.Enabled = True Then
'Assume the worst!
bSelectedSomething = False
'This is our test for listbox types (works for both multi-select and single select)
For i = 0 To Me.Controls(oCtr.Name).ListCount - 1
If Me.Controls(oCtr.Name).Selected(i) = True Then
bSelectedSomething = True
End If
Next
'Format the control appropriately
lngCount = lngCount + fValidateFormat(oCtr, bSelectedSomething, 2)
End If
' Check next control on the userform
Case Else
'Do nothing.
End Select
Next oCtr
lbl_Exit:
Exit Sub
End Sub
Private Function fValidateFormat(oCtr As MSForms.Control, bValid As Boolean, lngRouter As Long) As Long
Dim strLabel As String
With oCtr
If bValid Then
If lngRouter = 1 Then
'Set normal color.
.BackColor = RGB(255, 255, 255)
.ForeColor = RGB(0, 0, 0)
Else
strLabel = "lbl" & oCtr.Name
Me.Controls(strLabel).BackColor = &H8000000F
End If
'Don't add to our invalid control list count.
fValidateFormat = 0
Else
If lngRouter = 1 Then
'Set alert color.
.BackColor = RGB(255, 0, 0)
.ForeColor = RGB(255, 255, 255)
Else
strLabel = "lbl" & oCtr.Name
Me.Controls(strLabel).BackColor = RGB(255, 0, 0)
End If
'Add to our invalid control list count.
fValidateFormat = 1
End If
End With
lbl_Exit:
Exit Function
End Function

Addressing your point about listbox back and forecolor, if the OP really wants a visual flag then he could associate a label with the listbox. For the example above I just added a label (to mimic a change bar) down the side of the listbox. If an item isn't select the label shows red.

[clhare]

This is AWESOME and works great! I have to say, I absolutely love this forum! I have learned so much from you all!

Thank you both so much!