Hi all,

I am looking to create a workbook to track salaries and changes for the
company, with the ability for each manager to open it and update the relevant
details. What I would like, though, is a way to restrict the access so that
each manager can only see his/her team.

Is it possible to create a type of "log in" when the file opens, and have
that determine the available records? Can I utilize the security functions to
limit viewable access? Is there a better/easier way? All comments appreicated.

Regards,

Alan

Alan,
First, the security available through workbook and worksheet protection is
really more for preserving appearance and layout than actually providing
security. Anything you created in a macro to act as a login script could be
circumvented by the simple process of holding down the [Shift] key while
opening the workbook.

My recommendation would be to (at a minimum) create a separate .xls file for
each of your managers, those would only have their team member's information
in them. Protect each of those workbooks with a unique password and only
give that to the appropriate manager. Then you keep another workbook that
has one worksheet associated with each of the other workbooks and set up a
simple link from cell A1 on each of the individual workbooks and extends out
to be a link to all needed cells on each of those other workbooks sheet(s).
This way when you open up the 'master' .xls file it would retrieve all of the
data from the other workbooks used individually by the various managers.

To 'demonstrate' further: in your master copy, you might have a worksheet
named "MacsTeam" and manager Mac has access to a workbook named "MTeam.xls"
in cell A1 of your master copy, sheet 'MacsTeam' you would have a formula
that would look like this (assuming that MTeam.xls is open when you set it
up) -
=[MacsTeam.xls]Sheet1!$A$1
first thing to do is change that to remove the absolute addressing:
=[MacsTeam.xls]Sheet1!A1
now you can fill that formula in your workbook so that all entries in all
used cells in his workbook is echoed in yours. Repeat for all the other
individual team workbooks.

----
Now, if you want to try with macro code and a login script, I'd do it this
way:
Have a single sheet in the workbook that is visible and is basically a
welcome/instruction sheet. Might even have a button on it to start up a
login dialog built into the workbook.

All worksheets would initially have their visible property set to
xlVeryHidden. This is a setting that keeps their names from even being seen
in the Format Worksheets Unhide list. Your login gets name/password from the
manager and then makes the appropriate sheet(s) visible to him/her.

Now the key here is to provide password protection to the VBA Project so
that no one can easily get into it to see the passwords in use. Password
protection of the VBA Project is much stronger than that for the
worksheet/workbook.

Additionally, the workbook's OnOpen() even should set all worksheet's
visible property except that "welcome" sheet to the xlVeryHidden state and
perhaps offer up the login dialog. As an extra precaution, the workbook's
_BeforeClose() event could again make sure all sheets with personnel data on
them are set to xlVeryHidden. Finally, each worksheet's _Activate() code
segment could be set up to check and make sure that the current password in
use is the one for the person with permissions to view/change that worksheet
(probably need two levels of password checking: one for you so you can see
all sheets at once, and the other for the managers so they are only looking
at proper sheets for their use).



"Alan Smith" wrote:

Hi all,

I am looking to create a workbook to track salaries and changes for the
company, with the ability for each manager to open it and update the relevant
details. What I would like, though, is a way to restrict the access so that
each manager can only see his/her team.

Is it possible to create a type of "log in" when the file opens, and have
that determine the available records? Can I utilize the security functions to
limit viewable access? Is there a better/easier way? All comments appreicated.

Regards,

Alan

Thanks J,

The second part of your reply is pretty much what I was looking for.

Alan

"JLatham" wrote:

Alan,
First, the security available through workbook and worksheet protection is
really more for preserving appearance and layout than actually providing
security. Anything you created in a macro to act as a login script could be
circumvented by the simple process of holding down the [Shift] key while
opening the workbook.

My recommendation would be to (at a minimum) create a separate .xls file for
each of your managers, those would only have their team member's information
in them. Protect each of those workbooks with a unique password and only
give that to the appropriate manager. Then you keep another workbook that
has one worksheet associated with each of the other workbooks and set up a
simple link from cell A1 on each of the individual workbooks and extends out
to be a link to all needed cells on each of those other workbooks sheet(s).
This way when you open up the 'master' .xls file it would retrieve all of the
data from the other workbooks used individually by the various managers.

To 'demonstrate' further: in your master copy, you might have a worksheet
named "MacsTeam" and manager Mac has access to a workbook named "MTeam.xls"
in cell A1 of your master copy, sheet 'MacsTeam' you would have a formula
that would look like this (assuming that MTeam.xls is open when you set it
up) -
=[MacsTeam.xls]Sheet1!$A$1
first thing to do is change that to remove the absolute addressing:
=[MacsTeam.xls]Sheet1!A1
now you can fill that formula in your workbook so that all entries in all
used cells in his workbook is echoed in yours. Repeat for all the other
individual team workbooks.

----
Now, if you want to try with macro code and a login script, I'd do it this
way:
Have a single sheet in the workbook that is visible and is basically a
welcome/instruction sheet. Might even have a button on it to start up a
login dialog built into the workbook.

