Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Showlevels (Excel VBA)

    I have some code that shows data to a certain level in an Excel sheet...

    ExcelObject .ActiveSheet.Outline.ShowLevels RowLevels:=4

    4 used to be the second to last level of detail, 5 would display everything in the report. However, the report being produced is now changing regularly & 4 is not always the correct level to display.

    What I need to do is determine the second to last level of detail in the report & ShowLevels to this .... eg;

    Dim iLevels as Integer

    iLevels = ExcelObject.ActiveSheet.Outline.{GetLevels}
    ExcelObject .ActiveSheet.Outline.ShowLevels RowLevels:=iLevels

    Is there anything like this, or am I in dreamworld here....?!

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Showlevels (Excel VBA)

    How about this:

    <pre>Sub test()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    MsgBox GetLevels(oSh)
    End Sub

    Function GetLevels(oSh As Worksheet) As Integer
    Dim iLevels As Integer
    iLevels = 0
    Do
    oSh.Outline.ShowLevels iLevels + 1
    If oSh.Cells.SpecialCells(xlCellTypeVisible).Count <> oSh.Cells.Count Then
    iLevels = iLevels + 1
    Else
    Exit Do
    End If
    Loop
    GetLevels = iLevels
    End Function
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Showlevels (Excel VBA)

    Thanks, I'll give this a go...

Posting Permissions

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