1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to search Individual column (text inputs) in excel

Discussion in 'Ask an Excel Question' started by Izhar, Aug 10, 2017.

  1. Izhar

    Izhar New Member

    Messages:
    26
    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,
  2. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,890
    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:
    Code (vb):
    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 likes this.
  3. Izhar

    Izhar New Member

    Messages:
    26
    -------------------------------------------------------------------------------------
    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
  4. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,890
    Hi, Izhar!
    Check the updated file, code added for worksheet change event:
    Code (vb):
    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!
    rahulshewale1 and Izhar like this.
  5. Izhar

    Izhar New Member

    Messages:
    26
    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
  6. Izhar

    Izhar New Member

    Messages:
    26
    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

    Attached Files:

  7. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,890
    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!

    Attached Files:

    Izhar likes this.
  8. Izhar

    Izhar New Member

    Messages:
    26
    Dear SirJB7, thank you for your help i really appreciate it.
  9. SirJB7

    SirJB7 Excel Rōnin

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

Share This Page