Problem is that the column I'm filling the combo box with is full of dates, and although the code does the job, it's in the american format (m/d/y)
Private Sub Worksheet_Activate() Dim wbBook As Workbook Dim WS As Worksheet Dim WS2 As Worksheet Dim rnData As Range 'Variant to contain the data to be placed in the combo box. Dim vaData As Variant 'Initialize the Excel objects Set wbBook = ThisWorkbook Set WS = wbBook.Worksheets("ShiptToy") Set WS2 = wbBook.Worksheets("Toyota") 'Set the range equal to the data, and then (temporarily) copy the unique values of that data to the L column. With WS Set rnData = .Range(.Range("F2"), .Range("F1000").End(xlUp)) rnData.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=.Range("AA1"), _ Unique:=True 'store the unique values in vaData vaData = .Range(.Range("AA2"), .Range("AA1000").End(xlUp)).Value 'clean up the contents of the temporary data storage .Range(.Range("AA1"), .Range("AA1000").End(xlUp)).ClearContents End With 'display the unique values in vaData in the combo box already in existence on the worksheet. With WS2.OLEObjects("ComboBox1").Object .Clear .List = vaData .ListIndex = -1 End With End Sub
From novice to tech pro — start learning today.