Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    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 Lockton, Chrysalis Design, Melbourne Australia

  4. #3
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Andrew Lockton View Post
    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.

  5. #4
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

  6. #5
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.
    Attached Files Attached Files

  7. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    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
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #7
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    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?

  9. #8
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  10. #9
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.).

  11. #10
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  12. #11
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    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!A521 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 D417, 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.

  13. #12
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    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
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  14. #13
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

  15. #14
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  16. #15
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •