Add data validation to a table from a 3D array, VBA excel

Question

I've got a 3D array of strings, myArray(1 to 30,1 to 80,1 to 20). In my workbook I have a table that occupies the range (1,1) to (30,20). I want to use the validation.add type method to validate the cells of the table in the following order:

the 1st column of the table:

cell(1,1) should contain the values myArray(1,1,1) to myArray(1,80,1)

cell(2,1) should contain the values myArray(2,1,1) to myArray(2,80,1)

...

cell(30,1) should contain the values myArray(30,1,1) to myArray(30,80,1)

the 2nd column of the table:

cell(1,2) should contain the values myArray(1,1,2) to myArray(1,80,2)

cell(2,2) should contain the values myArray(2,1,2) to myArray(2,80,2)

...

cell(30,2) should contain the values myArray(30,1,2) to myArray(1,80,2)

and so on..

In short, each cell in the table should contain drop lists with data extracted from the array (each column of the table contains values from a different page of the array).

I apologize if my question isn't well specified, I'm new to programing and have little experience with VBA.

Alexander Bell Alexander Bell · Accepted Answer · 2016-03-27T16:11:51

The following simplified solution demonstrates adding the Validation DropDown List to the Worksheet Cell "A1" based on the Array List1 values:

Sub AddValidation()
    Dim List1(1 To 5) As String
    List1(1) = 1
    List1(2) = 2
    List1(3) = 3
    List1(4) = 4
    List1(5) = 5

    With Range("A1").Validation
        .Add Type:=xlValidateList, Formula1:=Join(List1, ",")
        .InCellDropdown = True
    End With
End Sub

In order to dynamically modify the validation list added to Worksheet Cells, refer to the solution shown below:

Sub AddValidationDynamicList()
    Dim List1(1 To 5) As String
    List1(1) = 1
    List1(2) = 2
    List1(3) = 3
    List1(4) = 4
    List1(5) = 5

    Dim str As String
    str = Join(List1, ",")

    For I = 1 To 5
        With Range("A" & I).Validation
            .Add Type:=xlValidateList, Formula1:=str
            .InCellDropdown = True
        End With
        str = Mid(str, InStr(1, str, ",") + 1)
    Next I
End Sub

You can further extend this solution pertinent to your requirements of using multidimensional array.

Hope this will help.

Add data validation to a table from a 3D array, VBA excel

1 Answers