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.

Running Macros on Hidden Worksheets

by Allen Wyatt
(last updated January 10, 2015)

23

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Sorting Single-Column Addresses

Got a document that contains a bunch of addresses? If you want to sort the addresses, then you are in for a surprise because ...

Discover More

Ignoring Selected Words when Sorting

If you use Excel to maintain a list of text strings (such as movie, book, or product titles), you may want the program to ...

Discover More

Text Truncated in PivotTable

When you create a PivotTable based on data that contains lots of text, you may be surprised to find that your text is ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

MORE EXCELTIPS (MENU)

Workbook Events

You can create macros that run whenever Excel detects a certain event happening within an entire workbook. This tip explains ...

Discover More

Finding Other Instances of Excel in a Macro

When processing information using a macro, you may need to know if there are any other instances of Excel running on a ...

Discover More

Converting Numbers to Strings

When creating macros, it is often necessary to change from one type of data to another. Here's how you can change from a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 - 0?

2017-01-11 16:26:52

Andrew

Amazing! This worked like a charm.

Thank you!!!


2016-09-27 08:47:16

Hans

Nice. Works perfectly. Tanks very much.


2016-09-21 10:58:19

DB

Exactly what I needed. Thank you!


2016-09-05 16:52:58

Mosco

@ 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.


2016-09-05 09:23:16

Barry

@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


2016-09-04 19:59:47

Mosco

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.


2016-09-04 08:17:37

Barry

@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


2016-09-03 21:33:03

Mosco

@ 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


2016-09-02 06:27:29

Barry

@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.


2016-09-01 07:20:28

Mosco

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.


2016-06-09 17:53:18

Edna

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


2016-05-26 04:35:37

Rebecca

Thank you! This was exactly what I needed.


2016-01-17 17:05:46

Anna

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


2015-12-14 00:29:22

ershad nomani

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


2015-09-29 04:11:07

Calvin

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


2015-05-22 07:06:35

Barry

@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.


2015-05-21 18:42:11

Sam

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.)


2015-03-26 06:32:12

Barry

@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.


2015-03-25 13:23:29

Stephanie

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


2015-03-25 10:42:24

Barry

"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.


2015-01-23 13:01:54

Andrew

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


2015-01-16 08:39:06

Barry

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.


2015-01-15 20:08:45

Tim

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?


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing