Restricting access to data
 

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??

Restricting access to data
 

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" wrote:

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??

Restricting access to data
 

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


"?B?S2V2aW4=?=" wrote in
:

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" wrote:

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??