filtering in a form, data validation dependent lists

[bazbuyer]

Hi
I have a rules database 4 columns, circa 500 rows(all cells populated). Column D has ALL unique values(the rules) Column A 37 unique values,B and C similar(the descriptors). If you want to find a rule in column D you use A-C to narrow down the search. Easy with filters, but I want to build it into a form where the database is not seen, just 4 cells 3 descriptors that narrow down the search to find the final rule. I could use dependent tables with indirect functions etc, but that would require setting up multiple linked table, could be done but would take ages and would manually need to be changed if the linked database changed. e.g select one of the 37 unique values from A may then have 15 options in B, select the most appropriate from that and there's say 6 options in C, select one of these that and chose from a final 3 to get your end unique value. I'm sure there must be an easy option given its really just a filter without a 5 metre long formula? Any ideas, thanks

 

[DanteAmor]

I propose the following Userform, with 3 textbox, one for each column A, B and C; a listbox to see the results of the filter and commandbutton to get the rule.


In this way you can write any word in any textbox and the code will perform the search and in the listbox it will show you the matches.

Eg:

This is the code in userform:

Private Sub TextBox1_Change()
    Call filtering
End Sub
Private Sub TextBox2_Change()
    Call filtering
End Sub
Private Sub TextBox3_Change()
    Call filtering
End Sub


Sub filtering()
    Dim sh As Worksheet, lr As Long
    
    Set sh = Sheets("[COLOR=#0000cd]DataBase[/COLOR]")
    sh.Range("J1").Resize(1, 4).Value = sh.Range("A1").Resize(1, 4).Value
    sh.[J2] = "*" & TextBox1.Value & "*"
    sh.[K2] = "*" & TextBox2.Value & "*"
    sh.[l2] = "*" & TextBox3.Value & "*"
    lr = sh.Range("A" & Rows.Count).End(xlUp).Row
    sh.Range("A1:D" & lr).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=sh.Range("J1:M2"), CopyToRange:=sh.Range("R1:U1"), Unique:=False
    
    lr = sh.Range("R" & Rows.Count).End(xlUp).Row
    ListBox1.RowSource = sh.Name & "!" & sh.Range("R2:U" & lr).Address
End Sub


Private Sub UserForm_Activate()
    ListBox1.ColumnCount = 4
    ListBox1.ColumnHeads = True
End Sub

The "database" sheet may be hidden.
The filters are made in the "database" sheet but they can be done in another temporary sheet.

Try to recreate the example on a sheet and comment if it is what you need.

 

[bazbuyer]

Hi

Firstly thanks for your help, this looks good, though am getting a runtime424 error object required on the row s.[J2] = "*" & TextBox1.Value & "*" . I'm running excel 2016

I propose the following Userform, with 3 textbox, one for each column A, B and C; a listbox to see the results of the filter and commandbutton to get the rule.


In this way you can write any word in any textbox and the code will perform the search and in the listbox it will show you the matches.

Eg:

This is the code in userform:

Private Sub TextBox1_Change()
    Call filtering
End Sub
Private Sub TextBox2_Change()
    Call filtering
End Sub
Private Sub TextBox3_Change()
    Call filtering
End Sub


Sub filtering()
    Dim sh As Worksheet, lr As Long
    
    Set sh = Sheets("[COLOR=#0000cd]DataBase[/COLOR]")
    sh.Range("J1").Resize(1, 4).Value = sh.Range("A1").Resize(1, 4).Value
    sh.[J2] = "*" & TextBox1.Value & "*"
    sh.[K2] = "*" & TextBox2.Value & "*"
    sh.[l2] = "*" & TextBox3.Value & "*"
    lr = sh.Range("A" & Rows.Count).End(xlUp).Row
    sh.Range("A1:D" & lr).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=sh.Range("J1:M2"), CopyToRange:=sh.Range("R1:U1"), Unique:=False
    
    lr = sh.Range("R" & Rows.Count).End(xlUp).Row
    ListBox1.RowSource = sh.Name & "!" & sh.Range("R2:U" & lr).Address
End Sub


Private Sub UserForm_Activate()
    ListBox1.ColumnCount = 4
    ListBox1.ColumnHeads = True
End Sub

The "database" sheet may be hidden.
The filters are made in the "database" sheet but they can be done in another temporary sheet.

Try to recreate the example on a sheet and comment if it is what you need.

 

[DanteAmor]

Hi

Firstly thanks for your help, this looks good, though am getting a runtime424 error object required on the row s.[J2] = "*" & TextBox1.Value & "*" . I'm running excel 2016

must be

[B][COLOR=#ff0000]sh[/COLOR][/B][COLOR=#333333].[J2][/COLOR]

Do not modify the macro, it works

 

[bazbuyer]

Hi
I've not touched the code the missing h was just me typing it into to the thread, it wouldn't let me paste it in. I'm not overly familiar with VBA, inserted a module and then pasted the code in, saved and run macro. The headers come up, but not the form

Many thanks

must be

[B][COLOR=#ff0000]sh[/COLOR][/B][COLOR=#333333].[J2][/COLOR]

Do not modify the macro, it works

 

[DanteAmor]

The code goes inside the userform.
I attach my evidence file for you to review.

https://www.dropbox.com/s/rdhk3x45wma2v5g/userform rules.xlsm?dl=0

 

[bazbuyer]

The code goes inside the userform.
I attach my evidence file for you to review.

https://www.dropbox.com/s/rdhk3x45wma2v5g/userform rules.xlsm?dl=0

Ok, thanks, I'll get a dropbox account and check it out, I don't doubt that it works just that I'm doing something wrong, thanks again for your help

 

[DanteAmor]

Let me know if you have any doubt