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.
Note:
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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
When you have a macro that processes a huge amount of data, it can seem like it takes forever to finish up. These ...
Discover MoreIt can be frustrating when you get error messages doing something that you previously did with no errors. If you get an ...
Discover MoreDo you need to create a number of words or phrases where you only alter a few letters in each one? If the alterations ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-07-22 12:01:44
Ella
Exactly what I needed. Thank you!
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
@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
@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
@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?
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments