Whats wrong with my code?

[Shaka215]

Private Sub ComboBox1_Change()
If Sheets("SYS").Range("A1:A18").Find(ComboBox1.Value) = False Then
MsgBox "You have entered a invalid choice. Please make a valid
selection to continue.", vbCritical, "- INVALID SELECTION ERROR -"
ComboBox1.Value = ""
Exit Sub
End If
End Sub

The idea is to make it so if someone types in a drop down box and its
not one of the values they could select from...the application will
popup with an error message. My logic makes perfect sense to me but
VBA is having a hard time accepting it...any ideas as to why? Thanks
alot!

 

[Corey]

Can you not POPULATE only the values in the Combobox from the range, thus eliminating the
possibility of the user entering an invalid entry?

Private Sub ComboBox1_Change()
If Sheets("SYS").Range("A1:A18").Find(ComboBox1.Value) = False Then
MsgBox "You have entered a invalid choice. Please make a valid
selection to continue.", vbCritical, "- INVALID SELECTION ERROR -"
ComboBox1.Value = ""
Exit Sub
End If
End Sub

The idea is to make it so if someone types in a drop down box and its
not one of the values they could select from...the application will
popup with an error message. My logic makes perfect sense to me but
VBA is having a hard time accepting it...any ideas as to why? Thanks
alot!

 

[Dave Peterson]

I'd use:

if application.countif(Sheets("SYS").Range("A1:A18"), combobox1.value) = 0 then
msgbox "not found"
....

 

[Dave Peterson]

And just to add to Corey's response, if you change the combobox's Style property
to fmstyledropdownlist, the user won't be able to type anything into that
combobox--except for the items in the list.

 

[Shaka215]

And just to add to Corey's response, if you change the combobox's Style property
to fmstyledropdownlist, the user won't be able to type anything into that
combobox--except for the items in the list.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Yeah I've figured it out...Thanks everyone for the help!