none
VBA way to bring up dialogue box for user to enter filtering text that then filters a specific column?

    Question

  • I don't know if the text filters feature of filtering data in a particular column is exposed to VBA, but if it is, what would it be.  It's a pain for the user to have to keep clicking

    FILTER ARROW > TEXT FILTERS ... > CONTAINS

    It would be ideal if a macro could just bring up the criteria box that I could then assigns keystrokes to operate and then the user could type in the, say, company name (etc.) and hit enter and the specific column (which would be column F for the company name) would just show the entries that have that company name.

    Is there any sort of user dialogue box possible?

    Thank you!

    Tuesday, June 11, 2013 6:18 PM

Answers

  • Do you have an existing filter applied to the sheet? If so, then you need to adjust the field number for the filter that is in place already - for example, if the filter is on columns A to J, then B is field 2:

    Range("B:B").AutoFilter Field:=2, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"


    Tuesday, June 25, 2013 11:51 AM
  • Do you have an existing filter applied to the sheet? If so, then you need to adjust the field number for the filter that is in place already - for example, if the filter is on columns A to J, then B is field 2:

    Range("B:B").AutoFilter Field:=2, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"

    Man, what a pain this was <lol>.  And it was _all_ my fault.  It's a question of not understanding the terminology completely.  I was always answering, "no, I'm only filtering one column" and was perplexed.  But today, I had a dimly lit bulb go off tentatively in a possible reason why.  To test, I went and recorded the keystrokes to filter this column and ended up with Criteria5!!  Well, I never had figured out _that_ part in filtering before even though I have lots of prior vb filtering experience in the much easier XL2003 sorting code.

    So I've been misinterpreting the meaning of filtering.  Until this, I always thought of it as the part where I'm actively filtering out by a string, etc., not whether or not there were pulldown arrows available <lol>.  I have filter arrows on columns B, C, D, E and F which I now know have to be taken into account whether or not I was using them in this macro.  So, got it now.  Thx.

    Here's the macro now.  It's close to finished and it's saved me a TON of time in last hour that I've been using and testing it out today:

    Sub Filter_COMPANY_Column()
        
        On Error Resume Next
        ActiveSheet.ShowAllData
    '    If Err.Number <> 0 Then MsgBox "All the data is being displayed.", vbExclamation, "Please note ..."
        On Error GoTo 0
    
        Range("B:B").AutoFilter Field:=5, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"
        Range("E1").Select
    '    ActiveCell.Offset(1, 0).Select     ' go down 1 cell(s)
    End Sub

    By adding the .ShowAllData, I don't even have to clear the filter, this macro will clear it and bring up the box again for a new search.

    The only part that isn't smooth is navigating to the top row after applying the filter.  Using the Range("E1").Select brings me up to the top header cell perfectly but when I use the ActiveCell.Offset, unlike when one moves the arrow manually, programatically that does navigate down one cell but down into the hidden rows and not the visible ones as is needed.  Is there any way to programatically move down one cell but into the first visible row instead of the first invisible one?

    Thx!  Once that's done, that's about as perfect as it can get!

    Wednesday, June 26, 2013 9:32 PM

