how to make a worksheet visible in excel vba

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Worksheet.Visible property (Excel)

• 6 contributors

Returns or sets an XlSheetVisibility value that determines whether the object is visible.

expression . Visible

expression A variable that represents a Worksheet object.

This example hides Sheet1.

This example makes Sheet1 visible.

This example makes every sheet in the active workbook visible.

This example creates a new worksheet and then sets its Visible property to xlSheetVeryHidden . To refer to the sheet, use its object variable, newSheet , as shown in the last line of the example. To use the newSheet object variable in another procedure, you must declare it as a public variable ( Public newSheet As Object ) in the first line of the module preceding any Sub or Function procedure.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

Hide or Unhide Sheets Using VBA

When working with large workbooks with many worksheets, it’s common to hide some worksheets to make the workbook more manageable.

With VBA, you can easily hide or unhide worksheets in bulk. This can also be useful when you want to quickly hide or unhide specific worksheets without having to find and locate them from a long list.

In this article, I will show you some simple VBA codes to hide or unhide sheets in Excel.

This Tutorial Covers:

Sheet.Visible Property in VBA

VBA has the Sheet.Visible property (or Worksheet.Visible property) that determines whether it would be visible or hidden for the user in the workbook.

Below is the line of code that would hide the sheet named ‘Example’ in the workbook in which the code is run:

And here is the code that would unhide it (in case it is hidden):

In this example, I have used Worksheets(“Example”), but you can use any sheet name, the active sheet, or even the Sheet object stored in a variable.

You can also use xlSheetHidden instead of True and xlSheetVisible instead of False . For example, the following code would also hide the sheet named Example Worksheets(“Example”).Visible = xlSheetHidden .

Note: You need to have at least one sheet visible in the workbook. If you try to hide the last visible sheet, VBA will give you the Runtime 1004 error.

VBA Codes to Hide Sheets

Below, I have some examples of VBA codes for hiding sheets in different situations.

Hide the Active Sheet

The below code will hide the active sheet:

Remember that you always need to have one visible sheet in the workbook. So, if the active sheet is the only sheet in the workbook, this code will throw an error.

Hide All Sheets Except the Active Sheet

Below is the code that will hide all the sheets except the active sheet.

The above code uses a For Each Next loop to go through each sheet, and then it checks the name of the sheet.

If the name of the sheet is not the same as that of the active sheet, it gets hidden.

Hide Sheets by Name

If you want to hide sheets by their names, you can do that using a VBA code as shown below:

In the above code, I specified the sheet name in double quotes and then used the Visible property of the sheet to set it to False to hide it.

In this case, I have hidden three sheets, and you can modify the code to hide more/less number of sheets.

Hide Sheets with a Specific Word in the Name

Below is the VBA code that hides only those sheets that have the word ‘Sales’ in the name:

The above VBA macro loops through each worksheet in the workbook and checks whether the name contains the word Sales or not. This is done using the VBA INSTR function .

If the word Sales appears anywhere in the name of the sheet, that sheet would be hidden.

Hide Sheets Based on Cell Value

Below, the VBA code hides any sheet that has the text “Hide” in cell A1 of the sheet.

The above code loops through each worksheet in the workbook and then checks the value in cell A1 of each sheet.

If the text in cell A1 is “Hide”, then it will hide that sheet by setting the visible property of the sheet to False .

Hide Sheets Based on Tab Color

When I’m working with a large workbook, I often give color to my worksheet tabs to group similar sheets and to make them more manageable.

This is also easier for me when I’m presenting my work on calls, as I can direct them to a specific tab by mentioning the tab color.

Below is the VBA code you can use to hide all the sheets that have a specific tab color (where you need to specify the tab color):

In the above code, I have specified the color criteria as RGB(255, 0, 0) , which refers to the red color.

The above code then loops through each worksheet in the workbook and checks the tab color. If the existing color of the worksheet matches the criteria in our code, then that sheet gets hidden.

If you want to hide all the sheets except the one that has a specific color, you can use a code similar to what I have below here:

