Auto outline an indented list

Topic

New Reply

I have a very long list that uses levels of indention to show relationships among terms. I would like to convert this list to an outline so I could view only the top level terms, and expand those of interest, one level at a time. For example, the way Windows Explorer or MS Outlook allows expanding/collapsing folders.

I haven’t yet received the list, so I’m not exactly sure how the indention was accomplished. I’ve found that if the indention was done using tabs, in Word I can make all the unindented lines Style Heading 1, Find & Replace ^t^t^t with Style Heading 4 for example, then Find & Replace ^t^t^t with nothing to eliminate 3 consecutive tabs, do this for ^t^t > H3 and ^t > H2, then switch to Outline view. In that view, I can collapse minor headings, but when I expand a major heading, it expands ALL the headings beneath it–not what I want. I just want to show the next level down, not all levels below.

Excel’s Grouping feature seems to operate the same way, expanding all items beneath a grouped item when I hit the +.

Any ideas would be welcomed.

Reply | Quote

Replies

Is your issue with the conversion to outline levels or the fact that Word expands all the sub-subheadings when you issue the expand command?

Perhaps you could try Microsoft Project instead – the treeview there remembers the state of the subheadings when you collapse the levels so when you expand a parent, the children expand but the grandchildren will only be visible if they were visible when their parents were hidden.

I just tried Excel 2007 and if it seems to remember the last state of the sub-subheadings so I can expand one level at a time if the sublevels were collapsed earlier.

Reply | Quote

Is your issue with the conversion to outline levels or the fact that Word expands all the sub-subheadings when you issue the expand command?

Perhaps you could try Microsoft Project instead – the treeview there remembers the state of the subheadings when you collapse the levels so when you expand a parent, the children expand but the grandchildren will only be visible if they were visible when their parents were hidden.

I just tried Excel 2007 and if it seems to remember the last state of the sub-subheadings so I can expand one level at a time if the sublevels were collapsed earlier.

Andrew, thanks for the reply. Re 1st para: Both. The conversion doesn’t have to occur in Word, but that would be nice.

Re 2nd para: That would work for me, but of course it requires MS Project, which isn’t on some user CPUs.

Re 3rd para: Hmmm. My first try didn’t give those results. I will try an expanded list.

Reply | Quote

Once I found the Outline Settings dialog and unchecked the Summary rows below detail, it worked as expected. Thanks for prompting me to try again.

Next I need VBA to apply a Group to all entries with 1 tab or space, then 2 tabs or spaces, then 3 tabs or spaces. I’m thinking along these lines:
1. Select the list. Replace each leading tab/space with a #, up to an alpha character. This will give a list with the number of #s indicating the indention. (Since terms may contain spaces, only do this at the front end of a term.)
2. Select the list. Find each ### term. Replace the ### with $$$.
3. Select the list. Find each ## term. Replace the ## with %%.
4. Select the list. Find each # term. Replace the # with *.
2. Select the list. Find each * term. Select its row. Group it. Select the list. Keep doing it until Excel says it can’t find any data. Click OK.
3. Select the list. Find each %% term. Select its row. Group it twice (Alt+Shift+right arrow, twice). Select the list. Keep doing it until Excel says it can’t find any data. Click OK.
4. Select the list. Find each $$$ term. Select its row. Group it three times. Select the list. Keep doing it until Excel says it can’t find any data. Click OK.
5. Select the list. Replace all *, %%, and $$$ with nothing.

Some or most of this is beyond my VBA skills. I could start by creating macros, but am unsure how to cycle through the list. Am I on the right track? Can you give me any VBA pointers?

Thanks a bunch for helping with this task. I have many term lists, with sometimes hundreds of terms per list.

Reply | Quote

Moderator: Please switch this thread to Visual Basic for Apps, as it has morphed into a VBA discussion. {Could I have done this myself?}

Attached is a workbook with sheets that show:
The problem
Term list examples
Macro approach
VBA for Level2 indentions
Results

The VBA sheet has areas where I need help. Any suggestions will be gratefully received.

