PDA

View Full Version : Creating a form to filter data?



Smeghead
05-13-2016, 05:20 AM
Hi,

I'm trying to create a form which allow me to type some data in and filter it down, however I've never really worked with forms before, so I'm not sure how to link it with the data. Here is a screenshot mockup of what I'd like to do:

i.imgur.com/ONouM7X.jpg

The 3 "style code" boxes are the season/STY_NUM/STY_QUAL fields, and the colour and fit are, well, the colour and fit fields.

It doesn't need to be all fields filled in to bring up the results, i.e. if I just type in "AAA" to the first box and leave the rest blank, I'd still like it to only filter down to any rows with AAA in the season box, and likewise if I fill in all of the fields with specific data, then filter down to that specific combination.
Also I don't know if it matters or not, but the spreadsheet is refreshable and the amount of rows will change, so it would need to work with the entire spreadsheet really.

I've managed to get the button to call the form correctly and the cancel button working, which isn't much, but connecting the input boxes with the corresponding columns I don't have a clue how to proceed :(

p45cal
05-13-2016, 07:25 AM
Is this an exercise to get the hang of userforms or are you simply trying to find certain rows in the sheet?
I ask because this can be quickest done by Autofiltering the whole table.

p45cal
05-13-2016, 07:41 AM
cross posted: http://www.mrexcel.com/forum/excel-questions/941036-creating-form-filter-data.html

Smeghead
05-13-2016, 08:34 AM
Is this an exercise to get the hang of userforms or are you simply trying to find certain rows in the sheet? I ask because this can be quickest done by Autofiltering the whole table. A little bit of both. It would be useful to learn about userforms, but also because of the way the things I want to query are all separate columns, if I autofilter, I have to click the filter arrow, type in what I want, use my mouse to click the filter arrow on the next column etc. etc., whereas with this form, I can just type it in, tab, type it in, tab, etc. and get the search done a lot quicker. I have search for stuff many times per hour, so it would just speed things up a lot in general.

p45cal
05-13-2016, 08:51 AM
OK, then supply a simple file here and I'll try and make dmt32's idea at MrExcel work in it.

ps. it would be a good idea to provide, at each site where you've cross-posted, links to everywhere else you have cross posted this topic to. It's a rule at most forums. while I'm a moderator at 3 or 4 other forums, I'm not a moderator here, so I can't (mustn't even) tell you what to do.

Smeghead
05-13-2016, 09:25 AM
Here we are. Thanks so much for taking the time to help with this, and sorry about the cross posting.

p45cal
05-13-2016, 10:02 AM
See attached.

Smeghead
05-13-2016, 10:46 AM
That is brilliant! I decided I didn't need it to reset the results whenever the filter window is closed, so I deleted the "UserForm_QueryClose" sub, but other than that it worked exactly as required. Again, many thanks.

Smeghead
05-16-2016, 01:24 AM
Sorry to be a bother again. I've gone back into work and implemented it into the proper spreadsheet. While the filter works exactly as planned, there's an undesired side effect when trying to refresh the data.

Since when I have the filter dialog open, I can't press anything in Excel other than in the dialog, I added a refresh button. However if I press it, it takes far far longer to refresh the data than it normally would when just pressing Excel's refresh button (Excel shows it's running a background query in the bottom left), and if I press the close button (either my button one or the red X), it'll tell me that "No cells were found", and the filter breaks.

Is there any way to have it so that once I've filtered what I need, I can refresh with my filters "as is", as though I had just done an autofilter and refreshed? Here is the code for the userform that I'm using:



Dim ws As Worksheet
'///
Private Sub close_button_Click()
Unload Me
End Sub
'///
Private Sub refresh_button_Click()
ActiveWorkbook.RefreshAll
End Sub
'///
Private Sub reset_button_Click()
With ActiveSheet
If .FilterMode Then
.ShowAllData
End If
End With
ActiveWorkbook.RefreshAll
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet1").QueryTables(1).Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").QueryTables(1).Sort.SortFields.Add Key:= _
Range("E2:E20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").QueryTables(1).Sort.SortFields.Add Key:= _
Range("F2:F20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").QueryTables(1).Sort.SortFields.Add Key:= _
Range("G2:G20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").QueryTables(1).Sort.SortFields.Add Key:= _
Range("I2:I20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").QueryTables(1).Sort.SortFields.Add Key:= _
Range("H2:H20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").QueryTables(1).Sort.SortFields.Add Key:= _
Range("A2:A20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").QueryTables(1).Sort
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
'///
Private Sub search_button_Click()
Dim FilterValues As Variant
Dim matchcount As Long
Dim rng As Range
Set rng = ws.Range("A1").CurrentRegion
FilterValues = Array(Me.style1_box.Text, Me.style2_box.Text, Me.style3_box.Text, Me.fit_box.Text, Me.colour_box.Text)
ws.Cells(2, ws.columns.Count - 4).Resize(1, 5).Value = FilterValues
rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=ws.Cells(1, ws.columns.Count - 4).Resize(2, 5), Unique:=False
matchcount = rng.columns(1).SpecialCells(xlCellTypeVisible).Count - 1
msg = IIf(matchcount > 0, matchcount & " Matches Found", "No Matches Found")
MsgBox msg, 48, "Search"
End Sub
'///
Private Sub UserForm_Initialize()
Set ws = Worksheets("Sheet1")
'add filter headings
ws.Cells(1, ws.columns.Count - 4).Resize(1, 5).Value = ws.Range("E1:I1").Value
End Sub

