Combobox Error Handling
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
steve wrote:
Dave,
How do I check if it is on the list before I put it in?
"Dave Peterson" wrote:
Why not check to see if the value is on the list before you plop it into the
combobox?
steve wrote:
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?
--
Dave Peterson
--
Dave Peterson
|