All worksheets would initially have their visible property set to
xlVeryHidden. This is a setting that keeps their names from even being seen
in the Format Worksheets Unhide list. Your login gets name/password from the
manager and then makes the appropriate sheet(s) visible to him/her.

Now the key here is to provide password protection to the VBA Project so
that no one can easily get into it to see the passwords in use. Password
protection of the VBA Project is much stronger than that for the
worksheet/workbook.

Additionally, the workbook's OnOpen() even should set all worksheet's
visible property except that "welcome" sheet to the xlVeryHidden state and
perhaps offer up the login dialog. As an extra precaution, the workbook's
_BeforeClose() event could again make sure all sheets with personnel data on
them are set to xlVeryHidden. Finally, each worksheet's _Activate() code
segment could be set up to check and make sure that the current password in
use is the one for the person with permissions to view/change that worksheet
(probably need two levels of password checking: one for you so you can see
all sheets at once, and the other for the managers so they are only looking
at proper sheets for their use).



"Alan Smith" wrote:

Hi all,

I am looking to create a workbook to track salaries and changes for the
company, with the ability for each manager to open it and update the relevant
details. What I would like, though, is a way to restrict the access so that
each manager can only see his/her team.

Is it possible to create a type of "log in" when the file opens, and have
that determine the available records? Can I utilize the security functions to
limit viewable access? Is there a better/easier way? All comments appreicated.

Regards,

Alan

The actual coding part of it all is pretty easy. For the individual sheets,
it's even repetitious - could have on function doing the checking with a
simple call to the function from the sheet's _Activate() event giving the
sheet name to the function so it could check against valid password and
authority level.

If you need help with any of it, feel free to contact me at
HelpFrom @ jlathamsite.com (remove spaces)

"Alan Smith" wrote:

Thanks J,

The second part of your reply is pretty much what I was looking for.

Alan

"JLatham" wrote:

Alan,
First, the security available through workbook and worksheet protection is
really more for preserving appearance and layout than actually providing
security. Anything you created in a macro to act as a login script could be
circumvented by the simple process of holding down the [Shift] key while
opening the workbook.

My recommendation would be to (at a minimum) create a separate .xls file for
each of your managers, those would only have their team member's information
in them. Protect each of those workbooks with a unique password and only
give that to the appropriate manager. Then you keep another workbook that
has one worksheet associated with each of the other workbooks and set up a
simple link from cell A1 on each of the individual workbooks and extends out
to be a link to all needed cells on each of those other workbooks sheet(s).
This way when you open up the 'master' .xls file it would retrieve all of the
data from the other workbooks used individually by the various managers.

To 'demonstrate' further: in your master copy, you might have a worksheet
named "MacsTeam" and manager Mac has access to a workbook named "MTeam.xls"
in cell A1 of your master copy, sheet 'MacsTeam' you would have a formula
that would look like this (assuming that MTeam.xls is open when you set it
up) -
=[MacsTeam.xls]Sheet1!$A$1
first thing to do is change that to remove the absolute addressing:
=[MacsTeam.xls]Sheet1!A1
now you can fill that formula in your workbook so that all entries in all
used cells in his workbook is echoed in yours. Repeat for all the other
individual team workbooks.

----
Now, if you want to try with macro code and a login script, I'd do it this
way:
Have a single sheet in the workbook that is visible and is basically a
welcome/instruction sheet. Might even have a button on it to start up a
login dialog built into the workbook.

All worksheets would initially have their visible property set to
xlVeryHidden. This is a setting that keeps their names from even being seen
in the Format Worksheets Unhide list. Your login gets name/password from the
manager and then makes the appropriate sheet(s) visible to him/her.

Now the key here is to provide password protection to the VBA Project so
that no one can easily get into it to see the passwords in use. Password
protection of the VBA Project is much stronger than that for the
worksheet/workbook.

Additionally, the workbook's OnOpen() even should set all worksheet's
visible property except that "welcome" sheet to the xlVeryHidden state and
perhaps offer up the login dialog. As an extra precaution, the workbook's
_BeforeClose() event could again make sure all sheets with personnel data on
them are set to xlVeryHidden. Finally, each worksheet's _Activate() code
segment could be set up to check and make sure that the current password in
use is the one for the person with permissions to view/change that worksheet
(probably need two levels of password checking: one for you so you can see
all sheets at once, and the other for the managers so they are only looking
at proper sheets for their use).



"Alan Smith" wrote:

Hi all,

I am looking to create a workbook to track salaries and changes for the
company, with the ability for each manager to open it and update the relevant
details. What I would like, though, is a way to restrict the access so that
each manager can only see his/her team.

Is it possible to create a type of "log in" when the file opens, and have
that determine the available records? Can I utilize the security functions to
limit viewable access? Is there a better/easier way? All comments appreicated.

Regards,

Alan