How to create a Unique Drop down of Employee names based on name and hire date, filtered by new hires and sorted

[cuban2]

0
I am trying to create a drop-down list of all new employees based on name and hire date on one sheet from data on another sheet.

My problem is when the code gets to this line: it runs the Function CollectionToArray but it stops when it returns to this line.
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=" & Join(CollectionToArray(uniqueEmployees), ",")

I get a "application-Defined or object-defined" error

*******************************************************
Private Sub Worksheet_Activate()
Dim employeeWs As Worksheet
Dim bonusWs As Worksheet
Dim employeeRng As Range
Dim hireDateRng As Range
Dim newDataRng As Range
Dim uniqueEmployees As Collection
Dim employeeName As Variant
Dim hireDate As Variant
Dim dropdownRng As Range

Set employeeWs = ThisWorkbook.Sheets("acm new letter")
Set bonusWs = ThisWorkbook.Sheets("ACM Bonus")
Set employeeRng = bonusWs.Range("C3:C" & bonusWs.Cells(Rows.Count, 3).End(xlUp).Row)
Set hireDateRng = bonusWs.Range("E3:E" & bonusWs.Cells(Rows.Count, 5).End(xlUp).Row)
Set newDataRng = bonusWs.Range("J3:J" & bonusWs.Cells(Rows.Count, 10).End(xlUp).Row)

Set uniqueEmployees = New Collection

On Error Resume Next
For Each hireDate In newDataRng
If Not employeeWs.Range("D13").Validation Is Nothing Then
employeeWs.Range("D13").Validation.Delete
End If

If hireDate.Value <> "" Then
employeeName = employeeRng.Cells(Application.Match(hireDate.Value, hireDateRng, 0))

If Not IsError(employeeName) Then
uniqueEmployees.Add employeeName, CStr(employeeName)
End If
End If
Next hireDate
On Error GoTo 0

Set dropdownRng = employeeWs.Range("D13")
With dropdownRng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=" & Join(CollectionToArray(uniqueEmployees), ",")
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With

End Sub

Function CollectionToArray(col As Collection) As Variant
Dim arr() As Variant Re
Dim arr(1 To col.Count)
Dim i As Long
For i = 1 To col.Count
arr(i) = col(i)
Next i

CollectionToArray = arr

End Function

 

[Domenic]

Try...

Formula1:=Join(CollectionToArray(uniqueEmployees), ",")

Hope this helps!

 

[cuban2]

Hello Domenic, Unfortunately, your suggestion did not work. thank you