Conditionally Hide Sheets

**amotto11** · 05-15-2012, 10:22 AM

I have a workbook with 6 sheets, and this workbook is serving 2 different uses. The first sheet gives the user a choice of what they would like to do in the workbook via a dropdown menu with the 2 choices. The next two tabs are the two different uses of the workbood and the final 3 use the information in tab 2 or 3, based on what the user chose to do in tab 1, to create a report. What i would like to do is sort of dummy proof my workbook. I would like to hide one of the two sheets that the user is not going to use, so that they cannot enter data on both worksheets and confuse my last 3 sheets with an overload of data. So for example if the user has the choice in the drop down menu of Dog or Cat, if he chooses Dog then the workbook will hide the one sheet, maybe 2 or 3, based on Cat. If he chooses Cat it will hide the other of the two sheets. The last 3 sheets will need to be shown at all times. The only 2 that i want to conditionally hide are Sheet 2 or 3. Any help is greatly appretiated. Thanks.

**Palmetto** · 05-15-2012, 11:22 AM

Basic code, no error handling and it uses sheet code names instead of worksheet names.
The code must go in the worksheet module of the sheet containing the dropdown cell.

Adjust sheet and cells references as needed.

Please Login or Register  to view this content.

**amotto11** · 05-15-2012, 11:34 AM

Originally Posted by Palmetto

Basic code, no error handling and it uses sheet code names instead of worksheet names.
The code must go in the worksheet module of the sheet containing the dropdown cell.

Adjust sheet and cells references as needed.

Palmetto, That worked great for the first time i used it, but when went back to sheet 1 and changed the dropdown menu item it did not change the sheets. in other words the code only worked for one use. is there a way to make it where the user can change their mind and it will work for more than once. maybe a loop of some kind. Thanks.

I am sorry, It worked. I had spelled one of my options in the dropdown box wrong so it didn't match the code. Palmetto, Thanks for all your help.

**amotto11** · 05-15-2012, 01:17 PM

Palmetto,

With your code would it be possible to lock cells based on criteria in other cells, by just changing a couple of variables and hide to lock, or would i need a whole new approach? in other words, if cell A1 is user input to be 10 then the program will lock cells A2:A9, so the user has to enter their data on or after cell A10?

**Palmetto** · 05-15-2012, 05:47 PM

Locking cells requires the use of worksheet protection or a method to cancel the user action if the active cell is in the target raneg (i.e. A2:A0).

There is almost always more than one way to go about things in VBA.
Here is a very simple approach that avoids the use of sheet protection.
It uses the worksheet_selectionchange event to detect if the user selects a cell in the range of A2:A9 and, if so, check cell A1 for a specific value (e.g. 10) and automatically moves the cell pointer to cell A10 and displays a message.

The code must be put in the worksheet module (rightclick sheet tab > View Code - copy and paste code to VB Editor and adjust references as needed).

Please Login or Register  to view this content.

**amotto11** · 05-15-2012, 06:05 PM

Originally Posted by Palmetto

Locking cells requires the use of worksheet protection or a method to cancel the user action if the active cell is in the target raneg (i.e. A2:A0).

That is sort of what i want to do. My real scenario is a worksheet that is locked except for a number of cells. In A1 the question to the user is what is the quarter that we are currently in, in the year. the user will input something like 2Q11 or 3Q09 or 2Q12, etc. I then made a list of starting at A30 with whatever they put, continuing up wards for 20 quarters or 5 years, to cell A10. My next question to the user is to input a date that they started business on. that date is converted to a quarter in the same format as the last question. what i am wanting is if the date that they started business in is less than 5 years back from the current quarter, i want them to only be able to input their data into column B where they did business in column A. For example in Cell A1, they put that the current quarter is 2Q12, then in Cell A2 they put that they started business on 1/1/2009 which would be 1Q09. this is not 5 years back from the current quarter, so they should only be able to enter data into column B next to the quarters that they did business and no more. so i would like to conditionally lock the cells that they cannot do business on based on the first two questions. The rest of the page contains other locked and unlocked cells, that the user has to input data that corresponds to the rest of my workbook, so i don't want to mess with them. Do you know of a way to conditionally lock the cells that i want so i can fully dummy proof my sheet. Thanks for all of your help.