PDA

View Full Version : [SOLVED] Combo box filtering?



Chunk
12-23-2016, 08:03 AM
I have the following scenario:

A user selects a project in a combo box (cb_Proj).

The projects dates range populates a second combo box (cb_ProjDate).

I have been successful thus far. Now my issue:

I would like the cb_ProjDate combo box to auto select (populate) with the next date that is less than today's date.

Example:

Today is 12/23/16.

The selected projects date range is as follows (1/3/16, 5/3/16, 7/3/16, 1/1/17).

I want the cb_ProjDate combo box to initial fill with the 7/3/16 date.

Any help is greatly appreciated. Thank you in advance.

Chunk

mikerickson
12-25-2016, 10:54 PM
Something like


Dim Mem as Long, i As Long

Mem = -1

With ComboBox1
For i = 1 To .ListCount - 1
If CDate(.List(i)) < Date Then
If mem = -1 Then mem = i
If CDate(.List(mem)) < CDate(.List(i)) Then
mem = i
End If
End If
Next i

.ListIndex = mem
End With

Chunk
12-27-2016, 06:44 AM
Mike,

Here is the code I am using. When I inserted your code, I am receiving an "Object Required" error. The two combo boxes that I am working with are cbo_project and cb_DDate. Is it the method that I am using to populate the cb_DDate box? Any ideas?


Dim ws As Worksheet
Dim Rng As Range
Dim FindString As String
Dim Mem As Long, i As Long

Mem = -1

Set ws = ThisWorkbook.Worksheets(UserForm2.cbo_project.Text)

Worksheets(UserForm2.cbo_project.Text).Activate

'Populates dates dependant on project selected
Range("L3", Range("L" & Rows.Count).End(xlUp)).Name = "Dynamic"

UserForm2.cb_DDate.RowSource = "Dynamic"


'Selects last date with data dependant on project selected

With cb_DDate
For i = 1 To .ListCount - 1
If cb_DDate(.List(i)) < Date Then
If Mem = -1 Then Mem = i
If cb_DDate(.List(Mem)) < cb_DDate(.List(i)) Then
Mem = i
End If
End If
Next i

.ListIndex = Mem
End With

Chunk

Chunk
12-27-2016, 08:14 AM
Sooooo,

After a little fumbling around, I got it to work. Instead of declaring "i" as Long, I changed it to an Integer and it worked fine.

A huge thanks to Mike for the quick assistance.

Chunk