TIPS ON A GENERAL STRUCTURE TO START WITH, ANYBODY?

  • Thread starter Thread starter robzrob
  • Start date Start date
R

robzrob

I want a workbook where all employees’ working hours, flexitime,
overtime, leave, etc can be recorded by themselves. Each would have
their own worksheet for viewing, inputting, amending, etc.

Restrictions. We have 4 levels: ‘Workers’, Team Leaders, a Section
Mgr, a Dept Mgr. Workers should have view/amend access to their own
worksheet, Team Leaders: view/amend access to their own + view only
access to all Workers, etc.

I’m thinking of a shared workbook with passwords for each type of
staff member. How would I do something like ‘Add new staff member’
bearing in mind that each staff worksheet has its own formulae in it
and macros associated with it?

Would be grateful for any general ideas.
 
On a similar project, I coded the workbook_open event to check the userID of
the person opening the workbook, and based on that, it showed that person's
sheet (or if it was a manager, showed the manager's sheet and their direct
reports). That way, the regular user woudn't have to sift through dozens of
sheets to find their own; their own was the only one that was visible. You
could easily include a line of code to protect the direct report sheets from
any manager edits, if desired.

I hardcoded my array of employees and the sheetnames to show/hide, but if I
had to do it again I might be inclined to just make a hidden sheet with that
list in a range, and have that sheet unhide only when it was my userID
opening the workbook.

I think that checking the userID is much easier than having a bunch of
different passwords, especially if the employees will be calling you every
time they forget a password.

As for adding a new sheet- I just kept one blank template sheet, and anytime
I needed to add an employee I just made a copy of that blank sheet, and
renamed the tab with that person's userID. I did it manually, but this could
be done with code as well.

HTH,
Keith
 
On a similar project, I coded the workbook_open event to check the userIDof
the person opening the workbook, and based on that, it showed that person's
sheet (or if it was a manager, showed the manager's sheet and their direct
reports). That way, the regular user woudn't have to sift through dozens of
sheets to find their own; their own was the only one that was visible. You
could easily include a line of code to protect the direct report sheets from
any manager edits, if desired.

I hardcoded my array of employees and the sheetnames to show/hide, but ifI
had to do it again I might be inclined to just make a hidden sheet with that
list in a range, and have that sheet unhide only when it was my userID
opening the workbook.

I think that checking the userID is much easier than having a bunch of
different passwords, especially if the employees will be calling you every
time they forget a password.

As for adding a new sheet- I just kept one blank template sheet, and anytime
I needed to add an employee I just made a copy of that blank sheet, and
renamed the tab with that person's userID. I did it manually, but this could
be done with code as well.

HTH,
Keith








- Show quoted text -
Click to expand...

Thnks - I think I can start now.
 
Back
Top