Combobox Error Handling

[steve]

I have some ComboBoxes on a form. I set MatchRequired = True. If I fill
these boxes with data from a worksheet (through VBA), it will let me use
items that are not found in the required list.
In other words, the required match is not checked until the user enters then
exits the combobox. How do I check that the MatchRequired has been met if
the user is not going to go in, then out, of every combobox?
Example:

the combobox is named ComboBoxColors
the validated list contains these items:
"Blue"
"Green"
"Red"

If I use VBA and say, ComboBoxColors.Value = "Pink" (value from a
worksheet), it will set the value = "Pink".
If I then enter the combobox, then leave it, I will get an error that this
is not a legal entry. How can I trap this error at the time I copy the value
from the worksheet?

 

[stevebriz]

Sorry i am a little confused. I am not clear on whether you want the
user to be able to type into the Combobox ? if not the below will work
If this is not quite what you want let me know.

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex < 0 Then ComboBox1.ListIndex = 0
End Sub

 

[Dave Peterson]

Why not check to see if the value is on the list before you plop it into the
combobox?

 

[steve]

Dave,

How do I check if it is on the list before I put it in?

 

[Dave Peterson]

Is the list on a worksheet somewhere?

dim res as variant
dim someval as variant
dim myRng as range

with worksheets("somesheet")
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

res = application.match(someval, myrng,0)

if iserror(res) then
'not there
else
'was there
end if

=====
If the combobox was already populated (some other way??), then maybe something
like this:

Option Explicit
Private Sub CommandButton1_Click()
Dim res As Variant
Dim myArr() As Variant
Dim iCtr As Long

If Trim(Me.TextBox1.Value) = "" Then
Beep
Exit Sub
End If

ReDim myArr(0 To Me.ComboBox1.ListCount - 1)

For iCtr = LBound(myArr) To UBound(myArr)
myArr(iCtr) = Me.ComboBox1.List(iCtr, 0)
Next iCtr

res = Application.Match(Me.TextBox1.Value, myArr, 0)

If IsError(res) Then
Beep
MsgBox "not valid"
Else
MsgBox "It's a match!"
End If

End Sub