Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,508
    Thanks
    3
    Thanked 143 Times in 136 Posts
    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...
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  2. #17
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    Compile error: Ambiguous name detected: GetLevel.

  3. #18
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,508
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Do you have the earlier version sitting somewhere in the same file?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #19
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    Yes, but I imported the latest as SetIndentA.

  5. #20
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,508
    Thanks
    3
    Thanked 143 Times in 136 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #21
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    Ah so. Will rem out and try tomorrow. Thanks a bunch for hanging in on this!

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

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

  9. #24
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thank you Andrew, I will give that a try.

Page 2 of 2 FirstFirst 12

Posting Permissions

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