data validation excel vba

Question

Option Explicit

Sub MakeValidationList()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim dataRange As Range
    Set dataRange = ws.Range("A1:A3,C1:C3")

    Dim dataList As String
    Dim entry As Variant
    For Each entry In dataRange
        dataList = dataList & entry.Value & ","
    Next entry
    '--- remove the last trailing comma
    dataList = Left$(dataList, Len(dataList) - 1)

    Dim dropDownCell As Range
    Set dropDownCell = ws.Range("B3:B10")
    dropDownCell.Validation.Delete
    dropDownCell.Validation.Add Type:=xlValidateList, _
                                AlertStyle:=xlValidAlertStop, _
                                Formula1:=dataList
End Sub

from the data above, how can the datavalidation range be taken from sheet2, because it works for sheet1 (the same sheet)? request the learning

JvdV · Accepted Answer · 2020-01-16 07:52:09Z

1

I would say there are two easy options to pull data from an other sheet into a Validation.Add method. The below is just an example to show you the technique:

Sub Test()

'Set your two worksheets
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("Sheet2")

'Set your range objects where you want a validation list
Dim rng1 As Range: Set rng1 = ws1.Range("A1")
Dim rng2 As Range: Set rng2 = ws1.Range("B1")

'Set your source range
Dim rng3 As Range: Set rng3 = ws2.Range("A1:A3")

'Set an array (for option 1)
Dim arr As Variant: arr = rng3.Value

'Option 1:
rng1.Validation.Add xlValidateList, Formula1:=Join(Application.Transpose(arr), ",")

'Option 2:
rng2.Validation.Add xlValidateList, Formula1:="='" & ws2.Name & "'!" & rng3.Address

End Sub

With option 1 you can add any 2D-Array to the validation list (drop the Application.Transpose if you already got one in memory).

With option 2 you refer to another worksheet within a concatenated formula.

edited Jan 16, 2020 at 7:52

answered Jan 16, 2020 at 7:46

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Indra Royal Silver Over a year ago

i try. run time error 1004 application-defined or object-defined error, you can send me vba excel sample to [email protected]

Collectives™ on Stack Overflow

data validation excel vba

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related