Using a listbox to hide/reveal rows.

Hello, everyone. I have searched for the following request/idea and I haven't seen any just yet. Anyway, might as well post about it.

I have a spreadsheet that uses a little VBA to hide/reveal rows via a button click. Pretty simple, really. However, I have two more ideas I want to apply to it.

1. I want to change the buttons to a listbox. The file is actually a performance report for individuals. Eventually, there will be a lot of them. For now, there are only 20. Is it possible to use a listbox referring to some hidden masterlist of names somewhere that hides/reveals the respective rows about the chosen name's data and still be able to have a check of what the choice was to activate the VBA for that name?

2. My VBA right now was pulled out of a simple method. However, it has one weakness. This is the code, btw:

Please Login or Register  to view this content.

Basically, what it does is just hide/reveal rows A15 to A40. However, when I choose some other name, instead of hiding A15 to A40 and revealing, say, ONLY A68 to A94, what happens is it just "appends" to the data at hand, ie. showing A15:A40 and A68:A94 at the same time. I want to edit the code in such a way that it only shows one person at a time. Is this possible?

I think this is pretty basic so I won't upload a workbook for now. I can pretty much make my own listbox and all. My only problems are the reveal/hiding mechanism and how a listbox can be used for VBA.

Thanks for any and all help.

I love this stuff. What you seem to be asking for is a built-in function already.

The Auto-filter does this.

Example:

A B C
1 NAME COLOR SHAPE
2 BOB BLUE ROUND
3 CAROL GREEN ROUND
4 BOB RED SQUARE
5 TED GREEN SQUARE
6 ALICE PURPLE TRIANGLE

Select cell A2.
Goto the Data tab and click on the Filter button(looks like a funnel). It will put some dropdown controls on the sheet(on Row 1).
Take a look and try some of these controls to see what they do.
Tell me if it works for you.

Hello, Mordred. Thank you for the prompt reply.

How do you propose I use Excel's Filter option with my VBA? I am a bit lost on the idea.

I know that there's basically a simple solution to all of it but, well, I try to keep it idiot-proof, to be honest. It's basically a tracker that I need to keep in a shared drive where people can just open it up and select their name and not mess it up with filters and all.

Right now, I have 20 buttons laid down in a matrix in A1:A4 and they just have to choose their names. However, it will grow and is expected to grow almost geometrically, with a modest expectation of about 500 performers eventually. At that level, I want to be able to, say, produce droplists that can even filter out by Surname or First Name or Event. A filter could do it, but I want even a kid without any knowledge of Excel to navigate through it intuitively.

Besides, it helps me with my personal learning.