Related Sheets Stack

In Previous Sheets Stack, I created a historical list of sheets visited on the right click menu for easier navigation. After reading the comments, I decided to change the whole approach. Now I show related sheets (which I define) in a userform.

Related Sheets

I’m dealing with inventory assemblies, so the relationship is already defined. Sometimes the active item has parents, sometimes it has children, sometimes it has siblings, and often times it has all of those. I have a couple of Access tables that define those relationships. I created a CItem custom class module to hold all of the items and created the Parent and Child relationships in that class module. Stay tuned for a post about why I know longer use SQL to construct complex queries, opting instead for class modules.

The Userform

excel userform showing related sheets

I have this non-modal form (that means you can work in Excel without dismissing it) with a a one-column listbox. The indents are just spaces I added to the start of the string for cosmetic purposes. They don’t mean there’s another column. When Goto is clicked, that sheet is activated and the Workbook_SheetActivate event fires to repopulate the listbox. Originally I didn’t have a Goto button. I just used the Listbox click event to activate a new sheet. But the listbox would go blank, even though I could “read” its contents using List, ListCount, and the like. I couldn’t figure out why, so I added the button and it works.

Populating the Listbox

I have the CItem class (and a CItems collection class) that holds every item. Each instance also holds references to its parents and children. The global variable gclsItems keeps a CItems instance in scope all the time.

Public Sub UpdateListBox()
   
    Dim clsActive As CItem
    Dim i As Long, j As Long
    Dim clsParent As CItem
    Dim clsChild As CItem
    Dim aList() As String
    Dim lSheetCount As Long
   
    Me.lbxSheets.Clear
   
    On Error Resume Next
        ‘Get the CItem instance if available
       Set clsActive = gclsItems.ItemByName(ActiveSheet.Name)
    On Error GoTo 0
   
    ‘Some sheets are not inventory (like table of contents), so ignore them
   If Not clsActive Is Nothing Then
        ‘Add all the parents
       For i = 1 To clsActive.ParentCount
            Set clsParent = clsActive.Parent(i)
           
            lSheetCount = lSheetCount + 1
            ReDim Preserve aList(1 To lSheetCount)
           
            If clsParent.HasSheet Then
                aList(lSheetCount) = clsParent.SheetName
            Else
                ‘Show the parent even if no sheet
               aList(lSheetCount) = clsParent.Name & “**”
            End If
           
            ‘Add all the parent’s children
           For j = 1 To clsParent.ChildCount
                Set clsChild = clsParent.Child(j)
               
               ‘Don’t show children that don’t have a sheet
               If clsChild.HasSheet Then
                    lSheetCount = lSheetCount + 1
                    ReDim Preserve aList(1 To lSheetCount)
                    aList(lSheetCount) = ”  “ & clsChild.SheetName
                End If
               
            Next j
        Next i
       
        ‘If the active item is a parent, add its children
       If clsActive.ChildCount > 0 Then
            lSheetCount = lSheetCount + 1
            ReDim Preserve aList(1 To lSheetCount)
            aList(lSheetCount) = clsActive.SheetName
           
            For i = 1 To clsActive.ChildCount
                Set clsChild = clsActive.Child(i)
                       
                If clsChild.HasSheet Then
                    lSheetCount = lSheetCount + 1
                    ReDim Preserve aList(1 To lSheetCount)
                    aList(lSheetCount) = ”  “ & clsChild.SheetName
                End If
               
            Next i
        End If
       
        ‘Sometimes aList is empty
       On Error Resume Next
            Me.lbxSheets.List = aList
       
    End If
   
End Sub

At first, I would only show a parent or child if a sheet existed. However, that made the form look strange if the parent didn’t have a sheet. There would be indented children with nothing above them. So I changed it to show the parent, but identified it as not having a sheet by appending two asterisks. I add a couple of spaces to the front of children, but use the Trim function later when activating the sheet.

I have some proprietary information in my workbook, so I can’t make it available. However, I threw together another workbook with the code it in. I uses cities and states for the parent-child relationship, so it’s not quite as cool – there are no multiple parent situations. But if you want to inspect the code, at least you’ll have it. Download RelatedSheets.xls.zip

Posted in Uncategorized

7 thoughts on “Related Sheets Stack

  1. I would probably just have allocated cell A1 in each sheet to hold the sheet list as a list of data validation items, populated when the sheet was selected, and going to a sheet if an item is selected. I find userforms are quite a lot of extra work.

    As far as identifying parentage, I would probably name the sheets in such a way that VBA could run through all the sheets and identify their relationship to the active sheet, just from the names. This makes the code extremely simple and robust.

  2. I remember spending a great deal of time trying to get the tab strip on the bottom of an Excel Window to either ‘double up’ into two or more rows, or ‘dock’ to the side, instead of hiding tabs.

    I eventually ended up building a menu (NOT a ribbon!) that dropped down the sheet names.

  3. Dick,

    Maybe an easier way (however it doesn’t account for the sub sheets) is to use the built-in Watch Window. Double-clicking an item in the Watch window takes you there. The code does not show the watch window. Use the built-in button or the Tools | Forumula Auditing menu to do that.
    Code goes in the ThisWorkbook module…

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ‘Jim Cone – Portland, Oregon USA – April 2009
    Dim rng() As Range
    Dim N As Long
    Dim lngCount As Long
    Const HOW_MANY As Long = 10

    On Error Resume Next
    lngCount = Application.Watches.Count – 1
    On Error GoTo 0

    If lngCount > (-1) Then
    ReDim rng(0 To lngCount)
    End If

    For N = 0 To lngCount
     Set rng(N) = Application.Watches.Item(N).Source
    Next ‘N
    Application.Watches.Delete
    Application.Watches.Add (Sh.Range(“A1”))

    If Scrabble(rng) Then
    For N = 0 To UBound(rng)
       Application.Watches.Add rng(N)
    Next
    Erase rng
    End If

    If Application.Watches.Count > HOW_MANY Then
       Application.Watches.Item(HOW_MANY).Delete
    End If
    End Sub

    Function Scrabble(V As Variant) As Boolean
     On Error Resume Next
     Scrabble = (UBound(V, 1) >= -2147483647) ‘lower limit for a Long
    End Function

    ‘–

    Jim Cone

  4. Further…
    I used the code tags but “>” appeared in the Scrabble function.
    Replace that with the symbol for “Greater than”
    ‘–
    Jim Cone

  5. Further, further…
    I am always a step behind. Maybe this will work:
    Replace all instances of > with “>”
    ‘–
    Jim Cone


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.