Debra had a great post about hiding worksheets with a macro the other day. I wanted to demonstrate another way using Custom Views. Start with a workbook with four sheets: Inputs, Outputs, Tables, and Settings.
When you’re actively developing the workbook, you want to see all of the sheets. But when the workbook is in use by its regular users (in production, we’ll call it) you only want to show the Inputs and Outputs sheets. With all of the worksheets visible, select Custom View from the View menu (View tab – Custom Views in 2007).
Click the Add button to create a new view and call it Development
Close out the dialog and hide the “admin” sheets. Now repeat the steps to create a Production view
To quickly change the visibility of the sheets, simply change the view from Development to Production and back
If you want to make it quicker with a macro, this will toggle between the two:
If wshTables.Visible Then
ThisWorkbook.CustomViews(“Production”).Show
Else
ThisWorkbook.CustomViews(“Development”).Show
End If
End Sub
I don’t know how to explicitly check if a View is active, so I check to see if the Tables sheet is hidden. If you know a better way, please let me know in the comments.
I like to make helper macros as generic as possible, so I’d probably do something like giving all the hidden sheets the same tab colour, and then hide/show all sheets with this colour. Then you can add more sheets without messing with code.
An interesting alternative.
I went with an old style approach for our data input models, and used a macro to cycle through a list of worksheets – depending upon what was needed at the time.
We have a list in column x, followed by 3 further adjacent columns which are populated by a Y or an N to denote whether the worksheet should be visible according to the criteria that is set for these columns.
The macro is called automatically by whatever process might need to change which sheets are displayed, so it is invisible to the users. In addition there are 2 macros which we use to help us out – showall, and hidenotdisplayed.
A simple approach but very effective.
Tab colour is good, but I avoid them if possible. Excel 2000 doesn’t have tab colours and whilst it is a 10 year old product I’m still having to develop for it.
A timely hint, right on the button, Thank You.
I use a generic function (in my library, natch!) to determine whether I am in production or development mode:
blnIAmAtHome = blnFolderExists(“T:GreavesClientsLLSUCLSUTProjectsAttendanceSystem”)
‘Sub TESTblnIAmAtHome()
‘ Debug.Assert blnIAmAtHome
‘End Sub
End Function
The path/file represents a file that will only be found on my system, never on the client’s system.
Your toggling macro might then be augmented by a Workbook.Open event that asks:
(“Development” view)
Else
(“Production” view)
EndIf
There has to be a better way:
Dim cbxView As CommandBarComboBox
If Application.CommandBars.FindControl(ID:=950).Parent.Name = “View” Then
With Application.CommandBars.Add(Temporary:=True)
.Controls.Add ID:=950
.Enabled = True
.Visible = True
End With
End If
Set cbxView = Application.CommandBars.FindControl(ID:=950)
WhatView = cbxView.Text
End Function
Nebraska to Big 10!
I test for the presence of an empty text file in the workbook’s directory to make the hidden stuff appear. I indicate the sheets which should be hidden either with a hidden worksheet level name or a prefix to the sheet name, or for large projects, I use a master sheet that lists information about each sheet (hidden/visible, address of visible range, other information).
Always thought Custom views could only be used to store hidden rows/columns setting or Filter settings. Never realized it could be used for hiding/un-hiding sheets
Oh…forgot to mention.
You need to be a bit careful of using custom views in 2007/2010 – If there is a sheet that has Tables – then custom views are disabled for the entire file…Cant see the logic…but thats how it is.
Hi,
Hadn’t thought of using this for ‘production’ and ‘development’ versions, great idea though =] Think I’ll start using this going forward…
As for making the macro more generic I tend to use the index as opposed to the name. So if we assume that the Development View will always come first we can use:
thisworkbook.CustomViews(1).Show
So something like this in the workbook open event:
if environ(“UserName”) = “Arthur.Nicholson” then ‘Only person who needs to see admin sheets
thisworkbook.CustomViews(1).Show
else
thisworkbook.CustomViews(1).Show
end if
Did you mean
“…
else
thisworkbook.CustomViews(2).Show
…
“
Yes… Lol, oh the dangers of writing code directly into a comments field… =]