CODEWITHSUNDEEP
excelvbacombobox
Synaptic Engima
Synaptic Engima

Reputation: 157

How to fill a combobox with a dataset that matches a specific date

I am creating a userform in Excel that allows the user to review all records that were entered on a selected date. The date is selected using a combobox populated with dates from the current week. Then all other actions are triggered by a command button.

I am trying to figure out how to populate the review combobox and/or review listbox with all data from the named range that matches the selected date. Any help is appreciated. The following code includes a Vlookup command that is a deadend.

worksheet: Data_Entry
named range: Records_Entered
userform: ufrmDataEntry1
date combobox: CboReviewWeek
command button: CmdReviewCount
review combobox: CboReviewRecords
review listbox: LstReviewRecords

Private Sub UserForm_Initialize()
    'fill combobox
    Me.CboReviewWeek.List=[index(Text(today()-weekday(today(),2)+row(1:7),"mm/dd/yyyy"),)]
End Sub

Private Sub CmdReviewCount_Click()
    'Step 1) pass selection of CboReviewWeek to "Formulas" sheet
    ActiveWorkbook.Sheets("Formulas").Range("A4") = Me.CboReviewWeek
    'Step 2) Return count of total records entered on selected date
    Me.TxtReviewCount = ActiveWorkbook.Sheets("Formulas").Range("A5")
    'Step 3) Return records entered to listbox
    var1 = WorksheetFunction.VLookup(CboReviewWeek.Value, Worksheets("Data_Entry").Range("Records_Entered"), 2, False)
    LstReviewRecords.Value = var1
    CboReviewRecords.Value = var1
    'Me.LstReviewRecords.List = ActiveWorkbook.Sheets("Data Entry").Range("Records_Entered")
    'Is broke here
End Sub

Upvotes: 2

Views: 978

Answers (1)

hpf
hpf

Reputation: 428

There are several ways to get the results you wish. The solution may depend upon two issues you didn't mention -- whether you're storing just dates or datetimes, and whether your data is sorted by the date entered or is in some other order. The latter is critical -- if the data is sorted by the date it was entered, then the group of records you're looking for is contiguous. If not, then they will be scattered through your worksheet.

Assuming your records are in order, you simply need to find the start and end rows. I am assuming that the date you are looking for in the Records_Entered range is in the first column -- if not, you'll need to change the "1"'s in the code to match.

Dim R as Range, NumRows as integer

NumRows=Worksheets("Data_Entry").Range("Records_Entered").Rows.Count
Set R=Worksheets("Data_Entry").Range("Records_Entered").Columns(1).Find(What:=CboReviewWeek.Value, after:=Worksheets("Data_Entry").Cells(NumRows,1))
Do While R.value=CboReviewWeek.Value
  CboReviewReviewRecords.addItem R.value (Or, if you want to return the second column like in your VLOOKUP above, use R.offset(0,1).value)
  Set R=R.offset(1,0)
Loop

The "after" attribute in the find is needed because, unfortunately, Excel starts the search with the first cell and only looks at cells after it -- i.e. if the date selected is actually the first date in the range, the find will result in the second row. By starting the search in the last row, it forces Excel to wrap to the the first row to start the search.

If the records are not sorted in order, or if you just want a more flexible solution, you could use FindNext instead:

Dim R as Range, NumRows as integer, FirstCell as Range

NumRows=Worksheets("Data_Entry").Range("Records_Entered").Rows.Count
Set R=Worksheets("Data_Entry").Range("Records_Entered").Columns(1).Find(What:=CboReviewWeek.Value, after:=Worksheets("Data_Entry").Cells(NumRows,1))
Set FirstCell = Nothing
Do While Not R is Nothing and R<>FirstCell
  If FirstCell is Nothing then Set FirstCell = R
  CboReviewReviewRecords.addItem R.value (Or, if you want to return the second column like in your VLOOKUP above, use R.offset(0,1).value)
  Set R=Worksheets("Data_Entry").Range("Records_Entered").Columns(1).FindNext
Loop

If you have Date/Time's in the column you're looking at, it gets much more complicated

Hopefully this gets you headed in the right direction

Upvotes: 1

Related Questions