I have a workbook with 131 worksheets in it (one for each inventory item). It is a terrible design, but it’s a non-trivial task to make it right, so I haven’t done it yet. Instead, I’m making little macros to make working in this monstrosity livable. I have a macro to build a table of contents, sort the sheets, roll forward some values from one month to the next, and that sort of thing.
The table of contents seemed like a good idea at the time. With that many sheets, it’s hard to navigate around. Now I click the ‘Go to TOC’ button, then click the hyperlink for the sheet I need. That’s much better than finding the desired sheets in more conventional ways. But it’s still not good enough.
For every finished good inventory item, I can have several sub-assemblies. In the case where I have, say, two sub-assemblies, I’m moving back and forth between three sheets an awful lot. Too much to be going back to the TOC every time. To make it a little easier, I created a right click menu with the 10 most recently visited sheets.
I use the Workbook_SheetDeactivate event to add sheets to the list. I can’t delete sheets very easily because of this issue, so I disable that particular commandbarbutton and delete it on the next pass.
There’s probably some problems in here I haven’t thought of, so if you plan on using this code, do so at your own risk.
Dim ctl As CommandBarButton
Dim cbp As CommandBarPopup
‘Get sheets control if it exists
On Error Resume Next
Set cbp = Application.CommandBars(“Cell”).Controls(“Sheets”)
On Error GoTo 0
‘Create sheets control if it doesn’t exits
If cbp Is Nothing Then
Set cbp = Application.CommandBars(“Cell”).Controls.Add(msoControlPopup, , , , True)
cbp.Caption = “Sheets”
End If
‘Delete sheet from list if exists
DeleteFromList Sh.Name
‘Add sheet to top of list
With cbp.Controls.Add(msoControlButton, , , 1)
.Caption = Sh.Name
.OnAction = “SelectPreviousSheet”
End With
End Sub
Public Sub SelectPreviousSheet()
Dim sName As String
On Error Resume Next
sName = Application.CommandBars.ActionControl.Caption
ThisWorkbook.Sheets(sName).Activate
On Error GoTo 0
‘If the sheet was renamed or deleted, remove from list
If Err.Number <> 0 Then
DeleteFromList (sName)
End If
End Sub
Public Sub DeleteFromList(sSheetName As String)
Dim ctl As CommandBarButton
Dim cbp As CommandBarPopup
Set cbp = Application.CommandBars(“Cell”).Controls(“Sheets”)
‘Disable “deleted” sheet because you can’t delete it yet
For Each ctl In cbp.Controls
If ctl.Caption = sSheetName Then
ctl.Enabled = False
ElseIf Not ctl.Enabled Or ctl.Index > 10 Then
ctl.Delete
End If
Next ctl
End Sub
Public Sub ResetPreviousSheetList()
On Error Resume Next
Application.CommandBars(“Cell”).Controls(“Sheets”).Delete
End Sub
Instead of a right click, I should have used a userform and listbox approach. Then I wouldn’t have to rely on the mouse so much.
Why not create a command bar? (oh yeah, because Excel 2007 doesn’t like them much :-( )
This sounds like a job for my GoBack tool:
http://www.jkp-ads.com/download.asp#goback
I use a toolbar with 3 buttons on it that is specific to the Workbook.
It allows you to move 1 sheet to the left, or 1 to the right or to go “Home” (first sheet in workbook”), it works on the sheets where the .Visible property is true.
I like your approach though, so thanks for sharing it.
Can’t think of a good way to do this, in the 3 mins it’s taken me to read the post!
I think I would have gone for a tool bar rather than right menu for this one – its not related to a cell action and it a bit of a big list to pop up.
I think I would try and put the Inventory items in to logical groups if that’s do able, or maybe group by first letter?
What about a task pane?
What about that addin thing you posted about a while back?
Ross, I also wonder every day why there’s no “worksheet pane” in Excel. That little bar at the bottom of the screen is only okay to work with as long as you have no more than three sheets, called “Sheet1?, “Sheet2?, and “Sheet3?.
I personally use a userform with a listbox in my addin, but I would really love to make that modeless to keep it floating on the screen while I work. However, all attempts to have the form update its listbox as I work with my sheets (e.g., switch sheets with the CTRL+PgUp and CTRL+PgDn keys) have failed. So if anyone uses a modeless form, I’d be curious.
Daniel
I use a commandbar, easier than a userform. Works a treat (but not with Excel 2007 of course).
I can’t think of a good reason to have this on the right click menu. It’s the menu I abuse the most.
Re logical groupings: The best way to do this for my spreadsheet would be to find the related components and put them on the userform/taskbar rather than a historical record. I should go up and down the production chain and list it’s parent, siblings, self, and children, in that order. Sounds like a lot of work.
Daniel
>>…there’s no “worksheet pane” in Excel.
well can can access the build in command bar from any com lang, so it’s easy enough to roll you own? (I’m working on a way to get it from pure VBA, but it not a very good picture!)
Also I thought there was a sort of popup list thing built in to the tabs bit – or am I on drugs?
>>…use a userform with a listbox in my addin, but I would really love to make that modeless to keep it floating on the screen while I work…
sounds like it should be doable, I’m properly over looking something?
>>Re logical groupings:
What are part numbers for? there must already be categorised, you need to network Dick, or at lest send an e-mail to procurement! :-)
Something I have built for a customer was the following:
They have this huge (legacy) system for reporting purposes. In there -scattered accross quite some worksheets- were various input areas. They were hard to find and they tended to forget skip some during data entry phase, thus producing nonsense reports.
I named each input area Input.Area1, Input.Area2, …
I inserted a hidden worksheet which lists the input area range names, next to that a description of the area and next to that a status column with either true or empty. I also added a commandbar with 5 controls: a dropdown which lists the descriptions to pick from, a previous and next button, a “Done” button and a button to reset all input areas back to “to be done”. Clicking “Done” will remove the input area from the pick list and from the previous/next list. The order of the areas is determined by the order in the hidden table.
It proves very useful in keeping track of whether all data has been entered in a big workbook.
If the sheets have an ‘Item Name’ cell in a predictable place, then you can use the TooltipText property to display a more informative name when the mouse is over sheet name on your menu.
Ross, you’re right, I just discovered that there *is* a sheet list built into Excel… Right click on those little “Forward-backward” buttons next to the sheet tabs, and it pops up. How could I have missed that, I use Excel for hours every day… Amazing!
Well there you go Daniel, there’s loads of stuff that I’ve forgotten about Excel over the years, then bang, someone mentions it on a blog, and you remember it.
You could store often-used sheets as custom views, and then use the custom views dialog box to select them (which can be done without the use of the mouse if you like). An added bonus is that you can then save multiple views of the same spreadsheet in the case that you’ve filtered or hidden rows. For instance, in a workbook I use, I have a ‘Customer information’ customer view, and then have ‘Customer info – customer x’ view for any specific customers I might want to check on often.
The downside is that this can be slow, as it not only takes you to the sheet concerned, but then does a whole heap of filtering/unfiltering…even if none is needed.
Sometimes I also use named ranges for navigation. I start any functionality-based named ranges with ZZ_ so that they appear at the bottom of the list.