Filter Data In User Form

adam · Post by **adam** » 05 Jun 2010, 14:22

I've applied a worksheet event code to the "Orders" sheet which filters the sheet "Orders" when the value in the cell J3 is changed.

In the current version of the user form when the listbox1 is double clicked the value that is in the Combobox; cboSerialNo gets copied to the cell J3 of the worksheet "Orders" which then filters the worksheet with the value in the combo box.

What Im trying to do is to use the range =COUNTIF(A5:H5,$J$3)>0 Instead of SourceRng =Orders!$M$2:$T$2 . and the filtered range from A5:H5 to be populated in the listbox2 of the user form.

I would be happy If I'm told why I cannot get the range I'm trying to get.

Post by **HansV** » 05 Jun 2010, 16:40

I'm sorry, I don't understand your question. =COUNTIF(A5:H5,$J$3)>0 is not a range but a formula.

adam · Post by **adam** » 05 Jun 2010, 17:45

What I meant is the current range that is used by the userform to filter the data is SourceRng =Orders!$M$2:$T$2. Instead of this range how can I make the range that is filtered from A5:H5 to appear on listbox2

Post by **HansV** » 05 Jun 2010, 18:41

If you change the line

With Sheets("Orders").Range("M1")

in cboSerialNo_Change to

With Sheets("Orders").Range("A4")

does that do what you want?

adam · Post by **adam** » 05 Jun 2010, 18:52

I did try using the modification you suggested but It does show the filtered ranges after the first double click in the listbox1. But After wards if I clear the text in cell J3 I cannot get back all the rows of data that I was having in he sheet Orders. Also it shows repeated rows of data for serial numbers like 0697. Initially the rows with serial 0697 is three. But after filtering it shows more that three rows.

Post by **HansV** » 05 Jun 2010, 18:57

I see. I'm afraid you can't do it that way - if you set the row source of a list box to a filtered range, the list box will still show the entire range, including the hidden rows. That's why the code copies the filtered data to another range (starting at M1).

adam · Post by **adam** » 05 Jun 2010, 19:13

The code in the posted in the Post=18358 loads the listbox2 from the filtered range (starting at M1). What I'm trying to do is that instead of this range to use the filtered range starting from A4. According to you this cannot be done. Am I right?

When the range is changed to, With Sheets("Orders").Range("A4") it doesn't show all the rows when the sheet is filtered, it shows the filtered rows only. the problem is after filtering the rows I cannot get back the total rows that were filtered.

Post by **HansV** » 05 Jun 2010, 19:20

The range starting at A4 is the original source range. If you overwrite it with the filtered data, you lose the original data. That's why the change I mistakenly proposed doesn't work correctly.

And indeed, you can't use the filtered data starting at A4 as row source for the list box.

adam · Post by **adam** » 06 Jun 2010, 18:32

Iâ€™ve changed the code that it filters the range and also I have replaced cboSerialNo with txtSerialNo so that as the sheet gets filtered, the list box also gets filtered with the change of value in txtSerialNo.

When the filter button is clicked the sheet gets filtered as well as the list box. But the list box does not seem to get filtered with the value that is in txtSerialNo. Instead it gets filtered with the first row in the worksheet.

When the clear filter button is clicked the sheet & the list box gets unfiltered.

Any Advice would be kindly appreciated how to filter the list box with the rows filtered with the worksheet.

Post by **HansV** » 06 Jun 2010, 19:10

I think the definition of the named range Rng is incorrect. It should include the column headers, i.e. it should start at A4 instead of A5.

adam · Post by **adam** » 06 Jun 2010, 19:27

According to your suggestion the sheet and the list box gets filtered if the serial number is "written" in the text box. But neither the worksheet nor the list box gets filtered when the text box is filled by double click event procedure from the listbox1.

What may be the reason for this.

Post by **HansV** » 06 Jun 2010, 19:31

Try using the txtSerialNo_Change event instead of the txtSerialNo_AfterUpdate. The latter doesn't get called if the text box is changed by code.

adam · Post by **adam** » 06 Jun 2010, 19:39

Thanks Hans. Wow! that works finally, as you might see the list box now does get "Filtered" as the worksheet gets filtered.

By the way, why doesn't the listbox2 show its column headers.

Post by **HansV** » 06 Jun 2010, 19:46

A list box can only show column headers if its RowSource is a range address, not if you set its List using code.
Since you must set the List using code if you want to display only the filtered cells, the list box cannot display column headers.
A workaround could be to place labels above the list box that show the column headers.

adam · Post by **adam** » 06 Jun 2010, 19:53

Thanks for the suggestion. already I'm doing so. Since I'm having combo boxes in the user form I have changed the UserForm_Initialize event code. But the form does not seem to load up. What have I done wrong.

Post by **HansV** » 06 Jun 2010, 20:02

You can't have a line

End If

if there isn't a corresponding line

If ... Then

above it in the same procedure, and similarly, you can't have a line

End With

if there isn't a corresponding line

With ...

above it in the same procedure. Simply remove the two offending lines, they don't serve any purpose.

adam · Post by **adam** » 06 Jun 2010, 20:14

Thanks for the reply the UserForm_Initialize event code now works

Post by **HansV** » 06 Jun 2010, 21:57

Adam, I see that you have removed several attachments and code fragments from this thread, making it incomprehensible to others reading it. The help provided in this board is not just for you personally, everone should be able to learn from it.

Please don't do this again - ever! Otherwise you won't get any help from me any more.

adam · Post by **adam** » 07 Jun 2010, 08:53

HansV wrote:Adam, I see that you have removed several attachments and code fragments from this thread, making it incomprehensible to others reading it. The help provided in this board is not just for you personally, everone should be able to learn from it.

Please don't do this again - ever! Otherwise you won't get any help from me any more.

I'm aware that the help provided in this board is not just for me personally, and that everyone should be able to learn from it. And because of this I too have loaded some sample workbooks with a little bit of adjustments so that others could get help from them too.
The workbooks that had been removed consisted of some personal data which I had mistakenly uploaded.
In reference to editing I had edited some posts so that another member could easily view the codes.
If removing my workbooks from this board matters, I'm sorry for that & it wont happen again. Keep this as a promise.
Meanwhile, I hope that the codes I had brought up to this board also would be appreciated the same way the codes that I had removed had been spot out.

Eileen's Lounge

Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form

Re: Filter Data In User Form