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

Running Macros on Hidden Worksheets

by Allen Wyatt
(last updated May 5, 2016)

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 regular macro commands. 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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9329) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel 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

Changing Toolbar Buttons with VBA

Toolbar buttons can have a different appearance depending on their stateâ€"whether they have been clicked or not. This ...

Discover More

Drawing Simple Objects

Want to draw a few simple shapes or lines on your worksheet? It really is simple; here's how to do it.

Discover More

Using the Highlighter

Need to draw attention to some text? Consider using the Highlight tool, which functions just like the highlighter pens ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Default Worksheet when Opening

When opening a workbook, you may want to make sure that a particular worksheet is always displayed first. The only way to ...

Discover More

Telling which Worksheets are Selected

If your macro processes information on a number of worksheets, chances are good that you need your macro to figure out ...

Discover More

Showing RGB Colors in a Cell

Excel allows you to specify the RGB (red, green, and blue) value for any color used in a cell. Here's a quick way to see ...

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

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. Maximum image size is 6Mpixels. 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 five minus 4?

2018-01-01 09:41:53

Peter Atherton

Clayton

The macros:

Private Sub FormatHidden()
Dim wks As Worksheet, rng As range

Set wks = Sheets("sheet4")
With wks
.Visible = xlSheetVeryHidden
Set rng = .range("A1:B5")
rng.Font.Bold = True
.Visible = xlSheetVeryHidden = False
.Visible = xlSheetVisible
End With
End Sub
Sub HideSheet()
ActiveSheet.Visible = xlSheetVeryHidden
End Sub


2018-01-01 08:03:28

Peter Atherton

Clayton

Excel is not very secure and the VB Editor can be activated with ALT + F11 if the developers tab is hidden. once in the VB editor each sheet is listed in the Project Explorer (CTRL + R). If the sheet is password protected then this offers a level of protection but this can be removed, see the link below. Using the keyword Private before a function or Macro will hide it from the Macro form (ALT + F8) so the macro will not be able to run from there unless the user knows the macro/ function name before hand. In short Excel is fairly secure against accidental usage but malicious damage.

http://www.mcgimpsey.com/excel/removepwords.html

Here are a couple of macros to play around with that you can use to test things for your self.


2017-12-31 06:51:07

Clayton

Can someone explain to me how any worksheet can be truly hidden in a macro-enabled workbook if a macro can Activate the hidden sheet, a sheet whose name is known by looking inside the code of the macros the user is permitted to run. Seems the user can write a simple macro that activates the hidden sheet and starts upper-left and print out every cell's formulas and values for the sheet? It seems to me the user must be denied access to the VBE to truly "hide" the guts of the sheet and/or sensitive data. If so, is Hiding the Developer tab sufficient for keeping users out of the source code?


2017-01-11 10:33:00

Caleb

Thanks! This helped me fix my issues and learning how to stop the screen updating really sped up my macro as it used less processing power. Helped alot!


2016-08-19 23:37:38

Peter

I have a different problem. Excel "Very well hidden" sheets complete with password protection can be viewed without any problem over a smartphone. Does anyone know a way around this?


2016-07-21 05:44:14

Barry

@Ramanathan

Hiding the worksheet is easy you can just create a button on each worksheet you want to be able to hide and assign it the macro:

Sub HideSheet()
Activesheet.visible=xlSheetVeryHidden
End Sub
put this macro in a code module.

Unhiding is not so easy as you cannot see the hidden sheet (duh!!), this will need to be done via a control worksheet which cannot be hidden with a button for each other worksheet which when clicked will execute a short macro to unhide that selected sheet and make it active.

Sub UnhideSheet()
With Thisworkbook.Worksheets("SheetName") 'use the actual worksheet name instead of SheetName
.Visible = xlSheetVisible
.Activate
End With
End Sub

A more elegant way might be to list the hidden worksheets in a ListBox so the User selects the Worksheets from the drop-down list then clicks a button to activate the select worksheet(s). This could be on a master/control worksheet, in a menu, or floating in modeless Userform.


2016-07-21 01:42:34

Ramanathan

I Want New codings to Hide or un hide the sheets by clicking in the box which is im going to set in excel sheet.


2016-06-10 11:31:53

Whatever

Thank you!!!


2016-03-24 05:31:44

Barry

@Dee

First and foremost make sure that you are saving the workbook as a macro-enabled workbook (with the extension ".xlsm") if not then the macros will not be saved. Excel does give a warning when saving but it is too easy to just select the default of saving without macros.

If this is not then case then look at when recording a macro you are initially required to name the macro, any shortcut key, AND crucially where it is to be stored. The storage choices are:

a. Personal Macro Workbook
b. New Workbook
c. This Workbook

It is likely that you've stored it in the wrong place. Which is the best place to store it depends on how you will be using the workbook/distributing the workbook, and is a whole discussion in its own right. As a newbie to macros I suggest you select "This Workbook" for simplicity.

The other possiblity is that it the macro is being stored in a different module. Re-opening a workbook and creating a new macro will create a new module, and although the macro is still there it is in a different module.


2016-03-23 08:48:08

dee

Hi,

I am also very new to macros, though I have been using excel for over a decade - I just felt that I never needed macros and have been dodging the bullet for awhile. However, I realise that I need it and Im stuck at the 1st huddle.

I have recorded a macro, which works fine. My issue is every time I close and reopen the file and try to run the macro, its no where to be found.

How do I ensure that the macro is visible to be used in the workbook all the time, please help....


2015-12-01 03:43:48

amit goyal

Great help, today prepared my first VBA macro based dashboard and got soe help from here.. thanks


2015-09-29 06:15:20

Barry

@M3NT4L

You can replace the code:

