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
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.
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
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.
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.
Have you tried using a TreeView control for this?
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…
‘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
Further…
I used the code tags but “>” appeared in the Scrabble function.
Replace that with the symbol for “Greater than”
‘
Jim Cone
Further, further…
I am always a step behind. Maybe this will work:
Replace all instances of > with “>”
‘
Jim Cone
The “Previous Page” links on tag-archive pages do not work, can you please fix them? Thanks!
Example:
http://www.dailydoseofexcel.com/page/2/?category_name=vba%2Fuserforms-and-controls%2F