preventing referencing hidden cell

[zxcv]

I have a spreadsheet that is used by several users and the users can
be added dynamically by an admin. We do not want users to be able to
see each other's data so when a user opens the file he is prompted for
a password that is stored on an xlSheetVeryHidden sheet and is only
allowed to see his own sheet. There are other controls in place to
prevent the user from editing any sheets or macros.

The problem is that a very smart user could see another user's data by
disabling macros when opening and then (assuming he knows the name of
the other user's sheet which would not be hard to figure out) type in
a formula like:
=OtherUser!A1

and be able to see the other user's data. Any way to prevent this?
Note that I cannot just password protect the workbook because they all
need access to the same workbook. If I password protect the sheet that
opens with macros disabled he can just insert another sheet and put
the formula there.

 

[Donald Guillett]

I have a spreadsheet that is used by several users and the users can
be added dynamically by an admin. We do not want users to be able to
see each other's data so when a user opens the file he is prompted for
a password that is stored on an xlSheetVeryHidden sheet and is only
allowed to see his own sheet. There are other controls in place to
prevent the user from editing any sheets or macros.

The problem is that a very smart user could see another user's data by
disabling macros when opening and then (assuming he knows the name of
the other user's sheet which would not be hard to figure out) type in
a formula like:
    =OtherUser!A1

and be able to see the other user's data. Any way to prevent this?
Note that I cannot just password protect the workbook because they all
need access to the same workbook. If I password protect the sheet that
opens with macros disabled he can just insert another sheet and put
the formula there.

Just a thought. A code name for the sheet contained in a selectcase
within the unprotect macro

 

[AB]

I don't know if it's an overkill for you but i usually deal with
situations like this by splitting it up into FrontEnd and BackEnd.
Meaning i've got :
- BE file (usually .mdb) that stores all the data for all the users
- FE file - the .xls/.xlsx or .xlt/.xltx file - that has just
MsQueries in it (i.e., the data are stored in the BE Access but the FE
Excel just pulls/displays the data to the user in Excel).
Now, the FrontEnd file has openworkook macro that inserts/manipulates
SQL statement for the MsQuery so that:
-- if macros disabled = no data are returned
-- if macros enebled = the code recognizes the user
(environ("Username")) and manipulates the SQL (commandtext) of the
MsQuery (querytable object) and returns only those records that the
user is allowed to see.

If the users need not only to see but also manipulate the data you'd
surely still need to figure out how to write the data back into the
BE.

As I said - might be an overkill for what you need - but i find it
works really good for my solutions.

 

[zxcv]

I don't know if it's an overkill for you but i usually deal with
situations like this by splitting it up into FrontEnd and BackEnd.
Meaning i've got :
- BE file (usually .mdb) that stores all the data for all the users
- FE file - the .xls/.xlsx or .xlt/.xltx file - that has just
MsQueries in it (i.e., the data are stored in the BE Access but the FE
Excel just pulls/displays the data to the user in Excel).
Now, the FrontEnd file has openworkook macro that inserts/manipulates
SQL statement for the MsQuery so that:
-- if macros disabled = no data are returned
-- if macros enebled = the code recognizes the user
(environ("Username")) and manipulates the SQL (commandtext) of the
MsQuery (querytable object) and returns only those records that the
user is allowed to see.

If the users need not only to see but also manipulate the data you'd
surely still need to figure out how to write the data back into the
BE.

As I said - might be an overkill for what you need - but i find it
works really good for my solutions.

Thanks. Both good ideas. Maybe a little more complicated than I want
to go but it might work.