How do implement dynamic data validation e.g. as Excel VBA-function?

Question

Data validation in Excel is a helpful way to verify user input in Excel. The standard way is to (1) define somewhere (e.g. on an auxillary sheet) a list with possible input values and (2) then choose that range in the Source field. Alternatively, one can also directly type in the different options in that field, e.g:

My question: How do we make the data-validation list dynamic?

What I tried so far is to enter a (possibly user-defined VBA) formula returning a list of strings in into the field Source of the data-validation dialog, for example

=INDEX({"New","Mint","Very Good","Good","Acceptable","Poor"},{1,RANDBETWEEN(1,6)})

However, this approach does not work as it leads to an error message

You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data Validation criteria.

What do I miss? Which (possibly more elegant) way of making the Source of the data validation dynamic do you suggest?

Edit: My concrete problem:

On all (but the first) tab sheet, I have a cell called myTest and the list of allowed values is for a cell on the first tab, where I want be able from the list composed of all possible values of myTest.

References:

Debra Dalgleish: "Create Dependent Drop Down Lists" at contextures.com
Dynamic Data Validation in Excel (Non-VBA!)

i did it once with a bunch of hidden columns. the hidden columns had formulas which gave different values depending on certain entries in that row. these values could then be used for data-validation in other cells in the same row. — horst
When you say "make it dynamic", are you saying the source will change complete locations, or you want to have the last row of a source sheet dynamically found and include the data within? Essentially, you give info about a general concept without providing any foundation for what is and what is desired in your particular case, which would lead to an opinionated or open-ended discussion, not an answerable question, which this forum requires. — Cyril
@Cyril's comment is on-point: exactly what do you want to do, or are you just exploring? If you use a VBA function as the source for a validation list, it must return a range on a worksheet, and that range must be contiguous. — Tim Williams
@horst: Your approach does not work for my case since the length of the range of allowed values may vary. — B--rian
I believe there's a limit on the length of the comma-separated list you can paste into the DV dialog - any longer than that and you need to point it at a range instead. — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2019-04-01T18:49:11

You can use something like this in the first sheet's code module:

Private Sub Worksheet_Activate()

    Const LIST_COL As Long = 26
    Dim sht As Worksheet, i As Long

    i = 1
    Me.Columns(LIST_COL).ClearContents       '<< clear current list
    For Each sht In ThisWorkbook.Worksheets  '<< collect all the values
        If sht.Name <> Me.Name Then            
            Me.Cells(i, LIST_COL).Value = sht.Range("myTest").Value
            i = i + 1
        End If
    Next sht

End Sub

It will collect the values from the other sheets into a range you can reference for the validation list.

Note: if the other sheet's values are updated via code or formulas (ie. do not require you to go to each sheet to change values) then you'd need to do a bit more work.

EDIT - this is more convoluted but works more reliably, since it gets run whenever you click on the validation drop-down

1. Put this in a regular module (edit as required):

'A function to return a range containing the 
'  various values which need to appear in the validation list
Public Function ListCompile() As Range

    Const LIST_COL As Long = 26      '<< create the list in Col Z
    Dim sht As Worksheet, i As Long

    i = 1
    Sheet1.Columns(LIST_COL).ClearContents
    For Each sht In ThisWorkbook.Worksheets
        If sht.Name <> Sheet1.Name Then
            Sheet1.Cells(i, LIST_COL).Value = sht.Range("myTest").Value
            i = i + 1
        End If
    Next sht
    'return the list we just created
    Set ListCompile = Sheet1.Cells(1, LIST_COL).Resize(i, 1)
End Function

Note: I'm using the codename for Sheet1 (which might be different from the tab name). You can see the code name in the VB editor Project Explorer.

2. Define a named range "tester" with "RefersTo" equal to ListCompile()

3. Finally set your data validation list range to: =tester

How do implement dynamic data validation e.g. as Excel VBA-function?

2 Answers