Results 1 to 5 of 5

VBA Outline Levels: Set Range in Function

Threaded View

briskie VBA Outline Levels: Set Range... 04-11-2024, 01:15 PM
ByteMarks Re: VBA Outline Levels: Set... 04-12-2024, 05:15 AM
briskie Re: VBA Outline Levels: Set... 04-12-2024, 06:09 PM
ByteMarks Re: VBA Outline Levels: Set... 04-13-2024, 09:21 AM
briskie Re: VBA Outline Levels: Set... 04-15-2024, 06:57 PM
  1. #1
    Registered User
    Join Date
    03-10-2024
    Location
    Berlin
    MS-Off Ver
    mac2016
    Posts
    94

    VBA Outline Levels: Set Range in Function

    Found this function on mrexcel:
    https://www.mrexcel.com/board/thread...evels.1061148/
    It lets you switch between your outline levels:
    Option Explicit
    Public Function LowestRowGroupLevelDisplayed( _
          Optional ByVal Worksheet As Worksheet _
       ) As Long
       Dim Row As Range
       Dim LowestLevel As Long
       If Worksheet Is Nothing Then Set Worksheet = ActiveSheet
       For Each Row In Worksheet.UsedRange.Rows.EntireRow
          If Not Row.Hidden Then
             If Row.OutlineLevel > LowestLevel Then LowestLevel = Row.OutlineLevel
          End If
       Next Row
       LowestRowGroupLevelDisplayed = LowestLevel
    End Function
    
    Sub testGRp()
        If (LowestRowGroupLevelDisplayed = 1) Then
            ActiveSheet.Outline.ShowLevels RowLevels:=3
        Else
            ActiveSheet.Outline.ShowLevels RowLevels:=1
        End If
    End Sub
    The code expands/collapses all your groups at once, though.
    Might there be a way to add an "input line" to set certain ranges of rows and columns?
    To say e.g. "rows 4-6" or "columns K-L"?
    (I haven't used any functions yet, I'm a bit overwhelmed.)

    Right now, I hide/unhide specific rows and columns with a "Flip Switch":
    ' Flip alpha
    Sub Flip_alpha()
       Dim Title As String
       Dim i As Integer
       Title = Range("A1")
       i = 0
       Do While Sheet1.Range("A1").Offset(0, i).Value <> Title
          i = i + 1
       Loop
       Sheet1.Range("A1").Range("A4:A8").Select
       ' Set Rows
       Selection.EntireRow.Hidden = Not Selection.EntireRow.Hidden
    '   ' Set Columns
    '   Selection.EntireColumn.Hidden = Not Selection.EntireColumn.Hidden
    End Sub
    This works just fine, but takes quite some time to build.
    Set range, add rectangle (transparent) as button, create click, assign "call" to rectangle.
    For every group. And in the end, it's not even the real deal, e.g. it leaves certain cells "selected" (screenshot).
    And for now, this only allows me to switch between "Level closed" and "Level max expanded".
    I'd have to have extra sets of macros for every level, right?
    1) Set/Select range 2) Add rectangle 3) Create click 4) Assign to rectangle

    I've attached the workbook, all codes included.
    The merged cells with the double arrows are all working flip buttons, expanding and collapsing.

    So now I wonder, if that function can be tweaked?
    Might that result in less code?

    Thank you for any help!
    Last edited by briskie; 04-11-2024 at 10:56 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Rolling Stock Levels for Assembly Component Levels
    By Keith650 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-27-2024, 04:30 PM
  2. Combining outline levels and autofilters
    By Kafrin in forum Excel General
    Replies: 1
    Last Post: 04-04-2011, 05:04 AM
  3. Determining number of Outline levels
    By Keith in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2006, 03:20 PM
  4. [SOLVED] How to Use Group and Outline function when locked sheet
    By Denis in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 AM
  5. How to Use Group and Outline function when locked sheet
    By Denis in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 AM
  6. How to Use Group and Outline function when locked sheet
    By Denis in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] outline...hide levels
    By icebreaker914 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2005, 04:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1