Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag for United States of America

asked on

Macro Change for Reset

EE Pros,

I have a simple request.  I have two macros that do the following;

1.)  The first macro opens and closes a 2 level outline. Summary and Input (Details).

2.) The second macro presents and sequentially serves up 14 different Ranges that represent subsets within the entire Summary.  The Ranges are named Range1 through Range14 with Range 14 being the entire set of Details.  

Here's what I need.  I need to reset the first Macro so that when it is fired, it will be fired when all of the details are open.  That is to say, none of the individual ranges are showing "individually".

That's it!  I'm thinking its one or two lines in the beginning of the first Macro to reset it.

Thank you in advance,


Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Summary" Then
    'Your two macros look the same to me except for the name, but
    'reguardless you would put the name of the one that you want to
    'run when the caption says "Expand" here
    CommandButton1.Caption = "Input"
    Expand_all '????
    CommandButton1.Caption = "Summary"
End If
End Sub

Private Sub CommandButton2_Click()
Const NoOfRanges = 14
Static CurrentRange As Long
Dim I As Long
Application.ScreenUpdating = False

    CurrentRange = (CurrentRange + 1) Mod (NoOfRanges + 1)
    For I = 1 To NoOfRanges
        Range("Range" & I).EntireRow.Hidden = CurrentRange <> 0
    Next I
    If CurrentRange <> 0 Then
        Range("Range" & CurrentRange).EntireRow.Hidden = False
    End If
    Application.ScreenUpdating = True
End Sub
Avatar of Faustulus
Flag of Singapore image

Would it be possible for you to post your workbook with the code in it? You can reduce, remove and substitute the data in the workbook but permit testing of code, both old and new.
Avatar of Bright01


Yes..... I hope this won't add to the confusion.  This is really a navigation problem.  If you select theBENEFITS button you will ratchet through each range (1-13)  Range 14 is the complete list.  Only when you are on Range 14 can you actually use the other button "Summary".  So what I'm attempting to do is to reset it to Range 14 before firing the Summary macro.

Make sense?

No, it didn't add to the confusion. But it didn't lift the veil, either. Sorry, I am probably standing on the hose somewhere. Please, let us forget about firing or resetting macros and concentrate on the user surface and the workflow.
My first impression is that the two buttons are confusing. How about hiding one and show it only when it is needed? Or having a third one which also hides and shows?
I think you might start off with a blank screen and two buttons: Summary and Benefits. I think these captions are already confusing because what do you want to happen when one of them is pressed? "Enter benefits"? or "View benefits"? Or perhaps "Edit benefits"?
Presuming that it is "Enter" you would start by showing #1.
Logically, #14 isn't part of the range. I wonder if it is the "Summary" except that you have another screen for a summary which doesn't appear to be linked to the buttons.
As far as I understand the workflow, if the worksheet has no entries, the Summary button would be useless and should be hidden. The other button should show as "Enter Benefits" and change to "Next" after one is entered. You might want a "Back" button, too.
On the last question (#13) the caption should change to "Finish".
On Finish the Benefits button either disappears or turns into "Edit benfits".
The Summary is shown automatically, not requring a button. But you have two different Summary screens the respective use I don't understand, unless one is for going back.

So, you have three possible scenarios on startup. The workbook may have all questions answered or none or some. Is it permissible not to answer all questions? I think you probably need a check that, perhaps, prevents the Next question from popping before the current one is answered, or prevents the summary from showing on the same condition. Perhaps you want to disallow saving while some questions remain open.

As you see, I am stuck in a groove trying to make sense of the buttons, and while you see only one thing wrong with your workflow I see many. And whereas it is clear to you that the one fault needs fixing, I would probably only come to that one after I have fixed all the others - unless it has become collateral damage by then. :-)

I'll gladly help you improve the workflow. Can we take it from there? Or does that carry you too far afield? Could you, perhaps, guide me to your requirement on the path that I have taken?
All great points!  Let me try to simplify the workflow.

By using the SUMMARY button, I want to be able to see the summary (all 13 categories compressed -- without the detail from each), with the push of one button regardless of where you are in cycling through any of the 13 ranges/individual benefits.  Push it again and it exposes all the Details for all Categories of Benefits (Range 14).

By using the BENEFITS button, I can cycle through each Benefit (all 13) one at a time.  The practical use is that when in front of a client, you walk through each benefit one at a time.  

You don't have to complete one section or category before going to another.

I don't know that you could do both without two buttons.  My original thought was to have a line or two of code in front of the Summary Macro to reset the model. Right now it won't display Summary unless you have cycled all the way through all of the Benefits and are at Range 14 displayed.

To answer specific questions;

1.) I wouldn't want to add another screen because that would add complexity and redundancy.
2.) What I start off with is either Summary or Detail.  Next I go into each Benefit individually. And what I want to do is go back to Summary anywhere in the cycle.
3.) You don't have to complete one section before moving to the next.  In fact, in many cases, the client will not answer particular sections.

Make sense?  Any clearer?
After a false start asking more questions I decided to just go ahead. The attached workbook should do what you want. Please check it out.
When it loads you see the Summary screen, regardless of how you saved. You can double-click on any item (column B only - can be modified to include more columns if you wish) to expand that item, or expand all using the button.
From either expanded view you can go forward or back to the previous or next item or to the Summary screen.
There is code in the worksheet's code sheet, the workbook's code sheet and the standard code module I called Functions. You can drag it to your project in the VBE's Project Explorer window. Copy/Paste the other two code items.
Let me know if there is some improvement you might like.
Wow!  What a creative approach!  This does exactly what I was interested in having done but in a far more eligant way.  Let me apply this to my production WB and see if I can get it to work identically (that's how I learn).  One quick question; if I want to change the button text display (e.g. from Summary / Expand Summary to Benefits / Expand Benefits), to something else?  Where in the code do you change the Text and does that implicate other parts of the Macro?

Much thanks!

At the very top of the code you have this line,
    Public Const SumBtnCaps As String = "Summary,Expand Summary"
You can change the two captions of the button to anything you like without ill effects. Just make sure that they are separated by one comma.

FANTASTIC!  It works great!  There is one very minor problem in my production copy...... when you click on "Next" once you have exposed the entire list, it takes you to the "second" range instead of the first.  If you then go back, the first range shows up.  I think it's in this line:

Under: ExpandRange
The line:  i = CurrentRange(Target) + NextRange

This takes you to the second range rather then the first range.  Hopefully this is a very minor tweek.

Thank you very much!  This is great.

I'll look at it tomorrow. From the last of the items clicking Next should take you to the Summary, as should be the case when you click Previous while on the First item. Unfortunately, it isn't quite so simple in reality. Therefore the challenge is in not allowing reality to be more complicated than the problem posed.
Thank you!  And agree..... simple is better.  With that said, I think this is a rather simple fix (again, you'd be the professional to tell me).  Just try it.  Click on the main bar and then "Next" you will see that it takes you to Range2 instead of Range1.  if you hit "Back" it will take you to Range1 but only after taking you to Range2.

It seems like a simple problem.  I'll await your verdict.  It's a great macro!  Again, much thanks,

Avatar of Faustulus
Flag of Singapore image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great job!  

I understand now.  So if I use the Activate C3 for instance as a line of code, then hit "Next", it should take me to the first Range.  And the reason why it goes to Range 2 as the first range is because the cursor is in Range 1 when it is it.

Very much appreciate your work here.   This works better then I even expected.