checking for errors in combobox's

  • Thread starter Thread starter Bob C
  • Start date Start date
B

Bob C

I'll try to keep this short. I have a form with a number of combobox's
(let's say 20). Each combobox represents a slot in a shelf. Each combobox
points to the same list of cards. Certain slots can accept any card,
whereas certain slots can only accept a few cards. I do not want to allow
the run button to be selected until all slots contain the correct cards. I
can accomplish this with the following code using many if statements for
each combobox.

Let's say CBox1 (slot-1), can contain any card except "card1 or card2)

Private Sub CmdRun_Click()

If (frmMaintest.CBox1.Text = "card1") Or (frmMaintest.CBox1.Text =
"card2") Then
MsgBox "You must select another name!"
Exit Sub
ElseIf (frmMaintest.CBox1.Text <> "card1") Eqv (frmMaintest.CBox1.Text
<> "card2") Then
MsgBox "This name is acceptable!"
End If

Range("d4").Select
Selection.Value = frmMaintest.CBox1.Text

Unload frmMaintest

End Sub

I have also accomplished this using
Private Sub CBox1_Change() 'instead of the CmdRun_Click()

QUESTIONS
1) Can the above "If" statements be shortened?

2) What I really want is for the combobox to "drop-down" and make you
select the correct card before letting you select the next combobox.

Any suggestions will be greatly appreciated!
 
Bob

Here's one way to do it. For each combobox on your form, put the cards that
are allowed in the Tag property (design time is probably best). Then, when
the form loads (or whatever time you determine where the list of cards is),
only add cards to the combobox that are in the tag. That way, the user
can't make a mistake. Here's an example

Sheet1.Range("a1:a3") contains

Card1
Card2
Card3

Userform1 has three comboboxes with Tag properties of

Card1;Card2
Card2;Card3
All

respectively. In the initialize event of the form, I populate the
comboboxes based on their Tags

Private Sub UserForm_Initialize()

Dim CardRng As Range
Dim Cell As Range
Dim Ctl As Control
Dim AllowedCards As Variant
Dim i As Long

Set CardRng = Sheet1.Range("a1:A3")

For Each Ctl In Me.Controls
If TypeName(Ctl) = "ComboBox" Then
If Len(Ctl.Tag) > 0 Then
If Ctl.Tag = "All" Then
For Each Cell In CardRng.Cells
Ctl.AddItem Cell.Value
Next Cell
Else
AllowedCards = Split(Ctl.Tag, ";")
For Each Cell In CardRng.Cells
For i = LBound(AllowedCards) To _
UBound(AllowedCards)

If Cell.Value = AllowedCards(i) Then
Ctl.AddItem Cell.Value
End If
Next i
Next Cell
End If
End If
End If
Next Ctl

End Sub
 
Back
Top