Excel Macro Trigger round trip

EE Pros,

I have an ever evolving Macro that Martin Liss has created with/for me (outstanding work).  It needs one enhancement.

What it does;  Current version displays a drop down set of statements (Green/Yellow) from the base question level (Blue) in an Outline Format.  It also displays all of the statements by outline level when you click the display button.  What I need now is simple (hopefully)!  I need "the round trip".  I need it only to display the drop down subsets (unless the display button as been selected) ONLY if the box is "checked" (i.e. "P" with Wingdings2). When you deselect a "Check" (i.e. could display in the box, a ? or an X or blank but they don't count as a check) the outline level is retracted back to the previous level.

That's it!  

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Hmm. I overlooked this one. Let me see what I can do.
Martin LissOlder than dirtCommented:
So do you want to delete the retracted row or just hide it?
Bright01Author Commented:
Just retracted....not deleted.  U can use the helper cell if u like in A.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Martin LissOlder than dirtCommented:
And when are the retracted rows shown again?

Another question. Deleting or adding rows manually causes your Worksheet_Change event to fire, which as written always affects 500 rows and it's time-consuming. Do you want it instead to just affect the active row?
Martin LissOlder than dirtCommented:
Another question in addition to the one above this. Do you want to do this when cell is clicked or do you want to do it when the cell is double-clicked. If you want to do it when it's double-clicked it will probably get confused with adding a new subitem.
Bright01Author Commented:
OK... let's see if I can answer this precisely.

1.) I too noticed the incredible delay when adding or deleting a row.  YES, I need the ability to add and delete so if there is a way to isolate the firing of the macro that expands the WordWrap capabilities to the row you are on, I think that would work.  That Macro wordwraps column K as someone may add many lines of text and I needed a way to allow for unlimited text additions in Col. K.

2.) Expose or Retract Rows;  There should be two ways to accomplish that.  One is how it does it now using the exposure BUTTON to trigger the simple outline level(s).  However, if the row is not exposed, can we trigger the exposure of the sub rows with a check mark in a particular box?  The only way I thought of doing that was to use the "helper" cell (in col. A,.... 1, 2 or 3), and have a macro that looks at the level and "IF" a box is checked, expose the next level down (i.e. if a box is checked, and level 1, then show all level 2s that are below that particular row; if level 2 is checked, then expose all of the level 3s below that particular box, etc. etc.).  However, if you clicked on the exposure BUTTON again, the outline cycle we have in there now would reengage.

Hope that helps. And I don't want to over complicate this.  But if its easy to do, then it would be an even better tool.

Martin LissOlder than dirtCommented:
I just wanted to let you know that I haven't forgotten about this and was still working on it. The method I was trying ran into a problem and while thinking about it I realized that you may already be able to do what you want. Click on one of the + or - signs. Isn't that what you want?

or -
Bright01Author Commented:
Martin..... why didn't I think of that????  

 Yes that's exactly what I was looking for.....but a way to have the macro do it.... so when there is a check in the box, it goes one level down and when there is not one, it retracts.  I think you just stumbled on the creative and potentially simple way to do that.
Martin LissOlder than dirtCommented:
There are several changes in the attached workbook.

1. One of the three possible captions for the command button was "Display More". I changed that (in two places) to "Display Less" because that's what actually happens when you click the button at that point.
2. I cleaned up the Worksheet_Change event so that there's no more long delay.
3. I moved the Toggle sub from the sheet code to Module1. (Congratulations on putting everything in one module.)
4. All rows in a set are expanded when the cell becomes a check and retracted when it becomes a question mark.
Bright01Author Commented:
Martin.... brilliant!  Almost there.  There is a slight flaw in the code.  Clear the boxes and set it to Level 1.  If you click the Text A Box it will display green AND YELLOW ROWS....but not all of them.   It should only show Green rows under Text A; then Yellow rows when you click the Green rows.  You have it working in the first several green rows....but for some reason, about half way down, the Yellow rows show up when you select the first level Text A option.

Check it out.

Thanks.... and this is looking really good!

Martin LissOlder than dirtCommented:
In your comment you say "click" a couple of times and in one case "select". Do you mean double-click for all of them?

Are you saying that if there are both green and yellow rows visible under a row with a helper cell of "1", that you only want to hide the green rows and leave the yellow rows visible?
Bright01Author Commented:

Thanks for hanging in here.......and "apologies" for the confusion.

Answer1:  I meant "double-click" for all of them.

Answer2:  When you double click the blue row (black box cell) AND there is a CHECK (not ? or X),  then the green rows under that blue row are displayed (1 level, no yellow rows...which would be 2 levels removed).  When you double click the green row (black box cell) AND there is a CHECK (not ? or X), then the yellow rows (if they exist) are displayed.  NOW, If you double click on the green row again, and the check changes to something else or blank, the yellow rows retract and are not seen.  Same for the blue row.  Double click the blue row and if it is not a CHECK, no yellow or green rows should be visible.   When you CLEAR ALL, only the Blue rows should show since nothing is checked.

It's like going down a outline and back by double clicking the primary row with a CHECK.....but only for that set.  The next blue row or set is dependent on double clicking to a CHECK and then it works for that set and subset the same way.

I hope that makes sense.

Martin LissOlder than dirtCommented:
Let's see if I've got it.

If a cell in column "C" becomes a check, display all the green rows that belong to the set.
If the same cell becomes not a check, retract all the green and yellow rows that belong to the set.

If a cell in column "D" becomes a check, display all the yellow rows that belong to the set
If the same cell becomes not a check, retract all the yellow rows that belong to the set.

If a cell in column "E" becomes a check, don't display or retract any rows in the set (in other words don't do anything)
If the same cell becomes not a check, don't display or retract any rows in the set (in other words don't do anything)

If any of the above isn't correct, please correct my words rather than restating what you said before.
Bright01Author Commented:
I think you've got it! The only thing I would caution on is that as you know I'm going to be expanding the number of rows. I'll need some direction on how to do that from you. Thanks again.

Bright01Author Commented:
Also Martin, the only time that all Rows should be displayed without any checkmarks is as you cycle through the display macro.

Martin LissOlder than dirtCommented:
After half a dozen false starts I think this does it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Martin..... works brilliantly!  I've been able to integrate the work you did into my production model and it works!  One last little question.  If I want to call a macro, that's in the WS code (not in a module), but I want to call it from code in a Module, what is the one line I use to do so?

For example........

Call Sub Contract_all()

Is it that simple to all a subroutine from another subroutine?  I'm trying to get the model to reset to only the blue Questions (level 1) when you clear the model.......

Bright01Author Commented:
Outstanding work on a challenging set of Macros in a complicated Workbook!  Martin, you excel (no pun intended) at dealing with complicated, often ill defined, requirements and seem to produce outstanding work!  You are truly a pro - EE guru/master.  This was a tough one .... and you not only stuck with it, but you helped redefine how to do it in a way that made the solution "elegant" and simplified.  You are a pleasure to work with and thank you very much for an outstanding job.  

Martin LissOlder than dirtCommented:
If the sub is not one of the built in Worksheet events like Worksheet_Change, or Workbook events like Workbook_Open then just change the declaration of the sub from Private to Public. For example change Private MySub() to Public MySub(). Otherwise if the code is in the worksheet or workbook, cut the code that's in the event (not the event itself) and paste it into a new Public sub in a module, and call the new sub from the event.
Martin LissOlder than dirtCommented:
You're welcome as always.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.