Reply | Quote

My approach is a little different. I assume that you now want to do this in Excel but this method starts in Word.
Step 1. Select tabbed text in Word and convert to a table using tabs
Step 2. Copy the table into Excel at Cell B1 leaving column A empty (similar to your worksheet Termlists – List with Tabs That Signify Indention Level)
Step 3. Select the cells containing all the top level headings down to the bottom of the content eg B1:B200
Step 4. Run SetIndent macro
Step 5. Select the Column A numbers (created by the macro SetIndent)
Step 6. Run SetGrouping macro
Step 7. Delete the Columns you don’t want anymore

Code:

Sub SetIndent()
  Dim aCell As Range
  If Selection.Columns.Count > 1 Then Exit Sub
  For Each aCell In Selection
    GetLevel aCell
  Next aCell
End Sub
Function GetLevel(aCell As Range) As Integer
  Dim x As Integer
  For x = 0 To 7
    If aCell.Offset(0, x).Text  "" Then
      aCell.Value = aCell.Offset(0, x).Text
      aCell.IndentLevel = x
      GetLevel = x
      aCell.Offset(0, -1).Value = x
      Exit For
    End If
  Next
End Function
Sub SetGrouping()
  Dim aCell As Range
  For Each aCell In Selection
    aCell.Rows.OutlineLevel = aCell.Value + 1
  Next aCell
End Sub

Reply | Quote

Thanks, Andrew. I will try this.

The reason for Excel: I was able to do the expansion/contraction + / – business there, at any desired level (not all expanded at once). Can something similar be done in Word?

Reply | Quote

I don’t think that Word remembers the collapsed sub-heading levels so you might be best to stay with Excel unless you want to load the structure into a Treeview control in a dialog box.

Reply | Quote

Andrew,

When I run the SetIndent macro, it fills Col A with the indention level, and copies cells in Col C-E into Col B, with leading spaces to show indention, but it never stops and I must use TaskManager to shut down Excel. (Excel 2007 SP 1). “The object invoked has disconnected from its clients.” (What does “Dim iLev As Integer” do?)

Same is true with the SetGrouping macro (in it, should “Dim aRng As Range” be “Dim aCell As Range”? However, this didn’t change the behavior.).

Reply | Quote

My instructions were misleading, I shouldn’t have told you to select the column in step 3 but to select only the range that you require to be processed. I would do this by scrolling down to the bottom of the indented list and clicking the last cell and then doing a Ctrl-Shift-UpArrow until you get back to the top. The last cell may not have something in it if there is an indented bit of text to its right.

You might like to select a smaller region of say 20 cells to run the first macro and see that it works properly. I had noticed that Excel was slow to process a small selection so if your selection is huge then I could imagine it taking a lot longer.

The iLev as Integer does nothing – it was a remnant from earlier thinking when I was working out how to get this to work and you should delete it.

Yes, the Dim aRng as Range was wrong but you managed to catch that bit already.

I will go back and edit my errant post to correct these problems in case someone else tries the same code in the future.

Reply | Quote

Well, I still must be doing something wrong, or didn’t explain what I want. I guess you need to give me step-by-step instructions including cell references.

For example, say I copy TermLists!A5:D21 and paste them in a NewSheet at B1. (NewSheet!B1:E17)

Then, to select the range that I desire to be processed, I select NewSheet!E7:E17 (last cell and Ctrl+Shift+Up arrow). Then I run SetIndent. I now have D7 and D13 = 0.

Select D4:D17, run SetIndent, C4:C7, C12:C13, and C17 now = 0.

Select C2:C17, run SetIndent, B2:B7, B9:B13, and B15:B17 now = 0.

Select B1:B17, run SetIndent, A1:A17 now = 0.

When I now try to run SetGrouping, it bombs, Type Mismatch.

So I still don’t get it.

Reply | Quote

Run SetIndent on the selection B1:B17 (each loop looks off to the right to get the text and adds an indent number in the corresponding A cell)
then
Run SetGrouping on selection A1:A17
then
delete columns A, C, D, E

Reply | Quote

Thanks for the additional explanation. This works but the results expand all items beneath a grouped item when I hit the +.

E.g., the + for Aaaaaaaaa expands ALL terms down to Ggggggg.

See the Results sheet outline. Each level below Aaaaaaaaa is capable of its own expansion/contraction. If I collapse Fffffffffff and Cccccccc, then collapse Aaaaaaaa, then expand Aaaaaaaa, all I see are Bbbbbb and Cccc.

Rationale: If I’m at Aaaaaaaaa, and I only want to see the next level down, all I want is Bbbb and Cccccc, NOT Ddddddd, Eeeeeee, Ffffff, and Ggggggggggg.

I can achieve close to the desired results in your schema by clicking the Outline numbers 2, 3, or 4, BUT 2 for example opens ALL the level 2s, which would blow up a large list way too big.

Reply | Quote

I don’t know why Excel is not automatically grouping based on the outline level. I will have another play with this later on and post back if I can work out why we are not getting the sublevel icons which allow you to expand and contract each group.

Reply | Quote

Andrew, your VBA DOES work. That is, as long as I remember to switch to the Data view and do the following every time I start over on a new sheet!

Once I found the Outline Settings dialog and unchecked the Summary rows below detail, it worked as expected.

Reply | Quote

Well done for working that out. I couldn’t solve it myself and it took me ages just to work out how to show the dialog you discovered. I have streamlined the code to include that little gem and avoid the need for many of the extra steps. Try this version with the selection of just the first column of cells (eg B1:B17). You don’t need the blank column in front now either as it skips this step now.

Code:

Sub SetIndent()
  Dim aCell As Range, x As Integer
  If Selection.Columns.Count > 1 Then Exit Sub
  For Each aCell In Selection
    x = GetLevel(aCell)
    aCell.Rows.OutlineLevel = x + 1
    If x > 0 Then aCell.Offset(0, x).Clear
  Next aCell
  With ActiveSheet.Outline
    .AutomaticStyles = False
    .SummaryRow = xlAbove
    .SummaryColumn = xlLeft
  End With
End Sub
Function GetLevel(aCell As Range) As Integer
  Dim x As Integer
  For x = 0 To 7
    If aCell.Offset(0, x).Text  "" Then
      aCell.Value = aCell.Offset(0, x).Text
      aCell.IndentLevel = x
      GetLevel = x
      'aCell.Offset(0, -1).Value = x
      Exit For
    End If
  Next
End Function

Now if I could just work out how to stop Excel insisting on celebrating by beeping for every row it processes…

Reply | Quote

Compile error: Ambiguous name detected: GetLevel.

Reply | Quote

Do you have the earlier version sitting somewhere in the same file?

Reply | Quote

Yes, but I imported the latest as SetIndentA.

Reply | Quote

The function is duplicated as well then, you will also need to change the name of the function to GetLevelA in the three places it appears.

The quicker alternative is to rem out the other code supplied earlier.

Reply | Quote

Ah so. Will rem out and try tomorrow. Thanks a bunch for hanging in on this!

Reply | Quote

Finally got the list. 1,140 terms, 6 levels of indention. Your code worked great. After collapsing, there were 14 high-level terms, making the list MUCH easier to search and explore, versus paging down 35 times and losing track of major terms on the way.

One question: Currently, after the macro runs, the indention level is shown with one space prior to each term for each level after 0. If I wanted to add more spaces or a tab in front of each term, to make the indention more pronounced, how would I modify the code?

Reply | Quote

You can modify the line in the function
aCell.IndentLevel = x
to be a multiple of x (where x is the outline level)
aCell.IndentLevel = 2 * x

If the doubled indent is still too small then you might try 3 * x or even 4 * x. I thought that there was a maximum indentlevel of 15 but Excel 2007 seems to allow me to go a lot higher than that. If you find the code errors on larger multiples then it might be because the indentlevel has exceeded the maximum available ie 4*6 = 24.

Reply | Quote

Thank you Andrew, I will give that a try.

Reply | Quote