Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.