Restricting access to data

[Richard H Knoff]

I have a workbook with job satisfaction data for several units in
a large organization. The data is in a hidden sheet. All
respondents enter a code designating which unit they belong to.

The workbook user can view results for different units by entering
the right code into a "Filter" cell. The codes are hierarchical -
"1" produces total results, "1a" produces results for the "1a"
division, "1a1" and "1a2" produce results for two units in the
"1a" division, etc.

I want to distribute the workbook to managers at all levels, while
restricting them from viewing results from units they don't have
authority over. That is, the manager of "1a1" should be able to
enter codes like "1a1", "1a1a" or "1a1b2", but not "1b", "1" or
anything not beginning with his own code.

I kind of think it should be possible for me to enter the
authorized code in a password protected cell, and have the
"Filter" cell test the code entered against the code in that cell
before allowing it - but I don't have any idea how this could be
accomplished.

By the way, the "Filter" cell already has a validation rule
refusing any code containing a "?", as this represents a unit with
less than 5 employees. This validation rule runs like this -
translated from Norwegian:

=IF(ISERROR(FIND("?";Filter;2)<1);FIND("1";Filter;1)>0;FIND
("?";Filter;2)<1)

Would this really be possible??

 

[Guest]

Richard,

What your wanting to do may actually be easier in a database like Access.
That said: your can use the vlookup command as follows:

useraccesslvl= Application.WorksheetFunction.VLookup(username, accesslvlRng,
2)

where username is the mangers name or some short string that represents the
user. If your using a VBA form this would be referenced from the form.
"accesslvlRng" is a range object before you execute this command. You declare
the range as follows:

accesslvlRng= Worksheets(1).Range("A1:B5")


The data in A1 to B5 would look something like this (if I understand your
process):

row A B
A1 A1A
A1 A1B
A1 A1C
A2 A2A
A2 A2B

etc.

The dynamically populate a list based on the user choosing A1, A2, etc. I
know you can dynamically populate that list, but I am not sure how off hand.

You can limit a users access the A1 or A2 using a similar process. When the
user starts your app, the first thing they are presented with is a form where
they would enter their username and password. use the vlookup again to
retrieve the users password and compare them.
If the password is not correct, allow them the either enter another password
or quit the application. If the passwor compares, write to a specific cell in
one of the worksheets the A1, A2 value etc. Use this value to populate your
list of A1A, A1B, etc.

This will require considerably more thought, but that is the basic idea. I
hope that helps! Without actually doing it and writing and testing code, that
is the best I can do at this point.

Hope that helps!

Kevin

 

[Richard H Knoff]

Kevin,

thank you for your advice. I'll have to look into it further; I
don't quite understand what it means to dynamically populate the
list.

The password approach is an interesting way in - actually I was
thinking about creating a copy of the file for each user,
including a protected code (like "1a1b") that would restrict the
range of codes the user would be allowed to enter in the Filter
cell. I guess both methods mean quite a bit of administrative work
....

Regards,
Richard