Running Macros on Hidden Worksheets

Amazing! This worked like a charm.

Thank you!!!

Nice. Works perfectly. Tanks very much.

Exactly what I needed. Thank you!

@ Barry

I tried to go back to the website but I was resticted as I'm using company's internet acess.

Here is my email adress, if you can have it sent to me.
barigomoses@gmaill.com

Thanks you much. I appreciate your assistance on that.

@Mosco

I don't have access to your email address in order to send a test spreadsheet to you. But I've uploaded to the web and you can download from here: http://goo.gl/s2CXmz

Thanks Barry for the tip. Ive tried but I came up with errors.

Could you do an example on a workbook of three worksheets and send it via my email or upload it so I can go over it and try incoporate it into my workbook project.

Thank you so much.

@Mosco

Here's some VBA code that'll do the job. I've used worksheets codenames just in case a User changes a tab name. The Master sheet should have its codename changed to "Master".

The following should be put onto the "ThisWorkbook" codepage:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'make sure all sheets other than the "master" sheet are hidden before closing the workbook
'the codename of the master sheet is set to "Master" as this cannot be easily changed by a user.
HideSheets
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'make sure all sheets other than the "master" sheet are hidden
'the codename of the master sheet is set to "Master" as this cannot be easily changed by a user.
Dim wsSaved As String
wsSaved = ActiveSheet.Name
HideSheets
Worksheets(wsSaved).Visible = xlSheetVisible
Worksheets(wsSaved).Activate
If Master.Name <> wsSaved Then Master.Visible = xlSheetVeryHidden
End Sub

Private Sub Workbook_Open()
'make sure all sheets other than the "master" sheet are hidden
'the codename of the master sheet is set to "Master" as this cannot be easily changed by a user.
HideSheets
End Sub

Sub HideSheets()
Dim ws As Worksheet
Master.Visible = xlSheetVisible
Master.Activate
For Each ws In Worksheets
If ws.CodeName <> "Master" Then ws.Visible = xlVeryHidden
Next ws
End Sub

Then on the Master worksheet create a button with the following macro for each button don't forget to change the worksheet to be activated for each button. This code will be on the codepage for the Master worksheet:
Private Sub CommandButton1_Click() 'each button will have a unique name
Sheet2.Visible = xlSheetVisible 'change this reference the codename of the required sheet
Sheet2.Activate 'change this reference the codename of the required sheet
Master.Visible = xlSheetVeryHidden
End Sub

Then on each other worksheet create a button with the following code assigned to it on the codepage for that worksheet:
Private Sub CommandButton1_Click() 'button will have a unique name on that worksheet
Master.Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Master.Activate
End Sub

@ Barry

Thanks Barry.

Currently I'm using hyperlink to selected w/sheets back and fort to the master worksheet. However, I want all the worksheets to be hidden, showing only the mastersheet when opening the workbook. From the masterworksheet, I want to clcik on the name of the sheet (similar concept to hyperlink) through VBA so it can take me straight to the desired w/sheets while at the same time hide the masterhseet. From the desired w/sheet, when I want to return to the master sheet, I will click on the VBA and it will hide the current w/sheet and take me straight to the master sheet again.

Using hyperlink does not work when w/sheets are hidden thats why I want a coding using VBA to be able to do that to hiden w/sheets.

Thanks

@Mosco

If you just want to jump to a specific worksheet (or make a specific worksheet active) rather actually hiding the worksheet. You do this without using a macro by using the "hyperlink" function.

To do this select "Insert" then "Hyperlink" then "places in this Document". Choose the worksheet from the list and click OK. Repeat this for each worksheet in your workbook. Then on each worksheet do the same but set the hyperlink to the "master" worksheet.

I have a school assesment workbook containing many worksheets (30). From the mainsheet,I want to view a worksheet click a VBA command and view the desired worksheet and then click back using VBA command in that active sheet so it can hide the active worksheet and retuns to the mainsheet.

Could someone help with this.

What if the worksheet is protected (so that others can't unhide the hidden sheet)?

Thank you! This was exactly what I needed.

Hi - I only sometimes have a problem accessing hidden worksheets on different computers/different versions of Excel. Does anybody have a good overview of how each version deals with running macros on hidden sheets? Thanks, Anna

thanks for help.how can i stop a macro to run only one time.

Excellent Tip Allen! This works just fine in Excel 2010..

@Sam

The following macro will list all the sheets, their names, visible status, and sheet type.

If you only want the number of sheets the code: Sheets.Count will give you this.

Sub wkListSheets()
Dim ws As Integer
Dim wkList As Worksheet
Dim str As String

str = "List" 'change this is a sheet called "list" already exists

On Error Resume Next
Application.DisplayAlerts = False
Sheets(str).Delete
Application.DisplayAlerts = True
Set wkList = Worksheets.Add(before:=Sheets(1))
wkList.Name = str
With wkList.Range("A1:C1")
.Value = Array("Sheet Name", "Visible", "Sheet Type")
.Font.Bold = True
.Font.Underline = xlSingle
End With

For ws = 2 To Sheets.Count
wkList.Cells(ws, 1) = Sheets(ws).Name
Select Case Sheets(ws).Visible
Case xlSheetVisible
wkList.Cells(ws, 2) = "Visible"
Case xlSheetHidden
wkList.Cells(ws, 2) = "Hidden"
Case xlSheetVeryHidden
wkList.Cells(ws, 2) = "Very Hidden"
Case Else
wkList.Cells(ws, 2) = Sheets(ws).Visible
End Select
Select Case TypeName(Sheets(ws))
Case xlWorksheet
wkList.Cells(ws, 3) = "Worksheet"
Case xlChart
wkList.Cells(ws, 3) = "Chart"
Case xlExcel4MacroSheet, xlExcel4IntlMacroSheet
wkList.Cells(ws, 3) = "Excel4 Macro Sheet"
Case Else
wkList.Cells(ws, 3) = TypeName(Sheets(ws))
End Select
Next ws
wkList.UsedRange.Columns.AutoFit
Set wkList = Nothing
End Sub

Please note: if there is already a sheet named "List" it will be deleted, or change the "str" variable to another name as shown above.

Is there a way to show a list of hidden sheets -- even if I can't see the sheets.

Say, for example, I want a count of all the sheets in the workbook (vis. and non-vis.)

@Stephanie

What error code/message are you getting?

The most likely error is that the object you are trying to make visible no longer exists. E.G. if you are trying to make a worksheet visible by referring to it using its tab name, if someone changes the tab name (or deletes that worksheet) then you will get an error if this change hasn't been reflected in the macro as well.

If worksheet name changes are possible then it is better to the worksheet's "codename" which cannot be changed.

What will cause an error in the Visible = True line of code all of a sudden?

"When you hide a worksheet, it is even hidden from normal macro operations."
This is not strictly true a macro can access cells on a hidden or even a "very hidden" worksheet but what the macro cannot do is "Select" a cell on a hidden worksheet (this will cause an error).

It is not good programming practice to use the Selection object or the Select method anyway.

Thanks for the tip and the helpful comments! Very useful for updating reports that contain sheets people should not be modifying.

The Visible property of the Worksheet object isn't boolean (i.e. true or false only).
The property can have any of the following values:
xlSheetHidden (or False or 0)
xlSheetVisible (or True or -1)
xlSheetVeryHidden (2)

You can use either the actual values or the enumerations, I prefer the enumerations as it is more obvious when I have to go back over the code what the macro is doing.

I'm curious since the Hide/Unhide has become so common, is there a way to move a sheet to the 'VeryHidden = True' in a macro?