Improve company productivity with a Business Account.Sign Up

x
?
Solved

Expanding Outline and showing Levels

Posted on 2015-01-12
9
Medium Priority
?
62 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 

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 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

584 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