When I present spreadsheet results to clients, there are always several variables that I know the clients will want to play with ("What if this was higher, or if that was lower, or if I could see just those 3 items..."). This means I need to give the client choices and options, but I need to make them simple to use and have the model respond immediately.
I've put together a collection of some techniques which you may find useful for this. Specifically, how to
make charts or lists dynamically adjust to the amount of data provided
allow users to choose how many items to show, how to sort them, even which items to show (from a list), all without any code
make multi-select listboxes work (this requires a little code, which I have written for you)
Please have a look at the workbook here (Dynamic User Selection.xls).
For the people who are wary of any workbook containing code, I will declare in advance that it contains two code modules, one containing the listbox code, and another to copy the listbox code to your own workbook, when you press a button in the workbook - this is for people with no VBA experience who would find it difficult to copy the code manually.
Comments, suggestions and improvements are welcome.
Jan 19 2008, 06:32 PM