Keeping sheet tabs hidden

H

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769
Hey all. I have the sheet tabs hidden in my workbook, but I really want to make sure they stay hidden even from a user who knows how to go into the excel options and check the "Show sheet tabs" check box. Is there any way to protect my sheet so that it requires a password to show the sheet tabs? Any help would be greatly appreciated.

Also, if there isn't any way to do this, is there any way to completely hide a sheet, but make it accessible when a macro is run? My problem is I have a sheet that contains a number of passwords, which is accessed by a password protected macro. I need certain users to be able to get to this sheet, but I don't want anyone else to be able to access it.

Thanks in advance for any help you can provide.

Hank
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
One option would be to go into the VBA editor and set the sheets to "Very Hidden". That way the only way to unhide them is through VBA code or by changing the visible property iin the VBA properties window. Then you can password protect the VBAProject.
 
Upvote 0
Open your VBA editor (shortcut is ALT+F11), and in the Project pane, click on the worksheet you want to make very hidden. Go to the Properties pane (if this pane is not open, press F4), and in this pane at the very bottom is the Visible property. You can use the dropbox here to choose "Very Hidden".
 
Upvote 0
Got it. So in my macros that access the very hidden sheets, I'll first have to change that property using vba and then change it back once thesheet is left?
 
Upvote 0
Ideally, your macros should never be "selecting" or "activating" sheets to begin with. As long as they are doing neither of the above, then there is no need to have a macro unhide/hide sheets.
 
Upvote 0
So how would I go about taking the user to the Passwords sheet without activating that sheet???
 
Upvote 0
hallingh said:
So how would I go about taking the user to the Passwords sheet without activating that sheet???
Click to expand...


Ahh - I didn't read your post carefully enough. Yes, you will need to have a macro make the sheets visible and then hide it again.

Use the following to make the sheets Visible and VeryHidden

Code: 
Sheets("PasswordSheet").Visible = True
Sheets("PasswordSheet").Visible = xlSheetVeryHidden

Consider having a Worksheet_Deactivate event on the PasswordSheet that makes it very hidden, that way when the user chooses a different worksheet, it will automatically hide that sheet.
 
Upvote 0
Thanks for the code. I have a Submit_Changes macro that the user hits on the password sheet that right now just goes back to the main sheet and saves teh workbook, but I will just have that set it back to very hidden. Thanks a lot for the help, it saved me a lot of time!

Hank
 
Upvote 0
So when I try to change the sheet to veryhidden it gives me an error box telling me it is "unable to set the visible property of the worksheet class." Any ide awhat the issue might be with this???
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Normal access to sheet but with hidden tab/shortcut
Replies
2
Views
430
mrfahaji
M
L
  • Solved
Restricting specific user sheets
2 3
Replies
23
Views
2K
Linki
L
JoshLyman
  • Solved
Hide/protect on close
Replies
2
Views
378
JoshLyman
JoshLyman
A
  • Question
Copy a sheet with hidden rows and past to another sheet keeping formula at some cells
Replies
1
Views
243
Candyman8019
C
S
  • Solved
Protected Sheet crash when use the tab key
2
Replies
10
Views
687
Sergio
S

Forum statistics

Threads
1,214,899
Messages
6,122,155
Members
449,068
Latest member
shiz11713

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top