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?
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
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.
You have to replace the & with “&”
At the end you need to insert an equal sign in the code line If i = 10 then i = 0
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/
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 &
I’ve got a free workbook structure add-in here:
http://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
The & needs to be changed to &
The line reading ‘If i 10 Then i = 0’ should be changed to If i < 1 Or i > 10 Then i = -1
Rough start… correction to above comment
The line reading ‘If i 10 Then i = 0’ should be changed to If i < 1 Or i > 10 Then i = 0
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.
My website is hosted as a free homepage. My ISP recently forced adverts onto the page.
I must do something about redesigning it soon, or the adverts will take over :(
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.
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.
I made the following, which is messy because I’m super new at this. Basically it lists and sorts all the tabs in a spreadsheet in ascending or descending order then creates hyperlinks to each on an index page, as well as using the A1 cell in each spreadsheet to link back to the index page. Let me know how terrible it is for a more or less first-timer!
‘
‘ SheetSorter Macro
‘ Macro recorded 5/14/2008 by Adam Valine
‘
‘ Keyboard Shortcut: Ctrl+Shift+S
‘
‘Declare variables
Dim msgResponse As Integer
Dim intSheetCount As Integer
Dim intRollThrough As Integer
Dim intRowCount As Integer
Dim msgSortOrder As Integer
Dim intSheetOrder As Integer
Dim intSheetName As String
Dim strLinkAddress As String
‘Determine if macro has been run
‘If NO then create the sheet and run the sort
If Sheets(1).Name “111 Sort Sheet” Then
Sheets.Add
ActiveSheet.Name = “111 Sort Sheet”
Sheets(“111 Sort Sheet”).Move before:=Sheets(1)
intSheetCount = ActiveWorkbook.Sheets.Count
intRollThrough = 1
‘Display the worksheet names
For intRollThrough = 1 To intSheetCount
ActiveSheet.Hyperlinks.Add Anchor:=Range(“A1”).Offset(intRollThrough – 1, 0), Address:=“”, SubAddress:=“‘” & Sheets(intRollThrough).Name & “‘!A1”, TextToDisplay:=Sheets(intRollThrough).Name
Next intRollThrough
‘Define intRowCount variable
intRowCount = Application.WorksheetFunction.CountA(Range(“A1:A65536”))
‘Define msgSortOrder variable
msgSortOrder = MsgBox(“Select ‘Yes’ to run Ascending Sort, or ‘No’ to run Descending Sort”, vbYesNo)
‘Determine sort order of sheets
Range(Range(“A1”), Range(“A1”).Offset(intRowCount – 1, 0)).Select
‘If YES then Ascending order
If msgSortOrder = vbYes Then
Selection.Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Else
‘If NO then Descending order
Selection.Sort Key1:=Range(“A1”), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
‘Create and downfill anchor numbers, which will be used to sort the sheets
Columns(“A:A”).Select
Selection.Insert shift:=xlToRight
Range(“A1”).Formula = “1”
Range(“A2”).Formula = “2”
Range(“A1:A2”).Select
Selection.AutoFill Destination:=Range(Range(“A1”), Range(“A1”).Offset(intRowCount – 1, 0)), Type:=xlFillDefault
Range(“A1”).Select
‘Define intSheetOrder variable
intSheetOrder = 1
‘Define intSheetName variable
For intSheetOrder = 1 To intSheetCount
intSheetName = Range(“B1”).Offset(intSheetOrder – 1, 0)
Sheets(intSheetName).Move before:=Sheets(intSheetOrder)
ActiveSheet.Hyperlinks.Add Anchor:=Range(“A1”), Address:=“”, SubAddress:=“‘” & Sheets(“111 Sort Sheet”).Name & “‘!A1”, TextToDisplay:=Sheets(“111 Sort Sheet”).Name
Sheets(“111 Sort Sheet”).Select
Next intSheetOrder
Sheets(“111 Sort Sheet”).Move before:=Sheets(1)
Columns(“A:A”).Select
Selection.Delete shift:=xlToLeft
Range(“A1”).Select
Else
‘If YES then ask the user if they want to re-run the macro
msgResponse = MsgBox(“The worksheets are already sorted.” & Chr(13) & Chr(13) & “Would you like to delete the sort sheet and sort again?”, vbYesNo)
‘If YES user chooses to re-run, delete the sort sheet and re-run the sort from scratch
If msgResponse = vbYes Then
Sheets(“111 Sort Sheet”).Delete
Sheets.Add
ActiveSheet.Name = “111 Sort Sheet”
Sheets(“111 Sort Sheet”).Move before:=Sheets(1)
intSheetCount = ActiveWorkbook.Sheets.Count
intRollThrough = 1
‘Display the worksheet names
For intRollThrough = 1 To intSheetCount
Range(“A1”).Offset(intRollThrough – 1, 0) = Sheets(intRollThrough).Name
ActiveSheet.Hyperlinks.Add Anchor:=Range(“A1”).Offset(intRollThrough – 1, 0), Address:=“”, SubAddress:=“‘” & Sheets(intRollThrough).Name & “‘!A1”, TextToDisplay:=Sheets(intRollThrough).Name
Next intRollThrough
‘Define intRowCount variable
intRowCount = Application.WorksheetFunction.CountA(Range(“A1:A65536”))
‘Define msgSortOrder variable
msgSortOrder = MsgBox(“Select ‘Yes’ to run Ascending Sort, or ‘No’ to run Descending Sort”, vbYesNo)
‘Determine sort order of sheets
Range(Range(“A1”), Range(“A1”).Offset(intRowCount – 1, 0)).Select
‘If YES then Ascending order
If msgSortOrder = vbYes Then
Selection.Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Else
‘If NO then Descending order
Selection.Sort Key1:=Range(“A1”), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
‘Create and downfill anchor numbers, which will be used to sort the sheets
Columns(“A:A”).Select
Selection.Insert shift:=xlToRight
Range(“A1”).Formula = “1”
Range(“A2”).Formula = “2”
Range(“A1:A2”).Select
Selection.AutoFill Destination:=Range(Range(“A1”), Range(“A1”).Offset(intRowCount – 1, 0)), Type:=xlFillDefault
Range(“A1”).Select
‘Define intSheetOrder variable
intSheetOrder = 1
‘Define intSheetName variable
For intSheetOrder = 1 To intSheetCount
intSheetName = Range(“B1”).Offset(intSheetOrder – 1, 0)
Sheets(intSheetName).Move before:=Sheets(intSheetOrder)
ActiveSheet.Hyperlinks.Add Anchor:=Range(“A1”), Address:=“”, SubAddress:=“‘” & Sheets(“111 Sort Sheet”).Name & “‘!A1”, TextToDisplay:=Sheets(“111 Sort Sheet”).Name
Sheets(“111 Sort Sheet”).Select
Next intSheetOrder
Sheets(“111 Sort Sheet”).Move before:=Sheets(1)
Columns(“A:A”).Select
Selection.Delete shift:=xlToLeft
Range(“A1”).Select
Else
‘If NO no changes are made to the sheet or sort
MsgBox “Your sort order will not be changed.”
End If
End If
End Sub
Hi, I’m a non-coder and I need what I would call tabs and sub-tabs in Excel. So the top level, or Master tabs, would be labeled A-C, D-F, etc. Then I need sub-tabs under each, with client names: Allen, Jane, Adams, Mari, Brown, Sue, etc. Each of those tabs (per client) will contain hundreds of rows for individual transactions. When my employees need to add something (or deduct) from a client’s account, they need to be able to click on, for instance, the M-N tab and see only the last names that start with those 2 letters. I can’t have a single speradsheet with 500 or more tabs (one for each client). Any help for a rookie? I found a post with some code to insert, but all tabs except the Masters were hidden – I couldn’t see the other tabs at all. Thanks for any help you can provide!
What Scott has expressed a wish for is EXACTLY what I am trying to find a way to do. This would greatly improve organization for people who currently have to work with multiple workbooks in their dayly routine. It would essentially allow grouping of workbooks within a single Excel File.
I am looking for the same thing.
For instance – a workbook to track all financial data for my organization.
Summary page shows monthly totals – and annual totals – linked to any of 6 categories which also have monthly totals. Those totals are generated by information on other worksheets – linked.
For example – If summary (Level 1) contains Students, Loans, Food, Projects, Overhead – then Loans has it’s own level 2 worksheet – which shows all current and past loans with total borrowed, total interest owed, number of payments and total outstanding – which is linked to a separate worksheet for each loan (Level 3).
This is a simplified expl of my workbook…so you can imagine that it gets pretty crowded with almost 35 worksheets in the workbook. I would really like to be able to group all of the Level 3s by category (level 2) i.e. have a group of “loans” tabs that hide unless open.
Any ideas?