Sheets("Repair Evaluation Data").Select
Sheets("Repair Evaluation Data").Columns("C:C").Select
Selection.Copy
Sheets("Repair Roster").Select
Sheets("Repair Roster").Range("L1").Select
ActiveSheet.Paste

With a single line of code:

Sheets("Repair Evaluation Data").Columns("C:C").Copy _
Destination:=Sheets("Repair Roster").Range("L1")

(this can be all on one line but I broke the line for display purposes)

As this does not use the Selection object or method the display will not change.


2015-07-30 12:00:00

M3NT4L

I"m new to macros in excel and finding that they are extremely helpful, but have run into a knowledge issue.. (my lack of it :) )

Below is a macro that I had running perfectly, then I tried to add to it using the 'record macro' function hence all of the 'selects' The first portion is filter a table based on a cell value 'c4:e4' Runs fine....

The second portion I'm trying to run is a copy of a range and paste it to another sheet... It works, but it does not hide the screen switches and I need to hide the sheets.

Any help is appreciated! Thanks



Private Sub Worksheet_Change(ByVal Target As Range)

Const DropDown = "c4"

Const TableSheet = "Repair Evaluation Data"

Const TableRange = "A1"
If Not Intersect(Range(DropDown), Target) Is Nothing Then
Application.EnableEvents = False
If Range(DropDown).Value = "" Then
Worksheets(TableSheet).ShowAllData
Else
Worksheets(TableSheet).Range(TableRange).AutoFilter _
Field:=8, Criteria1:=Range(DropDown).Value
End If
End If
Application.EnableEvents = False
Range("C4:E4").Select
Sheets("Repair Evaluation Data").Select
Sheets("Repair Evaluation Data").Columns("C:C").Select
Selection.Copy
Sheets("Repair Roster").Select
Sheets("Repair Roster").Range("L1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$L$1:$L$1041020").RemoveDuplicates Columns:=1, Header:= _
xlYes
Sheets("Repair Center Dashboard").Select
Application.EnableEvents = True

End Sub


2015-04-10 08:32:08

Lokesh Goyal

As chuck mentioned that we can run the macro with hidden sheets, I tried it an the macro ran perfectly well.However when I tried to run the macro line by line using the debugger it gave me error on the line which accessed hidden sheet.
Does any one know why is this so


2013-05-02 09:01:59

chuck

I ran a small test to verify: In fact, you can select cells on a hidden sheet... if you activate the sheet first.

So, apparently the error does not come from trying to select a cell on a sheet that is hidden, but rather, from trying to select a cell on a sheet that is not the active sheet.

Therefore, if you really want to select cells on a hidden sheet for some reason, then you can activate that sheet (but leave it hidden).

I do this in a case where I run a large query and subsequent calculations in the background (on another sheet), but I don't want it to affect the user's screen. Now, I normally don't use the select method at any time on this hidden sheet, but I ran a small test to prove that it works without setting an error.


2013-05-01 05:53:08

JMJ

@Bryan: Thanks for confirming what I thought, and explaining why it works!


2013-04-30 16:13:20

Bryan

@JMJ: You actually don't need to reset Application.ScreenUpdating to True at the end, because it is automatically set to True when code stops executing (a few other settings reset as well... Application.DisplayAlerts comes to mind).

The same can be said of local object variables (ranges, worksheets, etc); some people consider it "good coding practice" to clear out and reset everything at the end of the macro, but other people point out the VBA garbage cleaner is going to do this for you anyway!


2013-04-30 01:22:01

Dave Unger

Same here, I have no problems running my macros on hidden sheets. I almost never select any ranges, so it's not a problem.

Regards


2013-04-29 13:03:08

JMJ

You said "Make sure that before ending the macro, however, you set the ScreenUpdating property back to True."

In fact, I never, ever, noticed any difference when doing so or omitting this instruction: When the macro ends, the screen can be refreshed anyway!


2013-04-29 09:49:00

chuck

You can even perform the equivalent of pressing a button on a hidden sheet (i.e., run the button's macro) without ever unhiding the sheet. To "press a button" on a hidden sheet, use something like:
Set OriginalWorksheet = ActiveSheet
Sheet3.Activate
Application.Run ("'" & ThisWorkbook.Name & "'!" & "Sheet3.CommandButton1_Click")
OriginalWorksheet.Activate

Note that in the Application.Run command, you must use the sheet's code name that Excel gave it, not it's visible name that you gave it.

Interstingly, although you can't select cells in a hidden sheet, you can activate the sheet while it is still hidden. Many of my subroutines are written generically, so that the same code can run on different sheets. To do this, I rely heavily on the "Activesheet" object. So, for me it was important to activate the sheet before running the code so that it runs on the correct sheet. For simpler tasks, that may not be necessary. However, remember that if you don't explicitly specify the sheet, VBA often defaults to the Activesheet.


2013-04-29 08:28:27

Bryan

The macro recorder is excellent for figuring out how to do things you didn't know how to do, but one of the greatest downsides is you think you need to select everything to use it!


2013-04-29 07:25:46

Barry Fitzpatrick

I, too, have macros that access cell values on hidden worksheets without any problems. I often have a web query that returns results to a hidden calculation page before then extracting the retrieved data.

BUT as Rik says your macros/VBA code must NOT use the "Select" method for accessing a cell or range of cells. Otherwise this will resulting in a run-time error.


2012-08-22 04:03:34

Rik Smeesters

My macros work with very hidden sheets with no problems but I don's select
these sheets or cells on in.
I use statements like:
var1=range(c1,c2).value
for i=lbound(var,1) to ubound(var,2)
var(i,1)=sqr(var(i,1))
next i
range(c1,c2).formula=var1
range(c1,c2).sort ...
Kind regards


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu 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.