Data validation list - source

0
votes

Can I locate source of data validation list in VBA?

Here's example. I have cell 'A1' in sheet 'List1' and I have list data validation on this cell in sheet 'List2'.

In sheet 'List2' I have several values for cell A1 (List1), like 'Banana', 'Apple'.... These values are in column A, and in column B i have numbers, like 1, 2....

Is it possible to check in VBA, that there is data validation on cell A1 (List1) , that is located in column A (List2). And is it possible to get number from column B in relation to value 'Banana'

I'm not sure, if It's understandable.... Thanks for any advice :)

1
vbaexcel

1 Answers

1
votes

I am not sure if this is what you want, but you can check the type of data validation in a cell and then return the list range:

With Sheets("List1").Range("A1").Validation
 If .Type = 3 Then ' data validation is a list ?
  MsgBox .Formula1
 End If
End With