Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Need help with text strings search code

    Hi Loungers,

    I'm after help with some VBA code to search a column for text that match a search criteria in a column (L).
    I've tried to record a macro to activate the filter but that does not appear to work (at least the I did it)

    I'm thinking that a search message box would appear where the user would enter the text that they are searching for.

    In a cell say A1 a value could show the number of matches - ie 6 matches found to (what the search text is) and the rows that contain the matching text would be highlighted.

    Then a clear box to return to normal.

    I hope this makes sense - any help would be appreciated.

    Regards

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Instead of code, adding a filter and filtering on the text would be so much simpler...
    Even creating a conditional format to search and highlight would be simpler.

    These would not be required to keep track of previous formatting when the reset is done.

    Steve

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Verada,
    In the attached worksheet, enter your search criteria into the cell where indicated and it will filter the column as you type. One line of code does it all:

    Code:
    Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        ActiveSheet.UsedRange.AutoFilter Field:=7, Criteria1:="*" & TextBox1.Value & "*"
    End Sub
    HTH,
    Maud
    Attached Files Attached Files

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Steve Thanks for the reply, I agree, however the spread sheet will be used by a number of different people with different excel skill levels, so was trying to automate (to accommodate the lowest common denominator) the search processes.

    Regards

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Maud - thanks again for your help. The code looks to be just what I need.

    However, not sure how you set up the search field in G1 and actually how all the cells on Row 1 are set up

    Any tips would be greatly appreciated.

    Regards
    Last edited by verada; 2013-06-15 at 21:30. Reason: addition text

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In my experience, the autofilter is intuitive enough that even novice excel users can understand and work with it. I believe that using built-in features for these tasks is also prone to less failures in a workbook, than trying to create code for more routine operations.

    Steve

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Verada,
    If you click on the Developer tab then click on desisgn mode, You will notice that the blank cell in G1 is actually a textbox overlaying the cell in G1. This is the textbox that the code refers to. If you click on the cells A1 through F1, you will notice that there is a text box which overlays the cells A1 through F1 with typed in headers. The reason it is set up this way is to hide the unsightly filter icons. It essentially does what Steve is talking about but it does it through VB while avoiding filter dropdowns making it a no brainer to use

    If this meets your needs, setup your sheet in a similar fashion by placing the textbox over the header cell large enough not to cover the cell border, in the desired column. In the code, change the field number from 7 to your desired search column, and finally, overlay a textbox across the header and type in the hearders to line up.

    One line of takes care of the whole process. I have also done this from a pop up form in which a list box is populated with the values of the entire column. Underneath it is a search textbox which will filter the list box as you type.


    Good luck,
    Maud
    Last edited by Maudibe; 2013-06-16 at 19:25.

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Maud - Understood.

    Will see how I go using it in my spreadsheet.

    But looks as though is will be just fine

    Thanks again

    I've tested the code in my spreadsheet and it works as it should, however, is there some code to clear the search to reinstate the cells to allow entry of additional data. Thanks Again
    Last edited by verada; 2013-06-16 at 21:16. Reason: Additional querie

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Just backspace to clear the keyword. You will see the list of available matches grow as the keyword reduces in size (less filtering). When the search box is empty, you will be viewing the entire spreadsheet (Nothing filtered)

  10. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Maud - yes I understand how that, however when I back space (so no search criteria) there must be still some filtering happing as the row numbers are blue and there are no "Blank" cell showing.

    I'm thinking that there needs to be some code to replicate the "Select All" option in the filter. I do it manually from the filter menu and all the blanks appear.

    I hope that makes sense

    Thanks again for your help

    Regards

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Is what you are describing happening in the spreadsheet I sent you of in a spreadsheet that you have applied it to? When the search box is empty, look at the row numbers and they should all be present. Taking the place of the "Select all" option ist the "*" & TextBox1.Value & "*" which is saying that anything matches resulting in no filtering. I must conclude that you have applied this to you spreadsheet and you have blank cells in the filtered column.

    update:
    I have made some of the cells in the filter column blank, filtered the lines using a keyword, then backed out. The blank cells remain filtered. I now see what you are saying.

    This modification should resolve it and make the blank cells reappear when the search criteria is "":

    Code:
    Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If TextBox1.Value = "" Then
            ActiveSheet.UsedRange.AutoFilter Field:=7, Criteria1:=Null
        Else:
            ActiveSheet.UsedRange.AutoFilter Field:=7, Criteria1:="*" & TextBox1.Value & "*"
        End If
    End Sub
    Maud

  12. #12
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Perfect - thank you for your help again!

    Regards

  13. #13
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Lounges any thoughts on how to modify the code to work on a password protected worksheet?

    I image that the code would un-protect when clicking in the text box and entering the search string and re-protect when clicking outside the text box, but any other suggestions would be appreciated.

    Thanks for any assistance

    Regards

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Verada

    ..to allow AutoFilters to be used on a sheet that is protected, simply add this line to your code..

    ActiveSheet.EnableAutoFilter = True

    so, for example, use this:
    Code:
    Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
    ActiveSheet.EnableAutoFilter = True 
    
        If TextBox1.Value = "" Then
            ActiveSheet.UsedRange.AutoFilter Field:=7, Criteria1:=Null
        Else:
            ActiveSheet.UsedRange.AutoFilter Field:=7, Criteria1:="*" & TextBox1.Value & "*"
        End If
    End Sub
    zeddy

  15. The Following User Says Thank You to zeddy For This Useful Post:

    verada (2013-09-21)

  16. #15
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Zeddy - Thanks for your help again.

    I tied the code above, but still get an run time error '1004'': message - saying you can't use this command on a protected sheet........

    Any thoughts?

    Regards

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •