Find All VBA Form for Excel - Excel Campus
143

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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 143 comments
Andrew - July 24, 2017

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!!

Reply
Alpesh - July 15, 2017

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.

Reply
Phillip Fries - May 7, 2017

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.)

Reply
Eivind Sjursen - March 5, 2017

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?

Reply
Stephen - February 16, 2017

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.

Reply
John - February 7, 2017

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

Reply
    Jon Acampora - February 15, 2017

    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!

    Reply
Tes - December 30, 2016

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?

Reply
    Jon Acampora - January 4, 2017

    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.

    Reply
Andrew Williamson - November 26, 2016

Hi,

Thank you so much. This has allowed me to have a far superior search facility than the one I built.

Reply
Beekster - November 16, 2016

Pure Genius…..
Have been looking for a way to do this for weeks. Works like a charm.

Reply
mayank vijay - October 20, 2016

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

Reply
Kat - August 12, 2016

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!

Reply
    Jon Acampora - August 20, 2016

    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.

    Reply
      Kat - August 22, 2016

      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!!

      Reply
onur - August 4, 2016

Hello,
i appreciate your work,and you are amazing…!

Can i ask you a few things if you have time from the email ?

Reply
    Jon Acampora - August 8, 2016

    Hi Onur,

    Thanks for the nice comment. Unfortunately, I am not taking on any new projects or doing consulting right now. Thanks for your understanding. 🙂

    Reply
Eivind - July 7, 2016

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

Reply
    Jon Acampora - July 31, 2016

    Hi Eivind,
    Thanks for the nice feedback. I do have a video series on 3 ways to copy and paste with VBA that might help with your code for the copy paste. I hope that helps. Thanks again!

    Reply
      Eivind Sjursen - August 9, 2016

      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

      Reply
ravi - May 22, 2016

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

Reply
Andre - February 29, 2016

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!

Reply
    Jon Acampora - March 1, 2016

    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!

    Reply
      Martin Bengtsson - September 1, 2016

      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.

      Reply
        Jon Acampora - September 7, 2016

        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!

        Reply
      Martin Bengtsson - September 1, 2016

      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.

      Reply
Paul - December 10, 2015

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

Reply
dipak solanki - June 8, 2015

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?’

Reply

Leave a Reply: