Avatar of Bright01
Bright01
Flag for United States of America asked on

Excel Macro Trigger creates Subset

I have a great outline interview Worksheet that EE Pro Martin Liss helped build.  This is a very solid WS that should help people who do interviews.   Right now, if you click the Outline button, it discloses different outline levels.  If you click the black Boxes in Col. C, you will get a cycle for a check, a question mark or an X.  Here is what I'm trying to do;

I would like to see, when you double click the black box, and the box shows a "Check" or "P" with Wingding2, it auto expands by one level.  If you click another box and there is a 3rd level, it will expand to the 3rd level. Click if so it doesn't show a Check (or "P") and it will contract back to the appropriate level.

Keep in mind, this list of questions/text will be at least in the neighborhood of 200+ rows when it is expanded.  You can use Column A as a designator since it equates to a specific "level."

Thank you in advance,

B.
Outline-Dropdown-Trigger.xlsm
Microsoft Excel

Avatar of undefined
Last Comment
Bright01

8/22/2022 - Mon
Martin Liss

Let me see what I can do.
Martin Liss

Can there be more than 3 levels?

And if I double-click cell C8
Before double-click

should it look like this?
After double-clickOr like this?
Or?
Bright01

ASKER
YES.  If you double click... you get only 1 level down as in your second example.  If you click on the Sub Text A, you go to the next level..... but only if it's there.

B.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Martin Liss

What about me "Can there be more than 3 levels?" question?
Bright01

ASKER
Interesting Question.   I thought that there may be given changing requirements.  But this is actually a "tree" with 3 levels.  Right now 3 levels is all there is.  I'm trying to keep it simple.... but I leave the option open to add additional levels later.....  So now..  let's stick with 3.  Does that work?

B.
Martin Liss

Try this workbook. It has a new sheet called "Templates" that could be hidden.
28692258.xlsm
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Bright01

ASKER
Martin,

I'm getting errors on the opening.  

b.
Problem.ppt
ASKER CERTIFIED SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Bright01

ASKER
Much better!  Almost there.....

However, it should only trigger the outline subgroup if the box is "checked".  Otherwise, it collapses the boxes back. In other words, the statements/questions (Green/Yellow) are only exposed if a check box in the top level (Blue) have been selected as "Checked".  

So, when you clear the entire sheet, you will only see the blue boxes, unless you decide to trigger the "Display" Macro.  Then you will see the entire sheet.

You nailed this one.  I'm going to close this out and open another question to get the second half of this completed.

Thank you very much!   And I hope you will pick up the follow on.

B.
Bright01

ASKER
Nailed it!

I'm going to ask another Question given it should only trigger the outline subgroup if the box is "checked".  Otherwise, it collapses the boxes back. In other words, the statements/questions (Green/Yellow) are only exposed if a check box in the top level (Blue) have been selected as "Checked".  

So, when you clear the entire sheet, you will only see the blue boxes, unless you decide to trigger the "Display" Macro.  Then you will see the entire sheet.

You nailed this one.  I'm going to close this out and open another question to get the second half of this completed.

Thank you very much!   And I hope you will pick up the follow on.

B.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23