Adding a search box with VBA - School Reading Awards

[Feenix60]

Hello All,

I'm hoping for a little help with a spreadsheet I've built for my school to track Reading Achievements across all the Classes.

The file works by taking info from a "Data" worksheet and populating a series of charts to show current status.
It may be a little clunky but it works. All the names in this file version are random so no GDPR issues!

When a new child joins the school I currently add their name and class to the bottom of the list in the "Data" tab and then click the 'Sort Names' button I've added and it sorts them into the correct class alpha/numerically. I managed to follow some examples from the Internet and by doing step-by-step managed to add VBA code to make it work even though I don't know VBA.

What I would like to do now is also add a 'Search Names' text input box that will collapse all the rows apart from the matching criteria ... so as to make it easier to enter each achievement rather than having to scroll up and down the data all the time. I would like the search box to be dynamic so it displays rows as you type Smith e.g. S, Sm, Smi, Smit, Smith etc and finds any matching rows that contain the search letters within a name e.g. Smith, Psmith and Zesmithson would all be displayed.

I've tried and tried and did manage to create the search box and a linked empty cell etc but had two problems!
1. It stopped the Sort Names from working
2. When the search box was cleared it only showed the rows that contained text in the names columns and hid all the blank rows thus stopping me from adding new pupils etc.

I would be really greatful if someone could have a looking at my attached file and add a text search box into cell A2 but that when cleared reopens ALL rows.
Or if there is a far more elegant way of being able to have both functions of Sort Names and Search side by side please feel free to enhance the file!
The password if needed for any tab is Bethany123

I want to keep 400 rows of data to allow for an everchanging list of pupils in the school - we are usually 32 per class and 12 classes, so if I account for 33 x 12 = 396 rounded equals 400 I should be safe.

Your help is gratefully received as it seems to be a bit beyond me as hard pressed Teaching Assistant!

[Logit]

You forgot to attach your example workbook.

[Feenix60]

You forgot to attach your example workbook.

D'oh! Schoolboy error! Now corrected, Thanks.

[ByteMarks]

Does this work for you?

[Feenix60]

Many thanks ByteMarks, exactly what I was looking for.
I had an issue at first in that I was blocked from running Macros but Google helped me out!
Many thanks again for your help

[ByteMarks]

You're very welcome.

[Feenix60]

Hello - Hopefully ByteMarks sees this thread as they have done stirling work on my issue.

I have a spreadsheet that I'm using in my school (data/names in the attached file is random so no GDPR issues) to record Reading Awards.

My VBA knowledge would fit on a stamp so I posted my file asking if someone could add a Search Box on to the 'Data' worksheet of my file to allow users to filter names.

Unfortunately the Search Box will work fine if the 'Data' worksheet is unprotected but returns a Run Time error message if I use it when the worksheet is protected.

Is there a way to protect the worksheet (so errant TA's don't play with structure etc. The password is Bethany 123.

If some kind soul could take pity on me and wave a magic wand over the VBA/file and get the Search Box working in a protected worksheet.

Many thanks.

[ByteMarks]

Try unprotecting before and reprotecting after

Please Login or Register  to view this content.

[Feenix60]

Many thanks ByteMark - that's done the trick