All replies

  • Range("F:F").AutoFilter Field:=1, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"


    Tuesday, June 11, 2013 7:35 PM
  • Range("F:F").AutoFilter Field:=1, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"

    Hello!

    Thank you so very much for your response; much appreciated.

    Something I ran into yesterday, though, that I'd like to know.  It seems that Range("F:F") must not be the way to select a column as my trial yesterday didn't work either using that method.  Or perhaps because I'm testing here at home where I have XL03 yet the above will work on XL10 like at work??

    In any event, no matter what I put in the range, column A gets filtered - i.e., Range("F:F") , Range("G:G"), all filtered column A when I was hoping for F in the former and G in the latter.

    Is there a better way to define a specific column?  I am testing on a spreadsheet that has this type of data in column B:

    White_Paper
    Product_Information
    Technical_Briefs
    Product_Information
    Product_Guides
    Demo
    Datasheet
    Product_Information
    Product_Guides
    Product_Guides
    Product_Guides
    Product_Guides
    Product_Guides
    Product_Guides
    Product_Guides
    Product_Guides
    Product_Guides
    Product_Guides
    Analyst_Report
    Analyst_Report
    Analyst_Report
    Analyst_Report
    Analyst_Report
    Analyst_Report
    Analyst_Report

    And I was trying to filter for the rows that contained *product* in that column so tried Range("B:B") but only got nil on column A.

    I'm assuming it's taking the user input correctly even though I can't see the end result as positive.

    What do you think re a better way to specify a column?



    • Edited by SGFan Sunday, June 23, 2013 3:56 PM missed word
    Sunday, June 23, 2013 3:54 PM
  • When you filter your sheet based on one column, all columns effectively have the same view applied. For example, when you filter column B for "product", then some rows across all columns will be hidden - those where column B does not contain "product"

    Range("B:B").AutoFilter Field:=1, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"

    If you want to filter based on two columns then your need to include both columns in your initial range

    Range("B:F").AutoFilter Field:=1, Criteria1:="=*" & InputBox("B filter?") & "*"
    Range("B:F").AutoFilter Field:=5, Criteria1:="=*" & InputBox("F filter?") & "*"

    and note that the field is the relative column position for the filtered range.

    Monday, June 24, 2013 4:01 PM
  • When you filter your sheet based on one column, all columns effectively have the same view applied. For example, when you filter column B for "product", then some rows across all columns will be hidden - those where column B does not contain "product"

    Range("B:B").AutoFilter Field:=1, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"

    This is still nuts.  Thanks for your response but I just can't get this to work here at home, which has me stumped.  I'm going to take this test sheet to work to try this macro out with XL10.  If this is supposed to work then I have to assume it's the difference in versions that's at fault.  When I manually go to the filter on column B and select "Product_Guides", then the filtering is applied correctly and only the rows with that string appear (11 rows in total) and the pulldown arrow is blue on column B, as it should be.  Okay.

    When I use the macro and even when I put the exact string "Product_Guides", the pulldown on column A (what??!!) is blue (not column B as it should be) and NO rows appear.  Everything is filtered out.  No rows are visible!  So the macro effectively just does not work here at home with XL03.

    But like I said, I'll try at work where it's XL10.  Perhaps in this case, the macro language is very specific to the version #.

    Thank you!



    • Edited by SGFan Tuesday, June 25, 2013 8:04 AM missed wording
    Tuesday, June 25, 2013 8:02 AM
  • Do you have an existing filter applied to the sheet? If so, then you need to adjust the field number for the filter that is in place already - for example, if the filter is on columns A to J, then B is field 2:

    Range("B:B").AutoFilter Field:=2, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"


    Tuesday, June 25, 2013 11:51 AM
  • Do you have an existing filter applied to the sheet? If so, then you need to adjust the field number for the filter that is in place already - for example, if the filter is on columns A to J, then B is field 2:

    Range("B:B").AutoFilter Field:=2, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"

    Man, what a pain this was <lol>.  And it was _all_ my fault.  It's a question of not understanding the terminology completely.  I was always answering, "no, I'm only filtering one column" and was perplexed.  But today, I had a dimly lit bulb go off tentatively in a possible reason why.  To test, I went and recorded the keystrokes to filter this column and ended up with Criteria5!!  Well, I never had figured out _that_ part in filtering before even though I have lots of prior vb filtering experience in the much easier XL2003 sorting code.

    So I've been misinterpreting the meaning of filtering.  Until this, I always thought of it as the part where I'm actively filtering out by a string, etc., not whether or not there were pulldown arrows available <lol>.  I have filter arrows on columns B, C, D, E and F which I now know have to be taken into account whether or not I was using them in this macro.  So, got it now.  Thx.

    Here's the macro now.  It's close to finished and it's saved me a TON of time in last hour that I've been using and testing it out today:

    Sub Filter_COMPANY_Column()
        
        On Error Resume Next
        ActiveSheet.ShowAllData
    '    If Err.Number <> 0 Then MsgBox "All the data is being displayed.", vbExclamation, "Please note ..."
        On Error GoTo 0
    
        Range("B:B").AutoFilter Field:=5, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"
        Range("E1").Select
    '    ActiveCell.Offset(1, 0).Select     ' go down 1 cell(s)
    End Sub

    By adding the .ShowAllData, I don't even have to clear the filter, this macro will clear it and bring up the box again for a new search.

    The only part that isn't smooth is navigating to the top row after applying the filter.  Using the Range("E1").Select brings me up to the top header cell perfectly but when I use the ActiveCell.Offset, unlike when one moves the arrow manually, programatically that does navigate down one cell but down into the hidden rows and not the visible ones as is needed.  Is there any way to programatically move down one cell but into the first visible row instead of the first invisible one?

    Thx!  Once that's done, that's about as perfect as it can get!

    Wednesday, June 26, 2013 9:32 PM
  • Sorry, I posted a reply to this yesterday but it did not go through....

        Dim rngV As Range
        Set rngV = Range("E:E").SpecialCells(xlCellTypeVisible)

        If rngV.Areas(1).Cells.Count > 1 Then
            Range("E2").Select
        Else
            rngV.Areas(2).Cells(1).Select
        End If

    Friday, June 28, 2013 3:28 PM
  • Thanks for everyone's help!

    I have this code that I've been working with for last few days to get a feel for if anything is missing:

    Sub SEARCH_Bring_Up_COMPANY_Column_as_per_user_filter()
        
        On Error Resume Next
        ActiveSheet.ShowAllData
        'If Err.Number <> 0 Then MsgBox "All the data is being displayed.", vbExclamation, "Please note ..."
        On Error GoTo 0     ' on error GoTo 0 will bring up the debug box
    
    'If Cancel = True Then
    '    Exit Sub
    'ElseIf Cancel = False Then
    '    Range("F:F").AutoFilter Field:=5, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"
    '
    '    With ActiveSheet.AutoFilter.Range
    '        .SpecialCells(xlCellTypeVisible).Areas(2)(1, 5).Select     ' Areas(2) (1,5).Select - the 5 in (1,5) stands for column F / Col A=1, Col B=2, Col C=3, Col D=4, Col E=5.
    '    End With
    'End If
    
        Range("F:F").AutoFilter field:=5, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"
    '----- Puts cursor down into first visible row after filtering: -----------
        With ActiveSheet.AutoFilter.Range
            .SpecialCells(xlCellTypeVisible).Areas(2)(1, 5).Select     ' Areas(2) (1,5).Select - the 5 in (1,5) stands for column F / Col A=1, Col B=2, Col C=3, Col D=4, Col E=5.
        End With
    '--------------------------------------------------------------------------
    End Sub
    

    Sorry for commented out lines but I did find 2 issues with this macro.  Because I chose "0" for on error, I get the debug error popup when 2 conditions happen:

    - when I (the user) presses CANCEL

    - of if I type in a company name incorrectly so it's as if the company wasn't in the sheet

    So on cancel or on typo, is there a way to exit out of the above?  I tried with my attempt above in the commented out area, but was not successful.

    Thank you for any further help.  I think that with those 2 types of errors captured, this macro will be perfect to go!

    Cheers.

    Wednesday, July 10, 2013 3:34 PM
  • Sub SEARCH_Bring_Up_COMPANY_Column_as_per_user_filter()
        Dim strSearch As String

        On Error Resume Next
        If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData
        
        On Error GoTo 0     ' on error GoTo 0 will bring up the debug box

    GetFilter:

        strSearch = InputBox("Sub-string to filter for?")

        If strSearch = "" Then
            MsgBox "You cancelled."
            Exit Sub
        End If

        If IsError(Application.Match(strSearch, Range("F:F"), False)) Then
            MsgBox """" & strSearch & """ is not a valid value. Try again."
            GoTo GetFilter
        End If


        Range("B:F").AutoFilter field:=5, Criteria1:="=*" & strSearch & "*"
        '----- Puts cursor down into first visible row after filtering: -----------
        With ActiveSheet.AutoFilter.Range
            If .SpecialCells(xlCellTypeVisible).Areas(1).Rows.Count > 1 Then
                .SpecialCells(xlCellTypeVisible).Areas(1)(2, 5).Select
            Else
                .SpecialCells(xlCellTypeVisible).Areas(2)(1, 5).Select
            End If
        End With
        '--------------------------------------------------------------------------
    End Sub
    Wednesday, July 10, 2013 4:56 PM
  • Sub SEARCH_Bring_Up_COMPANY_Column_as_per_user_filter()
        Dim strSearch As String

        On Error Resume Next
        If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData

        On Error GoTo 0     ' on error GoTo 0 will bring up the debug box

    GetFilter:

    [snip]    

    Good Afternoon,

    Thank you for your response!

    Well, don't know what is happening but now the filtering doesn't work.  And I don't recognize where trouble area is as there is some  new code (i.e., Range("B:F").AutoFilter field:=5, Criteria1:="=*" & strSearch & "*" ... Range B:F??)

    It does recognize cancel, though, so all is not lost <g>, it's just that now I can't get anything to be found.

    I'm useless with this sort of thing.  The inputbox format above is different from anything I've found on the net so don't know how to put a cancel and trap misspellings <sigh>.  That's all it really needs not to change anything else, really, no?

    What can be done?



    • Edited by SGFan Wednesday, July 10, 2013 9:59 PM punctuation
    Wednesday, July 10, 2013 9:58 PM
  • Oh, got it!  I did some more searching and some more reading and found something that works for me temporarily for this case:

    2013-07-10 18:19:57 (http://www.techrepublic.com/blog/five-apps/five-tips-for-handling-errors-in-vba/339)
    
    
    To ignore an error, precede the statement with the Resume Next statement, as follows:
    
    On Error Resume Next
    
    This statement allows the program to continue to the next line of code, totally ignoring the error. In short, Resume Next disables error handling from that line forward (within the procedure). That?s the easy part, but you?re not done. Anytime you use Resume Next, you need to reset error handling by using the following statement:
    
    On Error GoTo 0
    
    GoTo 0 disables enabled error handling in the current procedure and resets it to Nothing ? that?s the technical explanation. In a nutshell, Resume Next skips an error and GoTo 0 tells the debugger to stop skipping errors.
    
    Be sure to insert the GoTo 0 statement as early as possible. You don?t want to mask other errors.

    This was excellent for me and will work for now until a solution to close box when cancelled or to let user know when company doesn't exist, since I like the least amount of error messages popping up to occur.  So here is modified code and it'll do for now:


    Sub SEARCH_Bring_Up_COMPANY_as_per_user_filter()

        
        On Error Resume Next  ' put On "Error GoTo 0" statement as soon as you can after this
        ActiveSheet.ShowAllData
        'If Err.Number <> 0 Then MsgBox "All the data is being displayed.", vbExclamation, "Please note ..."
        On Error GoTo 0

        On Error Resume Next  ' put On "Error GoTo 0" statement as soon as you can after this
        Range("F:F").AutoFilter field:=5, Criteria1:="=*" & InputBox("Sub-string to filter for?") & "*"
    '----- Puts cursor down into first visible row after filtering: -----------
        With ActiveSheet.AutoFilter.Range
            .SpecialCells(xlCellTypeVisible).Areas(2)(1, 5).Select     ' Areas(2) (1,5).Select - the 5 in (1,5) stands for column F / Col A=1, Col B=2, Col C=3, Col D=4, Col E=5.
        End With
    '--------------------------------------------------------------------------
        On Error GoTo 0
    End Sub

    If there's a typo, it's like the company isn't there and no rows show.  If there's a cancel, the box just disappears.  Not ideal as the user doesn't get any info, but will do for now.  So for other users like me, what I've learned is the valuable info from the above page referenced on using "Resume Next" to skip over an error but to folllow that by the "0" one as soon as possible after; I imagine embracing the very next step in a macro, as I have done here above by putting "On Error Resume Next" before filtering inputbox and after-filter navigation, immediately followed by "On Error GoTo 0".



    • Edited by SGFan Wednesday, July 10, 2013 10:27 PM wording
    Wednesday, July 10, 2013 10:23 PM
  • The B:F is specified as the filter range because you posted code that showed the filtered column F to be field 5, so that means B, C, D, E, F  -  F is 5th - and your filters must be on B to F at least. Try running the macro with no filters applied first - working with existing filtering can be a mess.

    But I missed the check for existence of the sub-string - try this one:

    Sub SEARCH_Bring_Up_COMPANY_Column_as_per_user_filter()
        Dim strSearch As String

        On Error Resume Next
        If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData
        
        On Error GoTo 0     ' on error GoTo 0 will bring up the debug box

    GetFilter:

        strSearch = InputBox("Sub-string to filter for?")

        If strSearch = "" Then
            MsgBox "You cancelled."
            Exit Sub
        End If

        If Application.CountIf(Range("F:F"), "*" & strSearch & "*") = 0 Then
            MsgBox """" & strSearch & """ is not a valid value. Try again."
            GoTo GetFilter
        End If


        Range("B:F").AutoFilter field:=5, Criteria1:="=*" & strSearch & "*"
        '----- Puts cursor down into first visible row after filtering: -----------
        With ActiveSheet.AutoFilter.Range
            If .SpecialCells(xlCellTypeVisible).Areas(1).Rows.Count > 1 Then
                .SpecialCells(xlCellTypeVisible).Areas(1)(2, 5).Select
            Else
                .SpecialCells(xlCellTypeVisible).Areas(2)(1, 5).Select
            End If
        End With
        '--------------------------------------------------------------------------
    End Sub

    Wednesday, July 10, 2013 11:14 PM