Solved: Data Validation / Array

[Philcjr]

How can you add the values of an Array into Cell Data Validation?

*Using Excel 2000
*Lists are on a seperate worksheet
*EndUsers need to maintain list(s) and they do not know VBA

Here is some of my code:


Dim vDocType As Variant, vTestType As Variant, vDept As Variant
Dim vEngineer As Variant, vProductFamily As Variant

With ThisWorkbook.Sheets(1) 'Finds vaules in Col A through Col E
vDocType = .Range(.Cells(3, 1), .Cells(.Range("A65536").End(xlUp).Row, 1))
vTestType = .Range(.Cells(3, 2), .Cells(.Range("B65536").End(xlUp).Row, 2))
vDept = .Range(.Cells(3, 3), .Cells(.Range("C65536").End(xlUp).Row, 3))
vEngineer = .Range(.Cells(3, 4), .Cells(.Range("D65536").End(xlUp).Row, 4))
vProductFamily = .Range(.Cells(3, 5), .Cells(.Range("E65536").End(xlUp).Row, 5))
End With

Dim EndRow As Long
EndRow = ThisWorkbook.Sheets(2).Range("B65536").End(xlUp).Row

If Not Intersect(Range("C2:C" & EndRow), Target) Is Nothing Then
With ActiveCell.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=vEngineer
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Please Use the Pull-down!"
.InputMessage = " " & vbNewLine & _
"Please Select The Correct Value" & vbNewLine
.ErrorTitle = "Please Try Again!"
.ErrorMessage = "You must use the values from the Pull-down"
.ShowInput = True
.ShowError = True
End With
Else
Exit Sub



Thanks,
Phil

[lucas]

Wouldn't dynamic named ranges make this work for you?
users can add and delete from the range and the name adjusts to the changes...

[Philcjr]

Can we say DUH.... Yes this works much better... and much easier

Thanks,
Lucas

However, is it possible to do what I thought?