Using conditions to show/hide worksheets

**Centara** · 02-25-2009, 12:39 AM

I'm new to the forum so I wasn't sure exactly where to place this question. I'm currently in an Accounting class that is based solely on Excel and the advanced uses of it. Unfortunately, we have a final project that we have to spend many hours on so I was hoping I could start it semi-early. This means I haven't gotten to use macros yet so I'm not sure if they would be able to solve my problem. So here it is!

I'm creating an excel workbook that will allow any business or accounting student to go in and create their four year plan while giving them all of their requirements for their particular degree. One of the features I wanted to have with this program is that it wouldn't display all the worksheets for each major, but rather the ones you choose that you are associated with. I was going to have a drop down menu on the first page that allows you to choose your majors, those being accounting, or business administration with its 5 focuses. If I just allow all the worksheets to be shown, there would be 6 in total and I thought it might get confusing for a student who's only taking one of the 6. My hope is that on the first page, when you choose one of the options, it would automatically display the hidden worksheet that goes along with that major.

It's kind of complicated and I'm kind of ambitious right now. I'm not sure if this is even possible or if any of you will understand what I'm saying, but if you have any ideas on how to make this possible, it'd be great if you could share them!

Thanks

**JBeaucaire** · 02-25-2009, 02:10 AM

Perhaps like this, on a worksheet called "Choices" and 6 other sheets called Major1-Major6. A selection box in cell B5 and the following Worksheet_Change macro in the Choices worksheet module:

Please Login or Register  to view this content.

**Centara** · 02-25-2009, 02:21 AM

Okay, so I must say that is exactly what I was looking for! The file looks amazing but I'm not quite sure on how to perform the macros just yet. I was sure that was going to be a necessary part of getting all of this project completed. I will save this page as a reference and whenever I learn how to do the macros I will refer back to this to help me out on completing the project.

Thank you so much for taking the time! Now I know that it is actually possible to get this completed and I'm not just shooting for the stars!

**Centara** · 03-01-2009, 09:27 PM

Okay, I have one problem with this! So I have multiple sheets other than that of the Majors but when I fixed the code to fit my worksheet, it made all of the other sheets hide! So I'll attach my sheet that I have created so far. Also, I can't get the macros to activate. I try to work it but it doesn't activate the hidden sheets. Do I have the code in the incorrect position? I think it's under the module area but I have no idea... I'm still new with the macros thing.

So basically, my questions are how do I get the Semester Tables sheet to stay visible while all others hide? And is my code currently in the wrong place?

I also had another question concerning vlookup that I figure I might as well post here just in case anyone knows it who can help with the macros. In the semester tables section, there are multiple tables where the classes are going to be placed. On the requirements page I wanted there to be a vlookup formula for each ofthe classes so it can look through those tables and recognize/place in the values of the classes put in each semester, but there are 15 tables! I've heard of using the IF function along with vlookup but I can't figure out how it works... Help?

**JBeaucaire** · 03-02-2009, 02:51 AM

Cut that macro out of the Module1, double click on the Sheet1 Module for "Student Information" and paste it into that module. This is a worksheet_change macro, so it has to be in the worksheet module you want "watched".

Also, just a suggestion...maybe color code those tabs so it's REALLY obvious when another sheet appears.

**JBeaucaire** · 03-02-2009, 04:28 AM

You know, I looked at your sheet and noticed you have THREE cells with Majors in them and I can just guess your next question will be how to keep all of them and have their own sheets appear/disappear as choices are made. That's an entirely different approach than the one we've used so far.

Here's an updated sheet with the new approach. I left the Data Drop Menu Input sheet visible so you can see the changes.

Instead of Worksheet_Change on Student Information, we're going to use Worksheet_Calculate on the Data Drop Menu Input. Every time this sheet changes the other sheets will hide and unhide. Now why would this sheet change? Look at the stuff I've added.

First, I've added a second section of Majors that "shrinks" as choices are made on the main sheet. This second list is what is actually being used in your Data Validation on the main sheet. So when you select "Accounting", the option disappears from the drop down for the next choice. Pretty cool.

This is being done with the "Key" column I added. As choices are made, they lose their key in the index. This causes the second list to reorder and since this happens in formulas, the RAND() formula in cell G1 creates an actual "change" in the sheet and THAT triggers the new macro to hide and unhide sheets.

In effect the new macro unhides sheets that have lost their index value in the "Key" section. If there is a number there, the sheets stay hidden.

Anyway, it works. You can now make choices on the main sheet all you want and the sheets will hide/unhide.

**Centara** · 03-02-2009, 02:24 PM

Wow. You're amazing at this! Thanks for all the help.

The macros code actually makes a lot of sense to me. I used to be somewhat fluent in HTML so I can disect codes and figure out what they're trying to say so it's definitely going to help me later in this course.

I'm going to have to look at the list stuff again, though, where you made it remove the already chosen major. That's an amazing code to do that.

Thanks fo all the help!