Solved

Expanding Outline and showing Levels

Posted on 2015-01-12
9
48 Views
Last Modified: 2015-01-14
EE Pros,

I have a WS that has a macro that expands (rotates) through different levels of Outline.  What I'm trying to do is get the macro to not only expand through the levels when the button is pressed, but to display a name for each level.  I have worked on this for several hours and can't seem to get the link between the name of the level and the action.

Thank you in advance.

B.
Outline-Expand-Case.xls
0
Comment
Question by:Bright01
  • 5
  • 4
9 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40544317
Can you elaborate on what you want to happen? Your code doesn't make a lot of sense as it stands - are parts of it missing?
0
 

Author Comment

by:Bright01
ID: 40544400
Rory,

There are no parts missing; however you can see I was trying to figure out what was referencing what in order to get the results.

As for the results I'm looking for;

1.) When you fire the macro from the button, it should change the outline level.
2.) The outline levels are named (e.g. Functional Areas, KPIs and Questions) and the name should show up on the button as the macro is fired.

This produces a way of viewing the data as the outline expands and then cycles back.

Does that make sense?

Thank you again for jumping in here.

B.
0
 

Author Comment

by:Bright01
ID: 40547512
Rory,

Greetings.  I found another part of the model I'm trying to re-create and in addition to what I sent you, it had a "Functions" module.  I have now added it.  This original model was for a two tiered outline.  The one in the model I'm trying to get to work with you is a three tiered model.  Based on what I have sent, I hope you can make sense of what it is suppose to do.  The assigned names are not correct so I continue to get an error, but hopefully you can follow the logic and make several simple corrections or additions.

Again, thank you for the help.

B.
D--Data-Data-Temp-Outline-Expand-Case-v2
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40548476
Do you mean something like this:

Option Explicit
Const SumBtnCaps As String = "Functional_Areas,KPIs,Questions"

Private Sub CmdSummary_Click()
    Dim vMatch
    vMatch = Application.Match(Cmdsummary.Caption, Split(SumBtnCaps, ","), 0)
    If Not IsError(vMatch) Then
        Me.Outline.ShowLevels RowLevels:=vMatch
        Cmdsummary.Caption = Split(SumBtnCaps, ",")(vMatch Mod 3)
    End If
End Sub

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Bright01
ID: 40548639
Wow!  That's exactly what I was looking for!  That was outstanding diagnosis and work!  I didn't give you much to go on but you figured it out.  Thank you very much.

One question; if I want to change the order of when you see the results on the button, do I simply change the order on this line?

Const SumBtnCaps As String = "Functional_Areas,KPIs,Questions"

Thanks again,

B.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40548657
No - the code just matches the current caption against the list and uses that position for the RowLevels argument. If you need a different order, you'll need a slightly different approach; you can use two constants like this as long as you keep them in sync:

Option Explicit
Const SumBtnCaps As String = "Functional_Areas,KPIs,Questions"
Const ShowLevels As String = "1,2,3"

Private Sub CmdSummary_Click()
    Dim vMatch
    vMatch = Application.Match(Cmdsummary.Caption, Split(SumBtnCaps, ","), 0)
    If Not IsError(vMatch) Then
        Me.Outline.ShowLevels RowLevels:=CLng(Split(ShowLevels, ",")(vMatch))
        Cmdsummary.Caption = Split(SumBtnCaps, ",")(vMatch Mod 3)
    End If
End Sub

Open in new window

0
 

Author Comment

by:Bright01
ID: 40548695
OK....but I'm getting a slight "debug" error.  WB attached.

B.
D--Data-Data-Temp-Macro-for-Outline-Expa
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40548738
Apologies, I forgot the 0 basing:

        Me.Outline.ShowLevels RowLevels:=CLng(Split(ShowLevels, ",")(vMatch))

Open in new window

should be:

        Me.Outline.ShowLevels RowLevels:=CLng(Split(ShowLevels, ",")(vMatch - 1))

Open in new window

0
 

Author Closing Comment

by:Bright01
ID: 40548751
Like a charm!  Thank you very much!  Great work without much to go on.

B.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now