Microsoft Excel - VBA Auto Filter data based on user input box criteria

Asked By Loo KL on 23-Oct-10 06:51 AM
Hi All,

I have a worksheet with data in column A to D.  I'd like to create a pop up input box for user select criteria to filter.
Excel create a new worksheet to show the filter result.  User not neccessary select all criterial to filter ;  at least one criterial to filter.

Example :- 

Date        Model    Unit Price      MOQ  (Pcs)
01-Oct-2010   FG 001    $100.00      50
02-Oct-2010   FG002     $250.00      100 
03-Oct-2010   FG003     $300.00      20

Filter criteria : -  
Date --> Filter "after or equal to"    AND   "before or equal to"
Model --> Filter Equals
Unit price -- > Filter "is Greater than or equal to"  AND "is less then or equal to"
MOQ -- > Filter "is greater than"
*** User input box show 4 criteria , user have optional to select one or two or all criteria to filter

Thanks in advance.

RGDS,
Loo
 


 
Rolf Jaeger replied to Loo KL on 23-Oct-10 11:37 AM
Hi Loo:

I had some cycles to spare and created a workbook for you that should meet your expectations:

LooKL_FilterForm.zip

Hope this helped,
Rolf
Loo KL replied to Rolf Jaeger on 23-Oct-10 01:04 PM
Hi Rolf,

I don't know what to say, you're so great!  It work perfect.

Deeply appreciate your helped.

RGDS,
Loo
Rolf Jaeger replied to Loo KL on 25-Oct-10 03:50 PM
Hi Loo:

you are welcome. Thanks for the note of appreciation. I am glad I was able to help.

best wishes,
Rolf
Loo KL replied to Rolf Jaeger on 07-Nov-10 03:51 AM
Hi Rolf,

Sorry, I shouldn't open this incident but I hope you can advice me on how can un-freeze the afte filter data.
Screen seem locked after filter data untill click Exit button to close filter form and clear all filter. 
Its anyway after filter data and not locked screen? Or filter data show in new worksheet?

Thanks in advance.

RGDS,
Loo 
Rolf Jaeger replied to Loo KL on 08-Nov-10 11:56 AM
Hi Loo:

simply modify the RunFilter routine in my original code as shown below (which will cause the user form to be shown modeless). That should do it.

Hope this helped,
Rolf

Sub RunFilter()
  UserForm1.Show (vbModeless)
End Sub

Loo KL replied to Rolf Jaeger on 09-Nov-10 10:17 AM
Hi Rolf,

Yes! Now can scroll and click the data even UserForm is open.

Thanks a lots :)

RGDS,
Loo