Combobox Not in List ? for Excel 1

[ribhead]

I have played around with this and there has to be an easier way to check the entry to the list. If the entry is not in the list then combobox.value=empty Exit Sub

But I'm not sure what the syntax is can someone help me with this? Please...

For just $19.95 you too can have a beautiful smile.

 

[SkipVought]

hi ribhead,

    bHit = False
    For Each c In [MyList]
        If c.Value = ComboBox1.Text Then 
          bHit = True
          exit for
        end if
    Next
    MsgBox bHit

Skip,

http://www.TheOfficeExperts.com

 

[ribhead]

Hey Skip,
THanks for the reply. Do I have to use the Set statement to mylist? And whi is mylist in []? I'm trying to make it so if they enter info that doesn't match in the list it will clear out the entry. Should I be doing this with a command button instead of the Cmbobx itself?

Private Sub ComboBox1_Change()
Set mylist = Range("A1:A4&quot
bhit = False
For Each c In [mylist]
If c.Value = ComboBox1.Text Then
bhit = True
Else
ComboBox1.Value = Empty
ComboBox1.SetFocus
Exit For
End If
Next
MsgBox bhit
Exit Sub

End Sub

For just $19.95 you too can have a beautiful smile.

 

[SkipVought]

Change will fire each time the user changes a value (each keystroke)

You need to evaluate on a button click event.

But I have a problem with your code in the Else. You want to do that stuff AFTER the for...next has had a chance to evaluate based on bHit.

So If Not bHit then remove the entry.



Skip,

http://www.TheOfficeExperts.com

 

[ribhead]

Hey thanx a bunch Skip.

For just $19.95 you too can have a beautiful smile.

 

[DrBowes]

The Combobox has a MatchRequired Property.

Set this to true and it will handle it for you.

You can either set the property in design time using the list of the combobox object's properties, or at run time by using
Combobox1.MatchRequired = True