Tab Groups
Scott wishes:
G’day Dick,
My wish list would be to have the functionality to group excel tabs within a workbook. So instead of having 20+ tabs going along the bottom of the screen you could group (similar to TabScrip in VBA ) them into a common theme. Maybe group all the raw data tabs, input sheets, output reports, State summaries, Yearly reports, etc.
Now, if you have some finance VBA code that already does it……then here’s your chance to make a wish come true.
Cheers matey.
I couldn’t find a picture of TabScrip, and if he meant TabStrip, then I don’t know what he’s talking about. You can color your tabs to group them. You can’t move the tabs to any other position except the bottom, as far as I know.
I have a few workbooks that have a lot of tabs. In those rare situations I wish for better navigation of tabs. If you right click on the VCR buttons to the left of the tabs, you can quickly select a tab. If you have more than 16 tabs, you can’t see them all. Instead you get a “More Sheets” options that brings up an unsortable dialog.
First, I’d like that right click list to go all the way to the top of the screen. Don’t stop at 16, stop at 30 or some other number that gives me more options. If I do need to open the dialog, I want three sort options: the order they appear in the workook; alphabetical order; and zOrder (the order in which they were last accessed). I guess that’s six sorts with ascending and descending options. I don’t really need this because I only have a handful of really big workbooks. But it would be handy in those cases.
What do you think about Scott’s wish or tabs in general?
Rob van Gelder:
I did something similar some time ago:
Here's hoping the code looks correct after HTML munching.
Assuming you have numbered sheets, eg.
"1 Intro", "1 Specs", "2 Design", "2 Detail", "2 Process", "3 Implementation", "4 Deploy", "4 Post-Deploy", "5 Go Live", "5 Go Live (2)", "5 Go Live (3)", "6 Support", "6 Post Go Live Issues", "Sheet3"
Paste this to the code module for ThisWorkbook:
Toolbar_ON
End Sub
Private Sub Workbook_Deactivate()
Toolbar_OFF
End Sub
Paste this to a Standard Code Module, then run Toolbar_ON
Sub Toolbar_OFF()
Dim bar As CommandBar
''' Delete the Commandbar if it already exists
For Each bar In Application.CommandBars
If bar.Name = cCommandBar Then bar.Delete
Next
End Sub
Sub Toolbar_ON()
Dim bar As CommandBar, wks As Worksheet, ctl As CommandBarPopup
Dim i As Long, arrGroups(0 To 11) As CommandBarControl
Toolbar_OFF
Set bar = Application.CommandBars.Add(Name:=cCommandBar, Position:=msoBarBottom, Temporary:=True)
bar.Visible = True
For Each wks In ThisWorkbook.Worksheets
i = SheetNameToGroupNum(wks.Name)
If arrGroups(i) Is Nothing Then
Set ctl = bar.Controls.Add(Type:=msoControlPopup)
If i = 0 Then ctl.Caption = "Other" Else ctl.Caption = " " & i & " "
Set arrGroups(i) = ctl
Else
Set ctl = arrGroups(i)
End If
With ctl.Controls.Add(Type:=msoControlButton)
.Caption = wks.Name
.OnAction = ThisWorkbook.Name & "!Popup_Click"
.Parameter = wks.Name
End With
Next
End Sub
Function SheetNameToGroupNum(SheetName As String) As Long
Dim i As Long
i = 1
For i = 1 To Len(SheetName)
If Not IsNumeric(Mid(SheetName, i, 1)) Then Exit For
Next
If i = 1 Then i = 0 Else i = Left(SheetName, i - 1)
If i 10 Then i = 0
SheetNameToGroupNum = i
End Function
Sub Popup_Click()
With Application.CommandBars.ActionControl
ThisWorkbook.Worksheets(.Parameter).Activate
End With
End Sub
am8421:
Old good times when using Excel 4 or 5 (well, it was a long time ago). When working with workbooks you could use an index sheet. It covered all page /screen area, you could use about 20-30 characters per sheet name (it was wow then) and you could see the whole name. Copying and moving was very comfortable, perhaps it was kind of grouping.
Dick writes: "If you have more than 16 tabs, you can’t see them all." I don't remember how many items were visible on 14" display, but it was very satisfactory. Now it would be more.
Rob - I get syntax error. Perhaps due to copy-paste from web page.
8 April 2008, 1:04 amAndreas Emmert:
You have to replace the & with "&"
8 April 2008, 1:50 amAt the end you need to insert an equal sign in the code line If i = 10 then i = 0
Patrick O'Beirne:
Two addins (probably not for Excel 2007, I haven't checked)
http://www.tushar-mehta.com/excel/software/utilities/wb_nav.html
http://www.robbo.com.au/
8 April 2008, 2:56 amAl:
Wow Rob- that's great...
am8421 - after pasting you need to change:
If i 10 Then i = 0
to
If i = 10 Then i = 0
and change all the & to just &
8 April 2008, 2:56 amSimon Murphy:
I've got a free workbook structure add-in here:
8 April 2008, 8:40 amhttp://www.codematic.net/excel-tools/free-excel-tools.htm
it adds an index sheet with a list of sheets and descriptions and facility to group them into multiple groups
Its a bit agricultural, but I've found it useful for big workbooks.
There is quicknav there too - that adds a toolbar with a drop down list of sheets and names for easy navigation. (that too is a bit agricultural - anyone see a pattern?)
cheers
Simon
Rob van Gelder:
The & needs to be changed to &
8 April 2008, 3:05 pmThe line reading 'If i 10 Then i = 0' should be changed to If i < 1 Or i > 10 Then i = -1
Rob van Gelder:
Rough start... correction to above comment
8 April 2008, 3:11 pmThe line reading 'If i 10 Then i = 0' should be changed to If i < 1 Or i > 10 Then i = 0
Doug Jenkins:
I can confirm that all of the tools posted here work in Excel 2007. The tool bars appear as a menu on the Add-ins tab, which is maybe not ideal, but better than nothing.
They all have their good and bad points. I think I'm most likely to use Tushar's, if any, because it seems to be the simplest and easiest to implement.
The add-in at robbo.com.au looks like an impressive application, but it will take time to investigate all that it does.
By the way, Rob Van Gelder, what has happened to your web site? I just get random adverts when I click on your name.
8 April 2008, 5:08 pmRob van Gelder:
My website is hosted as a free homepage. My ISP recently forced adverts onto the page.
8 April 2008, 5:23 pmI must do something about redesigning it soon, or the adverts will take over
Doug Jenkins:
Rob - the advert is all I get.
Just a single ad for My Space Jobs, which takes you to the My Space Jobs site if you click on it.
Nothing else at all.
8 April 2008, 6:05 pmam8421:
Thanks all for help with VBA corrections. It works now, nice tool for navigating. Rob - your website in Firefox 2 is OK, in IE 6 - only adverts are displayed. Simon - I'm not sure if I see an agricultural pattern in add-in, it's rather modest in colours and patterns. Most of users I work with love to use multiple colours for cell fills, fonts and individually for some values (like a set of textmarkers, absolutely not conditional formatting). Worksheets look like you had hallucination.
9 April 2008, 12:44 am