The above code goes through each worksheet in the workbook and checks the tab color.

It then uses an If Then Else statement to check the tab color. If the tab color is not red (RGB(255, 0, 0)), then the Visible property is set to False (thus hiding it); else, it is set to True (making the sheet visible).

Make the Sheet Very Hidden (Cannot Be Unhidden by User)

VBA in Excel also allows you to make a sheet Very Hidden so that it cannot be unhidden by a user using the regular Excel interface.

This means that when you right-click on the sheet tab and then click on Unhide, you will not see the sheet name, and hence, you won’t be able to unhide it.

Below is the VB code that makes the sheet named Data very hidden:

The above code sets the Visible property of the sheet to xlSheetVeryHidden .

This is different than all the other codes where we have been setting the visible property to False or xlSheetHidden .

When you make the sheet ‘very hidden’, you will have to use a VBA code or an option in the VB editor interface to unhide it (covered later in this article).

Check If the Worksheet is Hidden

Before using VBA code to make changes to a worksheet, it might be useful to first check whether the worksheet is hidden or not.

Below is the VBA code that checks the visible property of the worksheet and shows a message box with a numeric value (corresponding to the sheet’s visible property):

This would show a message box with one of three values:

• -1 : The worksheet is visible.
• 0 : The worksheet is hidden but can be unhidden via the Excel interface.
• 2 : The worksheet is very hidden and cannot be unhidden via the Excel interface (only through VBA).

You can modify the code above to give you more meaningful text instead of numbers or use it as part of a larger code where the output of this line could then be used to decide what to do.

VBA Codes to Unhide Sheets

And here are some example codes for unhiding sheets in different situations.

Unhide Sheets By Names

If you already know the name of the sheet that you want to unhide, you can use a code as shown below:

In the above code, I used the sheet name and set the Visible property of that sheet to True to make it visible.

You can also use the code below, where instead of True , I use xlSheetVisible

Unhide All Hidden Sheets

The below VBA code goes through all the sheets in the workbook and makes them visible:

Unhide Sheets With Specific Words in the Name

Below, the VBA code would unhide all the sheets that have the word ‘Data’ in their name.

In the above code, I have used a variable wordCriteria to store the word that I am checking for in each sheet’s name.

It then goes through each worksheet and then uses the INSTR function to check whether the sheet name contains the word stored in the wordCriteria variable or not.

If it finds the name, it makes the sheet visible.

In this article, I’ve covered the concept of hiding and unhiding sheets in Excel using VBA and provided some simple code examples that you can use in various situations.

I hope you found this article useful. Please let me know your thoughts in the comments section below.

Other Excel articles you may also like:

• What is VBA Programming in Excel?
• VBA Copy Sheet to New/Existing Workbook
• VBA Activate Sheet (Worksheet.Activate)
• VBA Create New Sheet (Sheets.Add)
• VBA Clear Sheet
• How to Unhide Sheets in Excel (All In One Go)

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

How to Hide & Unhide a Sheet using VBA in Excel

- Written by Puneet

You can use a VBA code to hide or unhide a sheet in Excel. When you right-click on the sheet tab, you can see the option to hide or unhide it, and that same thing you can do with a VBA code.

In this post, we will look at some of the ways and methods that we can use.

VBA Code to Hide a Sheet

Let’s say you want to hide “Sheet1” from the active workbook. In that case, you need to use code like the following.

In the above code, you have referred to Sheet1, use the visible property, and changed it to false.

Make a Sheet Very Hidden

There’s one more option that you can use to make a sheet very hidden that cannot be un-hide by the user easily.

Hide a Sheet Based on the Value from a Cell

Alright, if you want to use a cell value instead of directly using the sheet name in the code, you can refer to that cell.

This code refers to cell A1 and uses the value from it to refer to the sheet that you want to hide.

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Check Sheet Before Hiding

You can also use a small code like the following to check the sheet that you want to hide exits or not.

The above code uses the FOR EACH LOOP + IF STATEMENT to loop through each sheet in the workbook. And check for the sheet that you want to hide.

