Excel VBA Userform with search box

  • Good afternoon. I am new to this forum and extremely new to VBA. I've searched this forum, and also did a Google search for answers, and am coming up short. I hope I'm posting question this correctly!

    Using Excel 2013, I currently have a spreadsheet, with a total of 40 columns, where injury data is recorded. I stumbled through creating a Userform to make it easier to enter all that is required.

    Entries in the spreadsheet are summarized in the list box. The list box does not display all 40 columns; rather, only the most crucial information from 13 select columns (not all are adjacent to one another) from the spreadsheet.

    If an entry needs to be modified, the user double clicks on the record displayed in the list box, it is loaded back into the User Form, and the appropriate edit can be made and saved back to the original spreadsheet.

    I was recently asked to add a search box, allowing users to quickly search through/find entries on the spreadsheet. The search criteria is limited to these three columns: Last Name (column C), First Name (Column D), and Staff ID (Column E). I've added a Command Button next to the search box (txtSearch) that, once clicked, would search for and filter the items displayed in the list box based on the entry in the search box.

    I used the following code, associated with the Command Button, but receive a "subscript out of range" error (run time '9'):

    The error seems to focus on this line of code: iSearch = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.count

    I am really lost, and would appreciate some guidance on what I'm doing wrong.

    Thank you.

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.


    your code goes between these tags


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


  • Hello and Welcome to the Forum :)

    Maybe you could test the following standard instruction

    iSearch = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

    Hope this will help


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Once you have tested the modification ... feel free to share your comments ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks for the suggestion. And I apologize that I didn't post properly.

    The suggested modification still generates an error; however, after much digging and searching, I think I figured out my mistake. I need an additional step, an advanced filter, in order to finish my project.

    I'm still learning, and testing my latest assumption. If/when I get it, I will post an update.

    Hope I'm on the right track.

  • Glad to hear you are making progress :)

    Do not forget to search the Forum which many many suggested solutions ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Greetings. During a brief hospital stay, I had lots of time to dig into this project and the issue previously experienced. Thanks to much of the information in this Forum, and a couple of of others, I've solved the initial issue by utilizing a helper sheet. Thanks for that information.

    I am encountering a new, different issue with this almost completed project, but will post as a new issue and attach the file as it exists now (still much more work to be done before it's complete).

    Thanks everyone!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!