May 12 2020 05:13 AM - edited May 12 2020 05:54 AM
Question
How do I create (in)visible cells based on a value in a certain cell?
Specifications
In an Excel file I created I have a number of rows that only need to be visible when a certain question is "yes". The closed question "Yes" / "No" (In Dutch "Ja" / "Nee") is in cell B35. For this I made a Dropdown-menu where the choice is made possible. The rows that need to be visible when the answer in cell B35 is "Ja" in the Excel file are 36:44. What's the easiest way to do this?
May 12 2020 05:23 AM
Thank you for your response @bhushan_z !
I'm wondering which VBA-Code I need. I know how I find the VBA-code but I would like to know how I should write this code.
May 12 2020 05:30 AM
VBA if you'd like to hide entire rows.
If to hide cells values in these rows you may apply conditional formatting rule applying white on white color for them (or like). However, they will be visible in formula bar if user focuses on such cell.
May 12 2020 05:34 AM
@StijnCl you will need a macro that runs every time a cell value changes (event-driven macro)
you can google it
Also, if you want me to build the code,
is it possible for you to share the file?
You may remove confidential info from the file.
May 12 2020 05:49 AM - edited May 12 2020 05:50 AM
@bhushan_z I tried to implement the results I found on Google, but I it didn't work...
I attached the file in this comment. It's about the last two sheets. The cell that contains the question with the Yes / No answer is dark blue. I made the rows that need to vanish light blue.
May 12 2020 05:52 AM
Thank your for your answer @Sergei Baklan !
Unfortunately I can't write a VBA-code that's why I have this question.
May 12 2020 06:49 AM
Solution@StijnCl Try the attached file.
Note: Macros are for fixed rows and cells. If you insert/delete rows, it might fail.
May 12 2020 06:57 AM
@bhushan_z You're the best! It was just the other way around. So "Ja" was visable and "Nee" was invisible. But I changed it myself. Thanks for your help! Much appreciated!
May 12 2020 06:49 AM
Solution@StijnCl Try the attached file.
Note: Macros are for fixed rows and cells. If you insert/delete rows, it might fail.