Hide All the Sheets (Except ActiveSheet)

Now there one thing that you need to understand you can’t hide all the sheets. There must be one sheet visible all the time.

The above code loops through all the sheets of the workbook, then match each sheet’s name with the active sheet’s name and hide it if it doesn’t match.

VBA Code to Unhide a Sheet

To unhide a sheet, you need to change the visible property to TRUE.

If the sheet that you want to unhide it already visible, this code won’t show any error. But if that sheet doesn’t exist, then you’ll get a Run-time error ‘9’.

Use VBA to Unhide All the Hidden Sheets

Imagine you have more than one hidden sheet in a workbook, and if you want to hide them manually, you need to do this one by one.

But here’s the code does this in one go.

It loops through each sheet and un-hides it.

Related Tutorials

• CLEAR an Entire Sheet using VBA in Excel
• Copy and Move a Sheet in Excel using VBA
• COUNT Sheets using VBA in Excel
• DELETE a SHEET using VBA in Excel
• PROTECT and UNPROTECT a Sheet using VBA in Excel
• RENAME a Sheet using VBA in Excel
• Write a VBA Code to Create a New Sheet in Excel
• VBA Worksheet Object
• Activate a Sheet using VBA
• VBA Check IF a Sheet Exists

Three Options for the Worksheet's Visible Property

The " Visible " property allows you to:

• display the worksheet (default)
• hide the worksheet
• definitively hide the worksheet (it can then only be displayed using a VBA code or from the VBA editor)

By default, a worksheet is displayed. Its "Visible" property has the value of "-1 - xlSheetVisible":

Hide a worksheet (0 - xlSheetHidden)

To hide a worksheet, right-click on its tab and select "Hide":

Its "Visible" property corresponds to a value of "0 - xlSheetHidden":

You can then re-display the worksheet at any time by clicking on "Unhide" by right-clicking on any tab (or from the VBA editor):

Hide a worksheet (2 - xlSheetVeryHidden)

To prevent the worksheet from displaying in the tabs, select the value of "2 - xlSheetVeryHidden" for the "Visible" property:

The worksheet can then only be re-displayed by modifying its property in the VBA editor or using a VBA code.

Modify the property using a VBA code

To display worksheet 1 and hide worksheet 2, use the following code:

You can also enter constants instead of numbers:

• Ablebits blog
• Excel macro

Very hidden sheets in Excel

This tutorial clarifies the difference between hidden and very hidden sheets, explains how to make a worksheet very hidden and how to view very hidden sheets in Excel.

Are you exasperated because you cannot find the spreadsheet one of your formulas refers to? The sheet does not appear among other tabs at the bottom of your workbook, nor does it show up in the Unhide dialog box. Where on earth could that sheet be? Simply, it is very hidden.

What is a very hidden worksheet in Excel?

As everyone knows, an Excel sheet can be visible or hidden. As a matter of fact, there are two levels of worksheet hiding: hidden and very hidden .

Unhiding a sheet that was hidden normally is very easy. All you have to do is right-click any visible worksheet, click Unhide , and select the sheet you want to view. Very hidden sheets are a different story. If the workbook contains only very hidden sheets, you won't even be able to open the Unhide dialog box because the Unhide command will be disabled. If the workbook contains both hidden and very hidden sheets, the Unhide dialog will be available, but very hidden sheets won't be listed there.

Technically, how does Excel distinguish between hidden and very hidden worksheets? By the Visible property of the sheet, which can have one of these values:

• xlSheetVisible (or TRUE) - the sheet is visible
• xlSheetHidden (or FALSE) - the sheet is hidden
• xlSheetVeryHidden - the sheet is very hidden

While anyone can toggle between TRUE (visible) and FALSE (hidden) by using Excel's Unhide or Hide commands, the xlVeryHidden value can only be set from within the Visual Basic Editor.

How to make Excel worksheets very hidden

As already mentioned, the only way to make a sheet very hidden is by using the Visual Basic Editor. Depending on how many sheets you want to hide, you can proceed with one of the following methods.

