How to password Protect when WORKSHEET is Very HIDDEN

[Invisible]

Hi,

I have a workbook that contains three worksheets but i have hidden on
worksheet (Sheet2) but i m retrieving data into sheet1 by formula,
this workbook goes to other users too and they can just click on Sheet
TAB and enable that sheet's visibility and see what the data is?

So my question is that how i can disable this right click on SHEET
TAB

OR

How can i protect this option by password like excel is providing this
facility for the whole sheet and workbook but if i only want to
protect this then how?

I saw that option which is in VBA PROJECT PROTIES but it doesn't work.

Kindly tell me step by step or provide me any example sheet or video.


I will be thankful to you.


Thanks
Husnain

NOTE: email me at (e-mail address removed)

 

[Test]

You need to use the Protect Workbook option

 

[Mike H]

Hi,

You can disable right click on sheet tabs with this

Application.CommandBars("Ply").Enabled = False

Mike

 

[Harlan Grove]

So my question is that how i can disable this right click on SHEET
TAB

Looks like your basic premise is that your users can't be trusted.
Taking that as given, there's no reliable way to disable menus since
all means of disabling menus require macros, but these untrustworthy
users can open your workbook with macros disabled. So macros aren't a
robust means of securing data in workbooks.

OR

How can i protect this option by password like excel is providing this
facility for the whole sheet and workbook but if i only want to
protect this then how?

Excel's internal passwords are easily overcome. Still, this could be
effective if your users are unsophisticated. Just protect the workbook
once you've hidden the worksheet.

However, if there are formulas in other worksheets that are visible to
users, and users can see formulas in the visible worksheets that refer
to the hidden worksheet, all they'd need to do is open another
workbook and use external references into the hidden worksheet in the
first workbook in order to exposure ALL THE DATA in the hidden
worksheet.

So there are no simple, direct means of securing data in Excel
workbooks.

You need to use complicated, indirect means instead. You could use
multiple levels of hidden defined names to mask the fact that they
ultimately refer to a very hidden worksheet, but in the end, if an
untrustworthy user can see a formula referring to a hidden name like
XYZ in YourWorkbook.XLS, they'd only need to open another workbook and
enter the formula

=ADDRESS('YourWorkbook.XLS'!XYZ,0,0,1)

to uncover the name of your hidden worksheet. Once they discover it's
named VERYVERYPRIVATE, they could populate a worksheet in the new
workbook they created with formulas like

A1: ='[YourWorkbook.XLS]VERYVERYPRIVATE!A1

and fill that A1 into A1:IV65536 to expose all the data in
VERYVERYPRIVATE.

This doesn't even need VBA. With VBA, users could list ALL worksheets
in any other OPEN workbook, even very hidden worksheets in protected
workbooks.

In short, there's NO WAY to protect data in Excel workbooks users can
OPEN. Once a user opens a workbook, Excel provides MANY different ways
to access ALL the data in ALL of the worksheets in that workbook.

If you absolutely need to protect your data, you can't use Excel to
hold that data. Sorry, there's no way around this. If you want to use
limited filtered secured data in Excel workbooks, you need to fetch it
from secure data sources using queries. THERE IS NO ALTERNATIVE.

 

[Shane Devenshire]

Hi,

a number of options:

1. You turn on the Format, Cells, Protection, Hidden option for the hidden
sheet. Then you can apply the custom format ;;; (three semi-colons) to all
the cells you want hidden on that sheet - Format, Cells, Number tab, Custom.
Then you can password protect the sheet.
2. You can hide the sheet, without code, choosing Format, Sheet, Hide.
Then you can choose Tools, Protection, Protect Workbook and check Structure
and add a password.
3. You can use the VBA routine to make the sheet very hidden, however,
unless you protect the module the sheet can still be exposed. In the VBE
choose Tools, VBAProject Properties, Protection tab, check Lock project for
viewing, and add a password.

 

[Harlan Grove]

3. You can use the VBA routine to make the sheet very hidden, however,
unless you protect the module the sheet can still be exposed.  In the VBE
choose Tools, VBAProject Properties, Protection tab, check Lock project for
viewing, and add a password.

Users can disable macros, so protecting VBA modules is no guarantee
macros would be run, unchanged or not.

Also, VBA passwords are only slightly more difficult to bypass than
worksheet/workbook passwords. If you have a hex editor, a web browser
and an internet connection, you have all you need to unlock VBA
modules.

But this is mostly irrelevant. While it's possible to hide FORMULAS
from less sophisticated users, it's impossible to hide DATA (including
formulas' values) from users. Until Excel includes a security/
protection option that prevents evaluation of external references into
such protected workbooks, once a user knows that sheet XYZ is the
hidden worksheet in ABC.XLS with all the sensitive data, they just
need to open a new workbook and enter formulas like ='[ABC.XLS]XYZ'!A1
in cell A1 of a worksheet in the new workbook and fill it into the
rest of the worksheet, and all of the sensitive data is exposed.

The OP has already decided his/her users are untrustworthy. If they're
also a bit dim, then the methods mentioned by others in this thread
may work. OTOH, if they're relatively clever, then there's no
protecting data in Excel workbooks those users can open.