hide/unhide columns based on dropdown list.

[bg781np]

Hi All,

I am new to using macros. When I say new, I’m talking in the past 2 weeks, so any help would be great.

I have Microsoft Excel 2010, and I have created a spread sheet with 17 columns (A – R) and 95 rows. In the entire column D, there is a dropdown list that I created using Data Validation. It lists down the words Yes, No and call back.

What I need is when you click in any cell in column D, and choose either No or Call Back from the drop down list, that columns E trough to K will be hidden, and when you choose Yes from the drop down list these columns will be unhidden. Also how can I protect and unprotect using code?

Can you please help? I tried many times but not successful. Appreciate if someone will be able to walk me through how to do this. Thanks a lot. Nephi.

[mehmetcik]

The macro you need is sheet specific and runs whenever you change the sheet,

The macro checks to see if the cell that was changed was in column D.

If so the macro then reads the cell and modifies your spreadsheet.

If you had included a sample I'd know how to help you further.

How did you create a drop down list in the whole of column D.

why not on the top row and freeze the window?

[BeachRock]

Hi Nephi,

I'm not sure I understand what you mean about the result of hiding columns for rows where the D cell in a row contains either No or Call Back. You could hide the rows where D contains those values but not just some of the cells on those rows, which doesn't sound like what you want to happen. I think you may want to rethink how this workbook is going to be used.

Here's an example of hiding specific columns, which also contains the commands to unprotect and protect a sheet.

Please Login or Register  to view this content.

Then unhide them.

Please Login or Register  to view this content.

Also note the Application.ScreenUpdating = True or False. This will eliminate any screen flicker or any movement at all that might be visible while the macro runs.

I'm afraid I don't know how to get these macros to react to a selection of the DV Lists though. I would just use buttons and assign the macros to them, running them when needed.

[mehmetcik]

I have created something to get you started.

change col d and columns get hidden
change column d again and columns get un hidden.

right click on the sheet name and select view code to see the macro.

[bg781np]

Hi Guys,

Thank you for trying to help, I'm sorry if I was not very clear as I said I'm new at this. Attached is the spreadsheet I'm working with.

In each cell of column D, there is a dropdown list that I created using Data Validation. It gives the user the options of picking from the list of Yes, No and call Back.

What I need is when you click in any cell in column D, and choose either No or Call Back from the drop down list, that columns E trough to K will be hidden, and when you choose Yes from the drop down list these columns will be unhidden. Also how can I protect and unprotect using code?

I hope that this might be a lttle clearer especially with the attached spreadsheet. Cheers Nephi.

[BeachRock]

Hi Nephi,

You can't only hide the cells adjacent to the row you are on (E through K) without hiding the entire column for all rows on the sheet. Maybe some conditional formatting would better suit your needs? You could then cause the info for those columns on each row to change colors or fonts or whatever when Yes or No or Call Back is selected in column D on whichever row you are on at the time.