Make a worksheet very hidden by changing its Visible property

If you want to completely hide just one or two sheets, you can change the Visible property of each sheet manually. Here's how:

• Press Alt + F11 or click the Visual Basic button on the Developer tab. This will open the Visual Basic Editor with the Project Explorer window in the top-left panel displaying a tree of all open workbooks and their sheets.
• Press F4 or click View > Properties . This will force the Properties window to appear just below Project Explorer (please see the screenshot below). If the Properties window is already there, skip this step :)
• In the Project Explorer window, click on the worksheet you want to make very hidden to select it.
• In the Properties window, set the Visible property to 2 - xlSheetVeryHidden .

Make active worksheet very hidden with VBA code

If you have to hide sheets on a regular basis and are annoyed about having to do it manually, you can automate the job with a single line of code. Here's the macro that makes an active worksheet very hidden:

If you are writing a macro for other users, you may want to take care of situations when a workbook contains only one visible sheet. As you may remember, it's not possible to hide absolutely all worksheets in an Excel file (whether you are making them hidden or very hidden), at least one sheet should remain in view. So, to warn your users about this limitation, wrap the above macro in an On Error block like this:

Make multiple worksheets very hidden with VBA code

In case you want to set all selected sheets to be very hidden, go through all of the selected sheets in an active workbook (ActiveWindow) one by one and change their Visible property to xlSheetVeryHidden .

How to unhide very hidden sheets in Excel

Now that you know how to completely hide sheets in Excel, it's time to talk about how you can view very hidden sheets.

Unhide a very hidden worksheet by changing its Visible property

To be able to see a very hidden worksheet again, you just need to change its Visible property back to xlSheetVisible .

• Press Alt + F11 to open the Visual Basic Editor.
• In the VBAProject window, select the worksheet you want to unhide.
• In the Properties window, set the Visible property to -1 - xlSheetVisible .

Unhide all very hidden sheets with VBA

If you have quite a lot of very hidden sheets and you want to make them all visible again, this macro will work a treat:

Note. This macro only unhides very hidden sheets , not worksheets hidden normally. If you want to display absolutely all hidden sheets, then use the below one.

Unhide all hidden and very hidden sheets at a time

To show all hidden sheets in an active workbook in one go, you simply set the Visible property of each sheet to TRUE or xlSheetVisible .

How to use Very Hidden Sheets macros

To insert any of the above macros in your Excel workbook, perform these usual steps:

• Open the workbook where you want to hide or unhide sheets.
• On the left pane, right-click ThisWorkbook and select Insert > Module from the context menu.
• Paste the code in the Code window.
• Press F5 to run the macro.

To keep the macro, be sure to save your file as an Excel macro-enabled workbook (.xlsm). For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel .

Alternatively, you can download our sample workbook with macros and run the desired macro directly from that workbook.

The sample workbook contains the following macros:

• VeryHiddenActiveSheet - makes an active sheet very hidden.
• VeryHiddenSelectedSheets - makes all selected sheets very hidden.
• UnhideVeryHiddenSheets - unhides all very hidden sheets in an active workbook.
• UnhideAllSheets - shows all hidden sheets in an active workbook (hidden normally and very hidden).

To run the macros in your Excel, you do the following:

• Open the downloaded workbook and enable the macros if prompted.
• Open your own workbook.
• In your workbook, press Alt + F8 , select the macro of interest, and click Run .

I hope this short tutorial has shed some light on Excel's very hidden sheets. I thank you for reading and hope to see you on our blog next week!

Sample workbook for download

You may also be interested in.

• How to unhide sheets in Excel
• How to hide worksheets in Excel
• How to hide Excel formulas
• How to show formulas in Excel
• Hiding and unhiding rows in Excel
• How to unhide columns in Excel

Table of contents

52 comments

As I need to unhide a sheet and go to particular cell in that sheet use if condition, Kindly sort out

Hello Thanks for sharing valuable tips I dowloaded a free spreadsheet on the web. I have been looking for the code in vain. How to find the code out and go trough it fr needed changement pls

Thank you very much. I learn something new today. It was very useful to me.

I HAD ONE WORKBOOK THAT CONTAINS 23 SHEETS IN IT AND I SAVED THAT DATA BEFORE I LEFT THE OFFICE DAY BEFORE YESTERDAY. NEXT DAY WHEN I OPEN THAT TO START WORK ON IT AGAIN IT SHOWING ONLY 11 SHEETS OTHER SHEETS ARE NOT SHOWING

Here is what I did. I had my xlsx file open. I wanted to unhide the entire worksheet (there was only 1 worksheet in this workbook) I clicked on the upper left hand corner of the sheet to highlight the entire worksheet I clicked on View & then Hide (I'm not sure why as I was trying to UN-hide a few hidden rows. Then the file shut down and closed. I found the file but cannot open it at all. I try VBX (Alt-F11) but would not open the excel file reporting an error 'could not be loaded'. Now what? Any help is appreciated. I can find the file but cannot open it. Part 2 - When I try to change the name of the file it says 'File In Use, close the file and try again'. But No way is it open unless it is so hidden I can't see it? Thanks Dan

Hi. i want to check if you can password protect very hidden sheets.

I want to check if my excel file has any hidden tabs / hidden columns/ hidden rows using asp.net and c# code. Can anyone help me how to code this?

HOW NICE THANKZ VERY HELPFULL TO ME

Great work. I tried to unhide a worksheet using your visual basic editor version. When I try to change from 0-xlSheetHidden to -1-xlSheetVisible i get the error "Unable to set the visible property of the Worksheet class" I recognize that all right mouse click function are greyed out. Is there a way to sort this

Hi! Did you find a way to solve it? I'm having the same problem in my workbook and I didn't find anything helpful.

I am also having this issue - have either of you figured out how to resolve it? Thanks!

I had the same issue and fixed it once I realized the sheet itself was password protected, when I meant to protect the workbook. Once I put in the password, I was unable to unhide the sheet

Have you tried saving the file as .xlsm that's what worked for me.

Whenever I hide a sheet that contains a pivot table I use in a macro. The macro stops working. Any way to get around this? Thanks for your help

I have tried to run the unhide very secret sheets macro and it generates the following error. Run - time error '1004' Method 'Visible' of object'-worksheet' failed

Can anyone point me in the right direction?

I have created an Excel worksheet called "BI" some time ago with a button called "Start" that starts a macro. If I set this worksheet to Design Mode and I move the cursor to this button the cursor changes to a hand with a pointed finger and not to an arrowed cross like other buttons that I have recently added. If I double click on the name of the sheet in VBA Project explorer it shows me the other buttons but not this one. Is this button very hidden? How do I unhide it?

YES! Thank you!!!!

Does anyone know whether a Very Hidden Sheet is calculated if a user has Auto Calc on / hits F9 / calculate on the Excel Interface?

Essentially I have a Macro process that will utilise \ calculate the Very Hidden Sheet when running, but I dont want the sheet to Calculate from the Excel Interface side. I want it to ONLY calculate when running the Macro - the end user still does need to be able to calculate the data on the Visible / Standard Hidden sheets.

You did help me today using this "In the Properties window, set the Visible property to -1 - xlSheetVisible."

More blessings to you!! Noel

Hello, I have come a cross excel macro with best features but hidden formular and even can't do anything to manipulate i.e like editing, paste formular or share(protected worksheets). Does workbook with license authority not able to be edited? ?

Also . . . in the use of an Array, can a sequential group of worksheets be identified? So the Array might list specific worksheets like "Cash Flow", "Financial Position", etc., but also include all worksheets between "Sales of AAA" and "Sales of ZZZ". That way, when I add worksheets in the future between Sales of AAA and Sales of ZZZ, they will automatically be VeryHidden.

Steve, This sample macro makes all the sheets between "Jan" and "May" very hidden; feel free to change these as needed. For the macro to work, the startIndex sheet (Jan) should come before endIndex sheet (May) in your workbook. ---

Public Sub VeryHideBetweenSheets()

Dim startIndex As Integer Dim endIndex As Integer

startIndex = ActiveWorkbook.Worksheets("Jan").Index endIndex = ActiveWorkbook.Worksheets("May").Index

If startIndex < endIndex Then For i = 1 To ActiveWorkbook.Worksheets.Count If i >= startIndex And i <= endIndex Then ActiveWorkbook.Worksheets(i).Visible = xlSheetVeryHidden End If Next i End If End Sub

Thank you so much for this helpful article!! One question: regarding the code to hide multiple worksheets that I have selected, how do I modify that code to use an Array of worksheets? In other words, when I run the macro, I want it to "VeryHide" the same worksheets every time: ("Cash Flow", "Financial Position", etc.)

Here you go:

Public Sub VeryHideSheets() ActiveWorkbook.Worksheets("Cash Flow").Visible = xlSheetVeryHidden ActiveWorkbook.Worksheets("Financial Position").Visible = xlSheetVeryHidden End Sub

Hi i am hafiz from bangladesh, i want to hide a specific workbook and userform & other excel want to open, but i don't know how its possible, please someone help me. regards Hafiz

Hello, Thanks , Very Useful Codes !

Best Regards

I ran both macros. i also tried to manually change the visibility in VBA Editor but it did not work. I can type and save the changes in the file but when I add a module in VBA editor, I am not able to save it. Thanks, Alireza

Hi Alireza,

This may be a stupid question, but anyway - did you save the workbook as macro-enabled (.xlsm)? Though, the fact that your manual changes in the VBA editor are not saved shows that the problem in not in the macros. BTW, what error do you get on saving? Unfortunately we are unable to identify the cause remotely because we have never run into such issue.

I appreciate your quick reply. Sorry that I fogort to mention that it was about the Excel file (2013 version) that I have. I can see the list of the worksheets under the VBAProjects in VBA Editor but they are hidden. Even your VBA code that i used to unhide them did not work. Thanks, Alireza

Thank you for the details, Alireza!

For the sake of clarity, did you run the UnhideVeryHiddenSheets macro or UnhideAllSheets, or both?

Also, there are two more things to check: - Is your workbook or worksheets(s) protected? - Try to unhide your sheet manually via the VBA Editor (please see "How to unhide a very hidden worksheet by changing its Visible property"). Did it work?

Hi Svetlana, I tried to unhide a sheet by using your VBA codes but i got the following error: Run-time error '1004': Method 'visible' of object'_Worksheet' failed I would like to know if there is any way to make the worksheet visible, please. Thanks, Alireza

I have just downloaded a sample workbook from this post and all the macros worked fine on my machine. Please specify your Excel version and localization, which macro your where trying to run, and whether you tried to unhide a sheet in our sample workbook or in your own one?

So here is the code from a macro i created to select a sheet and hide the other sheet (used for navigating from a dashboard to individual sheets, there is a corresponding macro to go back).

Sub gotohoursturnover() ' gotohoursturnover Macro Sheets("Home").Select Sheets("Dashboard (Hours & Turnover)").Visible = True Sheets("Home").Select ActiveWindow.SelectedSheets.Visible = False

My question is how do i make this statement "ActiveWindow.SelectedSheets.Visible = False" make the sheet very hidden?

I have tried replacing the "false" with "xlSheetVeryHidden" but it just throws up an error.

Yes it may be simple but i am just working out vba lol.

Hii there, I am not familiar with macros,i needed a code to hide all the sheets in my workbook except the first sheet. And at the same time i want the other sheets to be opened when the user selects the buttons provided in the first sheet. I used the following code & the sheets are getting hidden but the buttons wont take the user to next pages either.

Sub HideWorksheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name ThisWorkbook.ActiveSheet.Name Then ws.Visible = xlSheetHidden End If Next ws End Sub

Hi Svetlana, this post is very useful for my goal. However, if the other (visible) sheets refer to the part of the content of this very hidden sheet (e.g. =VeryhiddenSheet!A1), then the very hidden sheet becomes unprotected, does it? Everybody else can add a new formula (=VeryhiddenSheet!A2) to get its content.

Hi Daniel, You are absolutely right. If someone knows the name of a very hidden sheet, they can get its content with a formula. I don't know a 100% reliable way to prevent this.

Could you simply just set the worksheet as protected and have all cells that have a formula as locked and hidden in their properties. This will prevent others from seeing such formula referencing the veryhidden sheet.

The name of the very hidden sheet is visible in the VBA editor so, although locking the formula cells is a good idea, it is not 100% foolproof. If the very hidden sheet contains named ranges, the Name Manager also gives away the secret. The following is convoluted, but you could: - put your secret calculations in very hidden sheet #1 - use a second very hidden sheet #2 to refer only to the cells that you want the user to see - refer to the sheet #2 cells in the visible sheet #3 that the user accesses - go back to sheet #1 and add columns and rows to push the formula cells into obscure positions - now add random numbers and text to the empty cells of sheet #1 - set the very hidden parameters for sheets #1 & #2 - lock the workbook structure Even if the user finds the name of sheet #1, it is not possible to read the formulae and virtually impossible to find the real data as it is buried amongst the random stuff. Using the intermediate sheet #2 stops the user finding out where the 'good stuff' in sheet #1 is hidden.

I like this CLEVER STRATAGEM!!!

You can also be assisted in these other articles for tips and technics to shuffle and fill empty cells with useless data:

Random sort in Excel: shuffle cells, rows and columns: https://www.ablebits.com/office-addins-blog/excel-randomize-list-random-sort/

How to fill empty cells with 0, with value above/below in Excel: https://www.ablebits.com/office-addins-blog/fill-blanks-excel/

Hi Svetlana,

Really like the way you explain things.

Is there a way to write vba code that protects the structure of a workbook and that protects workbooks and worksheets without having a user input a password, but by using vba code in a "password = $%^&assl" type assignment statement?

Thanks for you help.

Hope you're having a great holiday season.

How do I address the hidden sheet in VBA?

With ThisWorkbook.Worksheets("Plates") myString = "!" & ThisWorkbook.Worksheets("Plates").Range(.Cells(2, 1), .Cells(lstrow, 3)).Address lbxPlate.RowSource = ("Plates" & myString) End With lbxPlate.Selected(0) = True

Thanks for any assistance,

Hi, when i run the macro ,i get run time error and shows "method visible of object _worksheet failed". Sheets still very hidden.Help...

Hi, when I run the macro myself I get error 400 and when I download your sheet and ALT+F8 I get a Runtime error 1004. Sheets still very hidden. HELP!

The first thought that comes to mind is that you are trying to unhide sheets in a protected workbook. To check this, please go to the Review tab > Changes group, and have a look at the Protect Workbook button. If it is highlighted, the workbook is protected, and you need to remove the protection first. For more information, please see How to unlock a protected workbook in Excel .

Hi Svetlana.

This was one of the most helpful blog with every detail. Could you tell me what are the steps to save this file with hidden sheets and send it to others without the danger of being visible. Can this sheet be protected?

Hi Tasneem,

Before saving the file, protect the Workbook structure with password: Review tab > Protect Workbook. This will prevent unhiding both hidden and very hidden sheets as well as hiding, renaming, moving, or deleting worksheets. For more information, please see How to protect a workbook in Excel .

Protecting a workbook will slow down the macros. Using interface only won't work to a protected workbook.. Thus calling a very hidden sheet without unhiding works?

I can not work with my Excel Sheet because it's protected. And many of these sheets are hidden in it, I can not unhide them ... please help me.

Share the workbook

Sir, Required a formula if a cell contains a word in that word 4th Letter P = individual and if it C = corporate

If my understanding of the task is correct, the following formula should work a treat:

=IF(MID(A1,4,1)="p", "individual", IF(MID(A1,4,1)="c", "corporate", ""))

Thanks svetlana, for ur share. ur post have been provoked me to know something knew everyday about excel.

Post a comment