Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Running Macros on Hidden Worksheets

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Running Macros on Hidden Worksheets.

Macros are often used to process information within a workbook. Your macro can access any cells in the workbook, unless the worksheet containing the cell is hidden. When you hide a worksheet, it is even hidden from normal macro operations.

The upshot of this is that if you want to run a macro and have it access information on a hidden worksheet, you must first "unhide" the worksheet. To do this, you use the following line of code in your macro:

Sheets("My Hidden Sheet").Visible = True

When this line is executed, then the worksheet named My Hidden Sheet will no longer be hidden. It is then easily accessible by using the Selection object or the Select method. When you are later ready to hide the worksheet again (when you are done processing), use this line of code:

Sheets("My Hidden Sheet").Visible = False

Of course, unhiding and later hiding worksheets can cause a lot of flashing on the screen as Excel tries to update its screen display based on the commands executed in your macro. If you want to avoid this, then use the following line of code at the beginning of your macro:

Application.ScreenUpdating = False

With screen updating turned off in this way, nobody will ever know that you unhid a worksheet and later rehid it. Make sure that before ending the macro, however, you set the ScreenUpdating property back to True.

Remember, as well, that the Selection object (and the Select method) are not the only ways to access information. If you rely, instead, on working with ranges (using the Range method or defining an object using the Range method), then you can easily access information on a hidden worksheet without the need to make it visible.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2548) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Running Macros on Hidden Worksheets.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

DB    21 Sep 2016, 10:58
Exactly what I needed. Thank you!
Mosco    05 Sep 2016, 16:52
@ 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.
Barry    05 Sep 2016, 09:23
@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
Mosco    04 Sep 2016, 19:59
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.
Barry    04 Sep 2016, 08:17
@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
Mosco    03 Sep 2016, 21:33
@ 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
Barry    02 Sep 2016, 06:27
@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.
Mosco    01 Sep 2016, 07:20
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.
Edna    09 Jun 2016, 17:53
What if the worksheet is protected (so that others can't unhide the hidden sheet)?
Rebecca    26 May 2016, 04:35
Thank you! This was exactly what I needed.
Anna    17 Jan 2016, 17:05
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
ershad nomani    14 Dec 2015, 00:29
thanks for help.how can i stop a macro to run only one time.
Calvin    29 Sep 2015, 04:11
Excellent Tip Allen! This works just fine in Excel 2010..
Barry    22 May 2015, 07:06
@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.
Sam    21 May 2015, 18:42
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.)
Barry    26 Mar 2015, 06:32
@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.
Stephanie    25 Mar 2015, 13:23
What will cause an error in the Visible = True line of code all of a sudden?
Barry    25 Mar 2015, 10:42
"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.
Andrew    23 Jan 2015, 13:01
Thanks for the tip and the helpful comments! Very useful for updating reports that contain sheets people should not be modifying.
Barry    16 Jan 2015, 08:39
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.
Tim    15 Jan 2015, 20:08
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?
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.