Filter ListBox based on a ComboBox

[brorick]

I have a form based on a range named "database". I am trying to get the form to perform the following.

1. A user can load ListBox1 with data and ComboBox1 with headers(date, invoice, title, rep) from a specific range ("database") after clicking on CommandButton1.

2. A selection from ComboBox1(date) will then auto populate ComboBox2(3/4/08, 3/1/08) with unique data from the corresponding column under the selected header.

3. A selection from ComboBox2 permits the user to filter ListBox1 based on the selection.

My range "database" includes the following test data.
date invoice title rep
3/4/08 a-1011 abc Michael
3/4/08 a-1012 cde Tina
3/1/08 b-2002 fgh Robert


So far I have created code for step 1. Provided the user clicks on the CommandButton1 it works fine. For some reason clicking on the commandbutton1 more than once causes an infinte loop. A problem I will correct at a later time.

[VBA]
Private Sub CommandButton1_Click()
With ListBox1
Do While .ListCount > 1
Loop
End With

Dim rTable As Range
Dim lHeadersRows As Long
Set rTable = Range("database")
lHeadersRows = rTable.ListHeaderRows

If lHeadersRows > 0 Then
Set rTable = rTable.Resize(rTable.Rows.Count - lHeadersRows)
Set rTable = rTable.Offset(1)
End If

ListBox1.RowSource = rTable.Address
ComboBox1.RowSource = rTable.Address
End Sub
[/VBA]
I have searched for the past few days attempting to find vba a code to help me with steps 2 and 3. I am running out of time. Any help would be greatly appreciated.

[mdmackillop]

Can you post a sample workbook?

[brorick]

As requested here is a sample workbook.

[mdmackillop]

If only dates are to be shown in Combo2, what is the purpose of the other values in combo1?

Can you confirm the format of your dates. dd/mm/yyyy or mm/dd/yyyy

[mdmackillop]

Try this.
I've removed the button and used Initialize to populate Combo1. The code uses part of the spreadsheet (cols Z - AD) for filtered data to populate combo2 and listbox1

[brorick]

Mdmackillop thank you for your response to my problem. My final code will hide the workbook and only the form will be visible. Therefore I would like to have the listbox display all of the data from the workbook after the user clicks on CommandButton1. The idea is the user would have the option to select the column headers from ComboBox1 and based on that selection the corresponding unique data will autopopulate ComboBox2. The user then has the option to filter ListBox1 and view the record based on any selection from ComboBox2.

Example:

ComboBox1: date
ComboBox2: 3/1/2008, 3/4/2008

ComboBox1: invoice
ComboBox2: a-1011, a-1012, b-2002

ComboBox1:title
ComboBox2: abc, cde, fgh

ComboBox1: rep
ComboBox2: Michael, Tina, Robert

Basedon the Examples above, if the user selects date from ComboBox1 and then 3/4/2008 from ComboBox2 then these records will display in ListBox1.

date invoice title rep
3/4/08 a-1011 abc Michael
3/4/08 a-1012 cde Tina

Thanks again in advance for the assistance.

[mdmackillop]

Try

[brorick]

Mdmackillop you have my deepest gratitude. It works like a charm. I should have started my journey here first and my stress and agrevation would never had existed. This is what I had been searching for over the past several days. Thanks again.

[mdmackillop]

Stress and aggravation? I thought that's what computers were designed to create.

[brorick]

Based on the solution to this question I wanted to take the next step and allow the user the option to double click on an item in the listbox and then open another form with the details of the selected item. Currently the code I have only allows me to do this provided the fields are on the same form. I am not sure how to hold the data in the array and then autopopulate the new form with the data.

He is what I have so far.

[vba]Private Sub lbGrid_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim strCriteria As String
Dim i As Integer


For i = 0 To ListBox1.ListCount + 1
If ListBox1.Selected(i) Then
strCriteria = ListBox1.ListIndex + 2
Set rgData = Range("Database").Rows(strCriteria)
Call GetFormRecords
UserForm1.Hide
UserForm2.Show
txtRow.Value = strCriteria

End If
Next i
End Sub[/vba]

[brorick]

Based on the solution to this question I wanted to take the next step and allow the user the option to double click on an item in the listbox and then open another form with the details of the selected item. Currently the code I have only allows me to do this provided the fields are on the same form. I am not sure how to hold the data in the array and then autopopulate the new form with the data.

He is what I have so far.

[vba]Private Sub lbGrid_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim strCriteria As String
Dim i As Integer
Dim VarInfo as Variant
Dim rgData as Range

For i = 0 To ListBox1.ListCount + 1
If ListBox1.Selected(i) Then
strCriteria = ListBox1.ListIndex + 2
Set rgData = Range("Database").Rows(strCriteria)

UserForm1.Hide
UserForm2.Show
txtRow.Value = strCriteria

UserForm2.vaInfo = rgData.Value
UserForm2.txtDate.Value = vaInfo(1, 1)
UserForm2.txtInvoice.Value = varInfo(1, 2)
UserForm2.txtTitle.Value = varInfo(1,3)

End If
Next i
End Sub[/vba]

The code I have referenced is unfortunately not working for me as I had hoped. Any help would be greatly appreciated. Thanks in advance.

[mdmackillop]

Why not keep on the same form. You can unhide hidden controls to show the next step: consider Click or Change events rather than Double click as a more user friendly solution. Think through the design with care and it can greatly simplify the solution. If you can post your input form and desired results, maybe we can suggest a better solution.

[brorick]

Hello Mdmackillop. I appreciate the advice. The final form we are creating is much larger than the sample form and includes many other buttons and fields. After careful consideration it was decided to pursue the option of utilizing two forms instead of just one.

The initialization of the form that automatically opens by default will display the first record in the list, which is ok with me. But, the challenge is when the user decides to view all records and then double clicks on an item on the list it should open the detail form and display the selected record.

Any help someone could provide would be greatly appreciated.