Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
I have a worksheet which has in column A a series of dates, some of which are unique, others of which are duplicated many times.
What I'd like to do is on another worksheet create a combobox on the sheet itself (not in a user form) which will be populated with a list of the dates from column A on the other sheet, but with each date appearing once only.
When the user selects a date from the list, I need that date to appear in Cell A2.
Any suggestions gratefully received.
JAF
Hi Jaf
Place a Combobox from the Control Toolbox on your sheet. Double click it and add this code.
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex > -1 Then
ComboBox1 = Format(ComboBox1, "dd/mm/yyyy")
[A2] = ComboBox1
End If
End Sub
Private Sub ComboBox1_LostFocus()
Dim LastRw As Range
Dim FirstRw As Range
Dim ListRange As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error Resume Next
Sheets.Add().Name = "LISTSHEET"
If ActiveSheet.Name <> "LISTSHEET" Then ActiveSheet.Delete
Range("MyList").Clear
On Error GoTo 0
Set FirstRw = ActiveSheet.Columns(1).Find _
(What:="*/*/*", after:=[A1])
Set LastRw = [A65536].End(xlUp)
With Range(FirstRw, LastRw)
.AdvancedFilter _
Action:=xlFilterInPlace, Unique:=True
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("LISTSHEET").Range("A1")
End With
ActiveSheet.ShowAllData
With Sheets("LISTSHEET")
ListRange = .Range("A1").CurrentRegion.Address
ActiveWorkbook.Names.Add _
Name:="MyList", RefersTo:="=LISTSHEET!" & ListRange
.Visible = xlVeryHidden
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
ComboBox1.ListFillRange = "MyList"
[A2].NumberFormat = "dd/mm/yyyy"
End Sub
You may need to change the date format and the "*/*/*" within the Find to the type of date seperator your PC has in it's regional settings.
Hope this helps
dave
OzGrid Business Applications