Hiding Columns

**freybe06** · 02-20-2011, 02:52 PM

I apologize - I'm sure this question has been asked before but I have little to no experience when it comes to running macros.

Okay, so I have a drop down in A2 that a user can select either "Yes" or "No" - What I'd like to do is have it so that if A2 says "Yes" then columns E through J are hidden. If the user selects "No" then columns K through P are hidden.

I've tried to build/run a macro in the past and the spreadsheet just got caught in an endless loop. That's why I'd like to see if I can get as specific directions as possible.

Thank you in advance for your help.

**geoB** · 02-20-2011, 03:36 PM

This might be easier done using Option Buttons. See attached.

You'll need to decide whether either set of columns is visible when the file opens.

g

**Charles** · 02-20-2011, 04:00 PM

Hi,

I added a drop down in B1 for the selection.

**freybe06** · 02-22-2011, 10:32 AM

Thank you Charles - That is exactly what I was looking for. And thank you for your help as well geoB. Never hurts to learn different ways of doing things

In addition to this question - is it possible to hide tabs when a certain drop down item is checked? For instance, if in Sheet1 I choose "Yes" in A2, can I make it so excel hides Sheet4, Sheet5, and Sheet6?

Thanks again!

**freybe06** · 02-22-2011, 11:47 AM

I apologize - I'm so close to having this. One last question - Regarding the hiding columns, if I wanted to have the drop down in a different sheet than the sheet where the columns are being hidden, how would I change the macro?

Also, instead of picking "Yes" or "No", it will work like this - if the user selects "Client 1" then the equivalent of "Yes" will hide, if the user selects any of the other clients, then the equivalent of "No" will hide.

Thanks again!

**Charles** · 02-22-2011, 01:04 PM

hi,

If you want the drop down to be in diffrent sheet you will also need to change the range of the drop down. I have it in sheet2 for "Yes" and "No"
The code is in sheet1 code module. Right click the sheet1 tab and then select Code. This will take you to the code for sheet1.
I modified the code to show you how to hide the sheets.

Please Login or Register  to view this content.

**freybe06** · 02-22-2011, 01:11 PM

I guess I'm not sure how to change the range of the drop down. If the drop down is on Sheet1 in A2, and I'm looking to have Sheet2 columns hidden using the macro, would I put the macro code in Sheet1 or Sheet2?

Thanks!