VBA Code - Changing Data Validation List source and apply to a column in another table

[Jack_881]

Hi All

I have the code below which is meant to perform the following:

1. Use a table in a worksheet to get a list of technician names to use as a data validtation list in another table on different worksheet
2. Remove existing validation in the target table and replace with the new data validation list
3. This new validation list to apply in all cells in a specified column of the target table

The issue I have is the new validation list is only being applied to the first row in the column and each subsequent row, a name from the top of the list drops off, so on an so on. Hoping someone can point out the error in my code.


Sub SetDataValidation()
Dim wsData As Worksheet
Dim wsList As Worksheet
Dim lo As ListObject
Dim rngList As Range
Dim tblData As ListObject


'Set references to the worksheets and list object
Set wsData = Worksheets("Orders")
Set wsList = Worksheets("Technicians")
Set lo = wsList.ListObjects("tblTechs")
Set tblData = wsData.ListObjects("tblData")

'Set range to the column in the list object
Set rngList = lo.ListColumns(1).DataBodyRange


'Set data validation source to the range in the other worksheet
With tblData.ListColumns(10).DataBodyRange.Validation
.Delete 'Clear existing validation first if needed
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="='" & wsList.Name & "'!" & rngList.Address(False, False)

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

End With

End Sub

 

[Fluff]

Try

Operator:=xlBetween, Formula1:="='" & wsList.Name & "'!" & rngList.Address

 

[Jack_881]

Yes, that was it.

Many thanks.

 

[Fluff]

You're welcome & thanks for the feedback.