Using ComboBoxes to filter records

[JaneB19]

Good morning everybody!

Does anybody know how to filter records in a spreadsheet using the value selected in a comboBox?

At the moment I have 2 worksheets in my workbook. One contains data (records) and the other one has calculations on it. I have several comboBoxes on the calculations workbook, which I would like to use to help the user filter the data on 1 or both of the workbooks (I'd prefer if it filtered the records on the data sheet, as it's not as important for the calculations).

I would like to filter the records by dates (either month or year, it's a case of getting one working and then the others shouldn't be so bad). I have data in the comboBox done as follows:

DateMonth = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12&quot

For i = LBound(DateMonth) To UBound(DateMonth)
Worksheets("Equations & Formulae&quot.ComboBox1.AddItem DateMonth(i)
Next i

Unfortunately the month is within a date (i.e. "dd/mm/yyyy&quot.

How do I compare the comboBox value with the month within the date?

Your help is very much appreciated. Any more information wanted just let me know. Thank you

Jane

[PC2]

 

[Bowers74]

Month(Date) = Val(ComboBox1) or Val(ComboBox1) = Month(Date) should do the trick! ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JaneB19]

Hi Mike!

Can I ask a question (another one)? Can I assume that "Month" is a built in function, and that "Date" relates to the date in the column "J" on the spreadsheet?
Thanks

Jane

[PC2]

 

[Bowers74]

Yes, Month() is a BuiltIn function in VBA (as it is in Excel), just like Year(). And the Date was supposed to mean the date in the column.

The

Date

in VBA returns the current System Date, just like

Time

retunrs the current system Time.

Sorry for the confusion!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JaneB19]

I've said it before and I'll say it again "Maybe it's me but..."

I'm really sorry Mike but I'm not having much luck with the coding you suggested. :-( I keep getting "Error: "424" Object required". I have been trying to set the column "J" in the Slickline spreadsheet to be the dates that it compares the month comboBox with.

I've read through the 1st post and I don't think that I made it too clear what I'm trying to do, so hopefully the above paragraph helps a little.

Thanks again

Jane

[PC2]

 

[SkipVought]

Hi,

What about year?

Or is Year don't care?

I'd add a column for MONTH and just filter on MONTH



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Hi,

What about year?

Or is Year don't care?

I'd add a column for MONTH and just filter on MONTH

Why bother with a Combobox?



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JaneB19]

Unfortunately I can't create the new column Skip, this is a continuation of the last thread (about inserting new lines). The data is uploaded from scorecards, and I can't edit the data. It doesn't matter if I'm helped with the month or the year because the solution will be pretty smiliar. Or do I assume incorrectly?

I've got several rows of data in the spreadsheet, and it's just a filter that I'm trying to create, but the data is filtered by the month selected from the comboBox. The dates are in the formate of "dd/mm/yyyy" (08/09/2003) and the months in the comboBox are represented by 2 numbers i.e. 01, 02, 03, ... ,10, 11, 12.

Does this make it more or less difficult to solve?

Thanks for your help

Jane

[PC2]

 

[SkipVought]

Then use 2 criteria and construct a date for the 1st of the month and a date for the last of the month

date >= FirstOfTheMonth

AND

date <= LastOfTheMonth



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JaneB19]

I'm not having much luck with this, and I'm assuming that maybe I'm not quite understanding your suggestions correctly. :-(

I have tried a couple of ways of doing the coding, from
Sub FilterDates()
Range(&quot;J:J&quot.AutoFilter Field:=1, _
Criteria1:=&quot;>=01/01/2003&quot;, _
Operator:= xlAnd, _
Criteria2:=&quot;<=31/12/2003&quot;
End Sub

to

Dim iStartMonth As Integer
Dim iStartYear As Integer
Dim dtStartDate As Date
Dim dtEndDate As Date
Dim stStartCriterion As String
Dim stEndCriterion As String

iStartMonth = Sheet2.ComboBox1.ListIndex + 1
iStartYear = Sheet2.ComboBox2.Value

dtStartDate = DateSerial(iStartYear, iStartMonth, 1)
dtEndDate = DateSerial(iStartYear, iStartMonth + 1, 1)

stStartCriterion = &quot;>=&quot; & Format(dtStartDate, &quot;dd/mm/yyyy&quot
stEndCriterion = &quot;<&quot; & Format(dtEndDate, &quot;dd/mm/yyyy&quot

Range(&quot;Slickline&quot.AutoFilter Field:=1,
Criteria1:=stStartCriterion, _
Operator:=xlAnd, _
Criteria2:=stEndCriterion

As well as Mike and Skip's suggestions!

None of which work apart from the 2 above changing column J to 12:00:00AM! Which means it's changing the dates from there dd/mm/yyyy :-(

Can anybody tell me what I'm doing wrong now? I'm running out of patience with this I've been trying to work it out for the last week or so!

Thanks again!

Jane

[PC2]

 

[SkipVought]

Jane,

Turn on the macro recorder and record what works. Then modify the code. Dates can be tricky.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com