CODEWITHSUNDEEP
arraysvalidationvbaelement
DevilWAH
DevilWAH

Reputation: 2643

cell validation and arrays

two questions, first can you remind me how to polulate a multidimensional array when assigning it? I always do it with for loop and how to staticly do it seems to escape me?

But what I am really looking to do is to use the array to validate cells contents. however in the example below I want to use only the first element in the 2nd dimension.

ie
0,0
1,0
2,0
3,0
4,0

Is there any way to update the .Add Type:=xlValidateList, Formula1:=Join(ary, ",") line so it only returns these 5 values from a 5 by 4 array?

Cheeers

aaron

Sub test()


Dim ary As Variant
ReDim ary(5,4)


ary = Array("Value1", "Value2", "Value3", "test", "test2", "test3", "test4")

With ActiveSheet.Cells(1, 1).Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(ary, ",")
    .IgnoreBlank = False
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
End Sub

Upvotes: 0

Views: 1908

Answers (1)

brettdj
brettdj

Reputation: 55682

The resizing of a 2D array to 1D is quick

The code below populates your 5*4 array with sample data, a second loop then extracts only the first dimension to a new 1D array withe the same row length as your initial array

Sub test()

    Dim ary As Variant
    Dim X As Variant
    Dim lngRow As Long
    Dim lngCol As Long

    ReDim ary(1 To 5, 1 To 4)
    ReDim X(1 To UBound(ary, 1))

    For lngRow = 1 To UBound(ary, 1)
        For lngCol = 1 To UBound(ary, 2)
            ary(lngRow, lngCol) = "I am row " & lngRow & "  and dimension " & lngCol
        Next
    Next

    For lngRow = 1 To UBound(ary, 1)
    X(lngRow) = ary(lngRow, 1)
    Next

    With ActiveSheet.Cells(1, 1).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Join(X, ",")
        .IgnoreBlank = False
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Upvotes: 2

Related Questions