CellLink Value From Data Validation List

[specs]

Can i get cellLink values from a Data Validation List?

e.g my Validation List is in cell A1 and i chose the 2nd item.

im currently using: string = Range("A1").Value, which gives me the String of 2nd item. Is there any way i can get a value of 2 using .ListIndex or something?

 

[Tazguy37]

Try this:

Dim myIndex As Long
Dim myRange As Range

    Set myRange = Range(Right(Range("A1").Validation.Formula1, Len(Range("A1").Validation.Formula1) - 1)).Find(Range("A1"))
    myIndex = ActiveCell.Row - myRange.Offset(0).Row + 1

You can name the range referring to A1 in this example for more flexibility. Post back if you have any problems.

 

[specs]

Hi Taz, Thanks for the tip.

but ActiveCell.Row just keeps returning the same value of 1 and myRange.Offset(0).Row returns my items Row number, oftens makes the result a negative number.

It seems fine if i use:

myIndex = myRange.Offset(0).Row

Do u think its ok?