Hiding/showing columns based on dependent list

[acarrier1868]

Hello, Excel-ers! I have a question that I'm hoping someone can help with.

I have created a set of dependent drop down lists in subsequent columns. In other words, Column A has a dropdown list with options Yes, No, or Maybe. In Column B, if Yes or No is selected in Column A, another drop-down list appears. In Column C is yet another drop down list, based on the selection in Column B.

So far, so good. What I was trying to figure out is if I can hide columns (or unhide columns) B and C depending on what is selected in Column A. For example, if Yes is selected in Column A, I'd like Column B to be unhidden - but for Column B to stay hidden if Maybe is selected.

I got the VBA code to work for the above. However, once I select something from the dropdown menu in Column B, it goes back to being hidden. I can't figure out why. I'd like for that Column to stay visible once a selection is made.

Any ideas would be greatly appreciated!!

[TMS]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[TMS]

Ideally, for those of us using iPads, also post the code separately (using Code Tags)

[acarrier1868]

Attached is the file, with three examples - the first with nothing selected in Column B, the second has selections in Column B that would not prompt further action, and the third has selections in Column B that should cause Columns C and D to be visible; however, once you make a selection in Column C or D it causes them to be hidden again.

Here's the code.

Please Login or Register  to view this content.

[TMS]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

That said, try:

Please Login or Register  to view this content.

[acarrier1868]

Thank you, TMS. My apologies - I almost never use macros. I edited my post above.

Thank you for your suggestion - that seems to have done it. Of course, in doing so I realized the fallacy of my intent - since there are dozens of rows in each sheet, "No" is bound to be selected at some point, so the following columns will be visible throughout - making the hiding of them a moot point.

It was a nice theory, while it lasted...

[TMS]

You're welcome.

I edited my post above.

Thank you for that

As a suggestion, you could colour/black out and/or lock the cell(s). However, you would need to protect the sheet for this to be effective.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.