Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

Expanding Outline and showing Levels

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
Bright01
Asked:
Bright01
  • 5
  • 4
1 Solution
 
Rory ArchibaldCommented:
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
 
Bright01Author Commented:
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
 
Bright01Author Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rory ArchibaldCommented:
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
 
Bright01Author Commented:
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
 
Rory ArchibaldCommented:
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
 
Bright01Author Commented:
OK....but I'm getting a slight "debug" error.  WB attached.

B.
D--Data-Data-Temp-Macro-for-Outline-Expa
0
 
Rory ArchibaldCommented:
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
 
Bright01Author Commented:
Like a charm!  Thank you very much!  Great work without much to go on.

B.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now