Find All VBA Form for Excel

The Find All Form for Excel allows you to type a search query in a text box and have the results appear in a list box.  The results are narrowed down as you type.  This is similar functionality to Google's auto fill or auto suggest feature when doing a Google search.  See the animated screen capture below.

Find All VBA Form
Find All results in a worksheet or workbook

This is a very basic application that mimics Excel's built-in Find All feature.  The code uses Chip Pearson's FindAll Function in combination with the KeyUp Event in the text box to return results in the list box as you type. A sample workbook with all the code is available for free download below.

Possible Uses

The features and capabilities can be greatly expanded to meet your needs.  I've used this in an add-in that runs searches on general ledger (GL) account codes.  The user can quickly search for account codes from the GL or database, and quickly insert them into the worksheet.  In my application, the tables containing the account codes are stored in the add-in workbook.  I've also developed additional processes to automatically update the GL tables as they are updated in the system.  This makes the searches extremely fast since the underlying source data is stored in the add-in.

What could you use this tool for?  Leave a comment below.

Download

Find All VBA Form.xls (464.9 KB)

Find All VBA Form - All Worksheets.xls (1.2 MB)

File Find All VBA Form (Results on Form).xls (474.6 KB)

Find All VBA Form - All Worksheets - Copy Paste.xls (547.3 KB)

The “All Sheets” file above searches all the sheets in the file.  The sheet name is included in column 2 of the results along with the cell address.  When an item is clicked in the results, the resulting sheet and cell are selected.

The “Copy Paste” file above pastes the clicked item in a list on a Results Sheet.  This allows you to track which items the user clicks on in the results list on the form.

151 comments

