Preventing someone from unhiding a sheet

[Neil]

Hi all, I have put together a bulky sheet that uses
several sheets as data tables, the sheets are hidden, but
I want to prevent people from unhiding them, is there a
way to do this ?

 

[Dave Ramage]

You can hide them in VBA using the xlVeryHidden option-

Sheets("Sheet1").Visible = xlSheetVeryHidden

This way, the only way to unhide the sheet is to use VBA-

Sheets("Sheet1").Visible = True

Alternatively, you could protect the workbook
(Tools/Protection/Protect Workbook), although this will
prevent the user adding or deleting sheets etc. Also note
that any password under 10-15 characters long can be
broken fairly quickly.

Cheers,
Dave

 

[GB]

Hi all, I have put together a bulky sheet that uses
several sheets as data tables, the sheets are hidden, but
I want to prevent people from unhiding them, is there a
way to do this ?

Beware, Neil, even when very hidden and protected, the contents are still
visible to another Excel workbook - I guess they need to be so the workbooks
can interlink which is a feature of Excel.

So, a user can see all the values (but not the formulae) in the hidden
sheets. For example, if you have a column headed 'our buying-in price' you
should be aware that an end-user who is not too computer-literate can easily
find that info. If the end user knows what he is doing, he can break into
the whole spreadsheet with ease. So, spreadsheets are not the ideal vehicle
if they need to contain proprietary information.

Geoff

 

[Neil]

Thanks Dave, I created two subs one to hide them and one
to unhide them, then a thought struck me, the user can
simply hit the run macro button and pick the unhide from
the list, is there anyway to prevent the user from
hitting thr un button and seeing all the sub routines
that are available ?

 

[Bob L.]

Neil,
Make your sub private, i.e., Private mysub()
Bob L.