MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combo Box - Extract Dates

Posted by JAF on February 06, 2001 6:38 AM


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.


Posted by Dave Hawley on February 06, 2001 8:20 AM

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
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 _
End With
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

OzGrid Business Applications