Your email address will not be published. Required fields are marked *

  • Hello, if someone wants to show complete row in list box of the searched word what would be the changing that needs to be done in your code.

  • Hello i am trying to run your macro but it keeps giving me an error compile error
    Sub or Function not defined at
    FoundCells = FindAllOnWorksheets(Nothing, Empty, SearchAddress:=strSearchAddress, _
    FindWhat:=FindWhat, _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    SearchOrder:=xlByColumns, _
    MatchCase:=False, _
    BeginsWith:=vbNullString, _
    EndsWith:=vbNullString, _
    BeginEndCompare:=vbTextCompare)
    Please help me as i am new at VBA

  • Hello,

    It is a very helpful code. Could I know how to do to make a search in only one column?

    Thanks in advance,
    Luis Romero

  • What a great piece of code !! I am searching a sheet with 11,500 lines and 33 columns, but the code was taking forever to find the results. I modified the minimum string length to 4 and it made all the difference in the world. I’ve also added extra columns in the results window, added some option buttons to the form and wrote a routine to put the results in the current worksheet. I’m currently trying to modify things to do a “double” search, matching 2 criteria – 1 static, 1 variable, but having a little trouble with it. Thank you for providing such a great resource !!

  • Hi there,

    Great Code. One quick question, how to I search only when I pressed the enter key?

    I tried replacing
    “If Len(f_FindAll.TextBox_Find.Value) > 1 Then”
    with

    “If KeyCode = vbKeyReturn Then Then”

    but it did not work.

    it’d be great if you could share your thoughts.

    Thanks,
    Ken

  • Hi,

    This has been very helpful, however the amount of data I’ve is pretty large and therefore the macro stucks…. Can you add a find button may be in between so that after entering the data which I’m lokking for I’ll hit it to see the results below

  • Hello Jon,
    Really like this macro and it works perfectly for inventory needs. I would like to add an “update” or “edit” button where my staff can add or minus stock as they remove it from the shelf or put it back.

    Thank you very much!!

  • Hi,
    i am using excel vba macro to combine multiple csv file with specific data string search.But i want to copy only header from one file only.pls suggest if any way is there.

  • Jon – I have a workbook with multiple sheets, of which 2 sheets are ‘database sheets’; meaning each of these 2 contains a structured table for data (eventually there will be 3 or 4 such ‘database sheets’). I also have other sheets that contain multiple tables that I may want to search. Subsequently I chose the “Find All VBA Form – All Worksheet.xls” version of your code to deploy.

    I’m having difficulty figuring out how to change the code to loop through listobjects rather than just worksheets and utilize structured table names, e.g. Range(“TableX”) to define the strSearchAddress variable for each structured table. Any ideas how this could be done?

    I adjusted your UserForm strSearchAddress code to account for using the usedrange from my 2 datasheets only, the structured table headers and total rows, and I changed the LookIn parameter to xlFormulas, and changed the “Empty” parameter to “Matters:Actions” (the names on my 2 datasheets). This works, as long as I keep the structured table in the top left corner of its respective sheet, but I know that’s a kludge and really doesn’t address using this code with structured tables. (It also seems that setting the strSearchAddress for all sheets to the maximum columns and rows used in any worksheet would result in wider searches than are necessary.)

  • Hi
    Is there an easy way to modify the code to prevent duplicates in the “Find All User Form” (Find All VBA Form – All Worksheets – Copy Paste.xls)?
    My problem is that I have multiple instances of the same text and I only want to show one of them. I.e.:
    xx
    xx
    yy
    yy
    Can the CountIf function be used?

  • Hi your macro is great but I am looking to modify it and I am not experienced really in coding VBA at all.

    I have a training database that in the first column lists procedures, second procedure number, the top row is a list of employee names, the data then goes on to state if an employee is trained, or not trained

    Is there a way in which I can change the search results of this macro to something like the below.

    Search criteria: Procedure #, Training Status (i.e. Trained, Not Trained, TNR)
    Results would give me a list of names depending on what i enter into status criteria.

    End result is to show a list of names on who is trained or not on certain procedures.

  • I am using the FindAll script in a inventory worksheet and it works great but there are a couple of problems.

    Background: This workbook has several sheets one of which is a table. Another sheet has all the controls on it including the button to run the FindAll script. The FindAll form is used to search against a specific column in the table.

    1) If the worksheet is hidden it returns the ranges that contain the search criteria on the form but shows an error when I attempt to go to the range. Error is: “runtime error ‘1004’ Select method of Worksheet class failed”

    2) When the worksheet is not hidden it all works properly until I attempt to change a value in a cell on the sheet. It actually enters the value into the corresponding cell on the sheet that the find all button is on. In order to enter/change entries in the table after a search I have to close the form navigate back to the control sheet, click on another cell, and then go back to the table to do the work.

    Any suggestions.

    Thanks

    • Hi John,

      Here are some suggestions for the issues you listed:

      1) You can unhide the sheet in the code, or you can check if the sheet is hidden and return a message that tells the user the sheet is hidden. Here is some sample code to check if the sheet is visible.

      If Worksheets(strSheet).Visible = xlSheetVisible Then
         Worksheets(strSheet).Select
         Worksheets(strSheet).Range(strAddress).Select
      Else
         MsgBox "Sheet is hidden"
      End If

      2) The ShowModal property of the Userform is currently set to True. This means you cannot select cells or interact with Excel while the form is open. You can set this property to False to be able to interact with Excel while the form is open. This is called a Modeless form.

      I hope that helps get you started. Thanks!

  • Hi.

    thanks for this!
    i found that if an column or row is hidden it cant search/ find values.
    is there an easy way to rewrite the code for also show hidden row/columns?

    • Hey Tes,
      Great question! We can change the LookIn parameter to xlFormulas to look in the hidden rows and columns. This is a weird issue with the Range.Find method. When it’s set to xlValues it does not search hidden rows and columns. To change the parameter, right-click the f_FindAll form and select View Code. Then change the LookIn parameter to xlFormulas in the FindAllMatches macro. See the screenshot below for details.

      Find All Form on Hidden Cells

      I hope that helps. Let me know if you have any questions.

  • Hello Sir,

    I am new to vba. I found your macro very helpful. Now I am trying to change it my way but failed. I want the listbox to populate from other sheet with only one column. and also search the list for only that column. But no success. Please help.

    mayank

  • Hi Jon! Your code is perfect and works like a charm. I am trying to set up another pair of worksheets (in the same workbook) to run the macro. I have copied the code to a new module, changed the name of the Results sheet (so it is different from the Results sheet that is working with my first worksheet), and have set up a Search button on my second “data” sheet, but the results of the search on my 2nd datasheet are populating my FIRST (original) Results sheet. How do I get the revised VBA code (the one with the revised Results sheet name) to run separately from the original code that is working so well for the first pair of worksheets? Thanks SO much! You rock!

    • Hi Kat,
      In the Copy_Paste_Result macro you can change the sRESULTS variable to another sheet name. The Copy_Paste_Result macro is in the code for the f_FindAll userform. I hope that helps.

      • Thanks, Jon, but as soon as I start typing in the f_FindAll2 userform I made (so I could have a separate results sheet for the separate data sheet), I get a Compile error: Sub or Function not defined, pointing to this line: Private Sub TextBox_Find_KeyUp (ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer). I can change the sRESULTS variable to another sheet name, but then ALL my results go onto that one sheet. Is there a way to have 2 different results sheets (for the Results of 2 different worksheets in the same workbook)? Thanks again!!

  • This search function is something I have been missing in exel for a long time, and the code you have written is just amazing! 🙂

    I have tried to change some of the code, but it is getting to complex for me. So I am wondering if you can help me to adapt the code in the “Find all VBA Form – All Worksheets – Copy Paste”? 

    This is my problem:
    In the “search sheet” I have two columns (A & B) I want to confine the search to. So I’ve made this change: “Set SearchRange = ActiveSheet.UsedRange.Columns(“A:B”).Cells”

    In the search box, under “results”; I want to show the results from the corresponding cells in column A,B,C. I.e. IF my search result is from A1, A2, A …, I want to show the information from A1, A2, A …, B1,B2,B… and C1, C2, C…

    In the same way if my search results is from B1, B2, B…, I want to show the information from A1, A2, A …, B1, B2, B… and C1, C2, C…

    When I click the search result, I want copy and paste the information from i.e. A1, B1, C1 to a new sheet.

    Is this something that is possible to solve?

    Kind Regards
    Eivind

      • Hi Jon
        Thank you for your feedback.
        I have confined the search to one column:
        set SearchRange = ActiveSheet.UsedRange.Columns(“A”).Cells
        and I am showing the results from the two closest columns in the listbox:
        arrResults(lFound, 1) = FoundCell.Value
        arrResults(lFound, 2) = Cells(FoundCell.Row, 3).Value
        arrResults(lFound, 3) = Cells(FoundCell.Row, 4).Value

        However, this gives me a problem in the copy paste section: “Run-time error “5”, invalid procedure call or argument
        This line on debug:
        strSheet = Replace(Mid(strAddress, 1, InStr(1, strAddress, “!”) – 1), “‘”, “”)
        Any idea on how I can modify the code to copy paste from three columns?

        Kind Regards
        Eivind

  • hi jon
    its really good micro and I m using your micro for search my part no
    its really great work, i am trying to update the information using same form
    please help me

  • Hi Jon this macro is amazing can adapt a part of it.
    I wonder how I can put more columns with other information on the results of BOX List?
    thank you so much!

    • Hi Andre,
      Great question! You will first need to change the ColumnCount property of the listbox. Here is a screenshot that shows where to find that property. It is currently set to 2 for the 2 columns in the listbox.

      ColumnCount property of VBA Listbox Userform

      Then, in the FindAllMatches macro you will change the array that feeds the listbox. The screenshot below shows more details. You will just need to change the number of columns in the array, and then add additional lines to pull in the information.
      Load VBA Array with Items in a Loop

      If you wanted to pull in the value of the cell to the right of the found cell then you could use the following code. Notice that the column number is 3. This would be the third column in the array and listbox.

      arrResults(lFound, 3) = FoundCell.Offset(0,1).Value

      I hope that helps. Thanks!

      • Hi Jon!

        This find all macro is most helpful for me. I will use it to search an inventory of refrigerators and freezers in my lab. I have also modified the code as you describe to show more columns in the Result box. However, I would also like to have headers in the Result box. I have switched to TRUE for headers in the ListBox_Results which gives me “empty” headers. Can I somehow assign cells from a worksheet to show as headers, or otherwise specifiy them? I have the same headers on all the worksheets in this workbook that I use for my inventory.

        • Hi Martin,

          Great question! I typically create Label controls and place them above the listbox for headers. You can use the headers within the listbox, but I don’t like the look and it can get messy when programming the listbox. I hope that helps. Thanks!

      • Hello Jon,

        When adding more columns of information to the listbox, can it be done with column references instead of using offset as you show. I would like the result in the listbox always to be pulled from column 1-5 for instance. This is since I would like to be able to search different categories of information and these are located in different columns. The offset function will not be good if my search hit is in column 3 instead of in column 1. I would like the textbox to return column 1 through 5 in both cases.

  • Hi thank you for this VBA it has been very helpfull to me. I want to use it in another spreadsheet but i want the search box on one workbook but i need it to search items in a totally seperate spreadsheet. Please could you help me witht the VBA code

    thank you
    Paul

  • hi
    i am in process engineering
    i want to animate the chart “Time Vs Processes”
    my question is ‘how to set the time range in developer code?’

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly