Want to REALLY hide some sheets with Excel.XlSheetVisibility.xlSheetVeryHidden

[mbdigital]

Hi all.

Maybe somebody knows a lot more about SheetVisibility.xlSheetVeryHidden than I do.

I want some sheet in my workbook (not all) to really hide so that they can't be seen in the "right-click on the tabs and unhide" way.

I know that you have to use the following code for it

Please Login or Register  to view this content.

But how can I put this code in each sheet which I want to really hide?

Can somebody give me an example ?

[HaHoBe]

Hi, mbdigital,

no need to put the code in any sheet - you could place it in ThisWorkbook and refer to each worksheet by name.

Code may lok like this (standard module, adjust the sheet names to suit and add more if needed):

Please Login or Register  to view this content.

This code must be started one way or another, could be the opening of the workbook.

Ciao,
Holger

[mbdigital]

Hi Holger.

Thanks for your (super quick) help in this.

You said "the code must be started ...."

I have already the following (very big but I will only display the beginning here) code to show some sheets depending on some cell-values :

Can I put your code somewhere between this ?

Please Login or Register  to view this content.

[HaHoBe]

Hi, mbdigital,

maybe just replace the values for True and False (visible and hidden) accordingly:

Please Login or Register  to view this content.

Ciao,
Holger

[mbdigital]

Hi Holger.

This works very good.
Thanks for this !!

The only thing I would like to ask is the following :

Untill now I have used this code with some extra commands, like Select "Case Worksheets" and so on.

Is there a way to tell to a sheet (maybe in the code of this particular sheet ??) that it has to be VeryHidden always ?
Because now when a (secret) macro is called to unhide a page and later on it is hidden again (via the normal right-click on the tab and choose hide)
the page is not VeryHidden anymore.

[HaHoBe]

Hi, mbdigital,

I must admit I lack the understanding of making a very hidden sheet visible to any user (except Admin/Programmer where I use a different approach by checking the username on the system due to working on different PC).

If it´s just one of any sheets at a time adjust the code names of the sheets you will find in the project explorer. Code goes into ThisWorkbook:

Please Login or Register  to view this content.

If more than one sheet has been made visible use a loop over all sheets, compare the codename and take action as needed making sure that at least one sheet remains visible within the workbook.

Ciao,
Holger

[mikerickson]

To permanently VeryHide a worksheet, you can go into the VB Editor, select the sheet from the Excel Objects list and use the Proprerty Window to set the default visibility.

[mbdigital]

Hi mike. Can you please give me an example of this code?
I'm not so good in VBA codes. Have to learn a lot as beginner.

[mikerickson]

I was describing a way to VeryHide a worksheet without using code.
At design time, one can use the Property Window to set the .Visible property of a worksheet.

You don't need any code (unless you want to un-hide it during run time)

[HaHoBe]

Hi, mikerickson,

Because now when a (secret) macro is called to unhide a page and later on it is hidden again (via the normal right-click on the tab and choose hide)
the page is not VeryHidden anymore.

I wonder what´s the use to explain how to hide a sheet via the project explorer when VBA is used to make it visible - I´d used some VBA to make it very hidden again. But I´m certainly mistaken by thinking so.

Ciao,
HOlger

[mikerickson]

I didn't read closely.

A different version of your approach (depending on the desrired user experience) would be to put this in particular sheets' code modules. This would make those sheets sometimes visibile, sometimes VeryHidden, but (simple) Hideable only via code.

Please Login or Register  to view this content.

[mbdigital]

Hey Mike.

This is exactly what I was looking for.
And it works perfect!

Thanks for your help (also many thanks to Holger for your great help)

Best regards,
Maarten (Netherlands)