p45cal
05-16-2016, 02:35 AM
Since when I have the filter dialog open, I can't press anything in Excel other than in the dialog,The form's Showmodal property shold be set to False.





I added a refresh button. However if I press it, it takes far far longer to refresh the data than it normally would when just pressing Excel's refresh button (Excel shows it's running a background query in the bottom left), and if I press the close button (either my button one or the red X), it'll tell me that "No cells were found", and the filter breaks.Do you still need such a reset button if now you can operate in Excel while the userform/dialogue box is open?





Is there any way to have it so that once I've filtered what I need, I can refresh with my filters "as is", as though I had just done an autofilter and refreshed?Are you saying that if you do this ("had just done an autofilter and refreshed") the refreshed data remains properly Autofiltered (with Autofilter) without having to re-do the Autofilter? If so then perhaps the code could be re-written to use Autofilter instead of Advanced Filter. However, sticking with Advanced Filter, pressing the Search button again should refresh the filter, and of course, the clicking of that search button could be automated at the end of the refresh code.

ps. I see it's a querytable now rather than just a plain range which means there could be more robust ways to make sure the right range is being filtered rather than currentregion.

Smeghead
05-16-2016, 04:37 AM
The form's Showmodal property shold be set to False.

Do you still need such a reset button if now you can operate in Excel while the userform/dialogue box is open?

Ah I see, in that case no, I can get rid of the refresh button.


Are you saying that if you do this ("had just done an autofilter and refreshed") the refreshed data remains properly Autofiltered (with Autofilter) without having to re-do the Autofilter? If so then perhaps the code could be re-written to use Autofilter instead of Advanced Filter.

Yes. Before I tried to creating this form, I just enabled filtering on the columns, filter down each column one by one and then as needed, refresh the database. A few of the columns' data would update/change (e.g. DespID), but if I pressed the refresh button with the filters active, it would simply update the data in the DespID cells without changing the filters or anything.

When I added in the refresh when clicking the search button, it would still come up with the "No cells were found". I think this may be because it's trying to filter while the refresh is still occuring or something, because as a workaround, I've been clearing any/all filters, refreshing then sorting the data (those 3 actions are as their own macro), and once that has all finished, I search and the right results come up, together with the correctly updated cells. Because of this, I tried adding in a time wait after the search button refresh to try and make sure it's finished first (and experimented with "DoEvents"), but it still comes up with the error. I hope that makes sense.

Is it difficult to alter to use Autofilter rather than Advanced Filter to try it out?

snb
05-16-2016, 04:38 AM
I'd suggest quite another approach:

p45cal
05-17-2016, 10:06 AM
Something like the attached (no code required) will give you a lot of flexibility in the quickest time; a pivot table with slicers.
The fields can be in any order.
You can use combinations of the Shift, Ctrl and mouse-click to select exactly what you want from each slicer.
Update the data and refresh the pivot should be all you need to do to update existing filters and this could be coded for.

Smeghead
05-18-2016, 03:13 AM
Never used slice tables before, but they certainly are intruiging. Thanks for the idea.

I'm going to have a play around. Thanks so much for all the suggestions everyone.