Referring to dropdown list value in VBA 2

[rafal36]

Hi everyone,
I have a dropdown list (data range) named "nr_tyg3" and I want to check if value in a given cell belongs to that list or not.
How do I refer to that dropdown list?

IF variable1 <> value_from_dropdown THEN do_something

Thanks for your tips.

 

[Andrzejek]

If this is on a UserForm and your dropdown list is named nr_tyg3, try something like:

Dim i As Integer
Dim blnIsFound As Boolean

For i = 0 to nr_tyg3.ListCount - 1
    If MyVariable = nr_tyg3.List(i) Then
        [green]'found it[/green]
        blnIsFound = True
        Exit For
    End If
Next i

If blnIsFound Then
    MsgBox "BINGO!"
Else
    MsgBox "Sorry  :-("
End If

Have fun.

---- Andy

 

[rafal36]

My dropdown list is not in userForm, it's in a separate sheet. So now excel says "Object required"

 

[SkipVought]

Hi,

I have a dropdown list (data range) named "nr_tyg3"

You have a DATA RANGE named "nr_tyg3", that you are refernecing in a dropdown.

I want to check if value in a given cell belongs to that list or not.

How do you refer to the named range?

dim r as range, bFound as Boolean
bFound = false
for each r in [nr_tyg3]
  if variable1 = r.value then bFound=true
next
if not bFound then do something

or

if iserror(application.match(variable1,[nr_tyg3],0)) then do something

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}