how to search Individual column (text inputs) in excel

[Izhar]

Hello folks,

I have a huge database that i have to constantly search for different sort of information arranged in many columns.

With multiple filters i am able to get to the information i need (the result in multiple rows)

However i am able to get to my desired information much faster and easier if use the text box that appears in the drop down filter option

I found the below functionality on the internet

https://datatables.net/examples/api/multi_filter.html

I was wondering if the same model can be replicated in excel using ActiveX text boxes

Please do share as much information as you can.

Thanks,

 

[SirJB7]

Hi, Izhar!
Give a look at the uploaded file. It uses cells instead of text boxes, filter expressions should be put in full condition format (carlsberg for carlsberg, *carlsberg for ending in carlsberg, carlsberg* for starting with carlsberg, *carlsberg* for containing carlsberg... <1000 for less than 1000... and so on).
This is the code for the worksheet object module:

Option Explicit

Private Sub cmdFilter_Click()
    ' constants
    Const ksOp = " < = >"
    ' declarations
    Dim sOp() As String
    Dim I As Long, J As Long, K As Integer, A As String, B As String, V As Variant
    ' start
    cmdShowAll_Click
    sOp = Split(ksOp)
    ' process
    With ActiveSheet
        I = .Cells(.Rows.Count).End(xlUp).Row + 1
        J = .[A2].End(xlToRight).Column
        A = .Cells(2, 1).Address & ":" & .Cells(I, J).Address
        For K = 1 To J
            V = .Cells(1, K).Value
            If Len(V) > 0 Then
                .Range(A).AutoFilter Field:=K, Criteria1:=V, Operator:=xlAnd
            End If
        Next K
    End With
    ' end
    ActiveSheet.Cells(1, J + 1).Select
    Beep
End Sub

Private Sub cmdShowAll_Click()
    With ActiveSheet
        .Cells(2, 1).Select
        If .AutoFilter.FilterMode Then .ShowAllData
    End With
End Sub

Regards!

 

[Izhar]

Hi, Izhar!
Give a look at the uploaded file. It uses cells instead of text boxes, filter expressions should be put in full condition format (carlsberg for carlsberg, *carlsberg for ending in carlsberg, carlsberg* for starting with carlsberg, *carlsberg* for containing carlsberg... <1000 for less than 1000... and so on).
This is the code for the worksheet object module:

Option Explicit

Private Sub cmdFilter_Click()
    ' constants
    Const ksOp = " < = >"
    ' declarations
    Dim sOp() As String
    Dim I As Long, J As Long, K As Integer, A As String, B As String, V As Variant
    ' start
    cmdShowAll_Click
    sOp = Split(ksOp)
    ' process
    With ActiveSheet
        I = .Cells(.Rows.Count).End(xlUp).Row + 1
        J = .[A2].End(xlToRight).Column
        A = .Cells(2, 1).Address & ":" & .Cells(I, J).Address
        For K = 1 To J
            V = .Cells(1, K).Value
            If Len(V) > 0 Then
                .Range(A).AutoFilter Field:=K, Criteria1:=V, Operator:=xlAnd
            End If
        Next K
    End With
    ' end
    ActiveSheet.Cells(1, J + 1).Select
    Beep
End Sub

Private Sub cmdShowAll_Click()
    With ActiveSheet
        .Cells(2, 1).Select
        If .AutoFilter.FilterMode Then .ShowAllData
    End With
End Sub

Regards!

-------------------------------------------------------------------------------------
Thank SirJB7 for the solution; i am a step closer to what i am ultimately looking for.


want to further explore this, is there a possibility to modify this vba to do away with the filter and show all buttons and instead it shows all when no condition is typed and with the * before and * after conditions the results filter as the user types in the cells or presses enter after typing what he is looking for.


Please do give it a go.


Thanks,

Izhar

 

[SirJB7]

Hi, Izhar!
Check the updated file, code added for worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' constants
    Const ksOp = " < = >"
    Const ksRng = "FilterList"
    ' declarations
    Dim sOp() As String
    Dim rng As Range
    Dim I As Long, J As Long, K As Integer, A As String, B As String, V As Variant
    ' start
    Set rng = ActiveSheet.Range(ksRng)
    With Target
        If Application.Intersect(Target, rng) Is Nothing Then
            Exit Sub
        ElseIf .Cells.Count > 1 Then
            Exit Sub
        End If
    End With
    Application.EnableEvents = False
    sOp = Split(ksOp)
    ' process
    With ActiveSheet
        I = .Cells(.Rows.Count).End(xlUp).Row + 1
        J = .[A2].End(xlToRight).Column
        A = .Cells(2, 1).Address & ":" & .Cells(I, J).Address
        K = Target.Column
        V = .Cells(1, K).Value
        If Len(V) > 0 Then
            .Range(A).AutoFilter Field:=K, Criteria1:=V, Operator:=xlAnd
        End If
    End With
    ' end
    Application.EnableEvents = True
    Set rng = Nothing
    ActiveSheet.Cells(1, J + 1).Select
    Beep
End Sub

Buttons still work for manual reset of all values or for copy/paste of parameters sets. Worksheet change detection is restricted to single cell modifications.
Regards!

 

[Izhar]

Dear SirJB7,

thank you so much for doing this i really appreciate it.

i was wondering if you could guide me to a resourse for me to learn the vba coding and learn to perform these funtions on my own.

thanks again,
Izhar

 

[Izhar]

Dear SIRJB7, me being completly novocie to vba have not figured out how to break down the given code to accomodate on my file, i was wondering if you could do the same for the attached file and if you could explain a little on range selection, and selection of columns it will be very helpful. want the filter to work on all columns. i should have uploaded the sample file in my first post.

but i guess i over estimated my skills in understanding the vba code.

please do help

Thanks,
Izhar

 

[SirJB7]

Hi, Izhar!

You've introduced slight changes that have high impact in the code behaviour.
The updated uploaded file corrects all them:
a) your worksheet data starts at row 3 and not at row 2
b) there's missing the named range "FilterList" which corresponds to parameters
c) you've removed the 2 command buttons
d) you've removed the CF that turned yellow the cells at filter list that contains data (only visual)
d) you've removed "Option Explicit" clause
e) you've added new unused variables (P, R, S...) and ended "Dim" instruction with comma
f) you've removed the filter option for all columns

Please do not alter the provided file which is now working. If you want to introduce new code, do a backup, try your code, then if it doesn't work restore the backup and lose your changes.

There's no way you can update/modify a code like this if you're a complete novice at VBA, sorry to say but you've yet learnt that it's true.

I tried to add as much as comments as possible to indicate what is each part of the code intended for. Hope it helps. However you can always access to the built-in help positioning the cursor on any word of the code and pressing F1 (help).

About learning VBA please do a search at this site, there have been many posts about where to start from.

Regards!

 

[Izhar]

Dear SirJB7, thank you for your help i really appreciate it.

 

[SirJB7]

Hi, Izhar!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards