Keeping sheet tabs hidden

[hallingh]

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

 

[MrKowz]

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???

What is your code?

 

[hallingh]

My code on the password sheet? Just this:

Private Sub SubmitPassword()
Sheets("Data Entry Portal").Select
End Sub

The sheet hardly has anything on it.

 

[MrKowz]

The code that is erroring.

 

[hallingh]

It isn't really an error on the code. A message just pops up that says "Unable to set teh visible property of the worksheet class." It isn't highlighting any code, and the message box just has two options, OK and Help. When I click help, it takes me to the main page of Microsoft Excel Online help.

 

[MrKowz]

What code is being ran when that pops up? That popup indicates an error in the code. Try unprotecting every sheet in the workbook, then running that code, see if you can choose the "debug" button and check where it highlights.

The code I supplied works fine on my end, tested under various scenarios.

 

[hallingh]

There is no debug option. All of my sheets are password protected though, so I'll try unprotecting them and see if that is the issue.

 

[shg]

I think first in the VBE you'll need to do Tools > Options, General tab, tick Break in Class Module

 

[hallingh]

Got it. The workbook's structure was protected, so it wouldn't let me change the visibility. All good here. Thanks again!

 

[hallingh]

OK, now I have a question about the veryhidden property. Sometime in the next few weeks I want to add some dynamic charts to my workbook. I have a data archive sheet that I will be drawing the data from for the charts. Can I keep these sheets veryhidden but still use the data from them in the charts? Or does having them veryhidden prevent other sheets/charts from accessing their contents? Thanks for any insight you can give me. I've never used the veryhidden property before, so I don't know much about it. Thanks!

Hank

 

[MrKowz]

The veryhidden propery just prevents users from being able to unhide them through conventional means. You can still refer to data, use data, etc on these sheets.