Excel Timesheet - Restricting Access by Sheet to Individuals

Baseball15

New Member
Joined
Nov 13, 2018
Messages
3
I am pretty new to Excel but learning as I go......

I built a timesheet for my church's employees in Excel and working well. I would like all timesheets for a pay period to reside in the same workbook on different tabs primarily to help with staff efficiencies..
For example, there are 8 people using the timesheet so the workbook would have 8 tabs (sheets), 1 unique to each employee. For this example, employee names are April, Brenda, Charlie, David, Ethan, Frank, Grace, and Henry.

April needs to be able to update her timesheet but restricted from viewing or updating anyone else's timesheet, and the other 7 employees are restricted from viewing or updating April's timesheet. It would be even better if April could only see her tab in the workbook if that is even possible.

Brenda needs to be able to update her timesheet but restricted from viewing or updating anyone else's timesheet, and the other 7 employees are restricted from viewing or updating Brenda's timesheet. It would be even better if Brenda could only see her tab in the workbook if that is even possible.

Etc. for the other employees and their timesheets.

I also need to allow their boss to see/update all employee timesheets (all workbook tabs), like a super user.
I also need to allow the finance person who does payroll to have this same super user access.

If you are able to help me resolve this issue, being fairly new I would appreciate as much detail as you could provide and I greatly thank you in advance.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi!

It's possible to do all of those things, but you need to specify for us which way you prefer to distinguish one user from another. There are two ways I can think of off hand. Simply using a user login form (this is called a "Userform" in Excel),
User Log-in Form.PNG


And another is, if you all happen to use Microsoft Outlook (and you each have your own computers), the following code can be used to detect who is viewing the spreadsheet by the Outlook account that's tied to the computer. (This way they don't have to sign in to the sheet at all, but it is of course less secure than the user login form.
VBA Code:
Sub Test__My_Email_Address()
MsgBox My_Email_Address
End Sub
Function My_Email_Address()
My_Email_Address = CreateObject("Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(6).Parent.name
End Function

It would be even better if Brenda could only see her tab in the workbook if that is even possible.
Yes, Brenda's tab can be hidden from the other users' view. You can right click on a tab and select Hide, but in Excel VBA (the programming language of Excel), there is a thing called "very hidden"
VBA Code:
Sheets(name).Visible = xlSheetVeryHidden

which means that it cannot be unhidden by right clicking on any sheet tab and selecting Unhide. It's easy to have it so that all of those with super privileges will be able to see her sheet, etc. And the way she (and they) see her sheet is by the VBA program making the sheet visible when it knows it's one of them viewing it.
 
Upvote 0
Thank you so much for responding and the detail of your comments!!!

Based on your comment about security, the "userform" being more secure seems the approach I should use. I am not 100% sure all have Outlook and some complete the form using their phone instead of a computer. Does their use of phone have an impact on any of my questions?

Since I have not written VBA code previously, does this code go into a workbook or a worksheet? Is VBA code global across a workbook or unique to a worksheet? Is there any issue to the VBA code or the userform if multiple employees attempt to enter time in the workbook updating different sheets?
 
Upvote 0
Just in case, which version of Office do you use? (And does everyone use that same version (or later?).)

the "userform" being more secure seems the approach I should use
Fair enough.

And logging in will not be enough, but it is all they need to physically do from their end (on a regular basis).

That is, if you all choose to try this out, when they create credentials for logging in, they will need to create/update their credentials from a computer that no one else uses, or at least from a specific user account that is assigned to that user, should more than one user potentially use the same computer. (There can be an option to add multiple computers/usernames to the same user, but all must be uniquely for that individual! And of course, all computers/user accounts need to be password protected.) Otherwise this won't be secure.

(And there is another important note/condition for using this current system design proposal. It's mentioned at the end of the post, but you will probably not understand until you read the rest of it.)

The reason for the extra "login account information" for computer/username is because how can Excel detect that it's you if people are constantly opening, closing, saving, and/or editing the Workbook, potentially at the same time? The login form can be used to detect who was the last to log in to the Workbook (or how many people logged in that day, how often they did, etc.), but that doesn't help detecting who is currently making a change to the Workbook.

Every time the Excel Workbook is saved, opened, or closed by any of the staff, it will need to check whose computer it is (or user account), and if that computer is the computer (or user account) that is associated with the current user. For example, you can run the following sub which displays your computer's name:
VBA Code:
Sub Whats_My_Computer_Name()
MsgBox Environ$("computername")
End Sub

And you can see the current user account with the code:
VBA Code:
Sub Current_User_Account()
MsgBox Application.UserName
End Sub

The login form (which part of its code is in the Forms module section of the VBA project:
Forms module.PNG
) would simply be that, should someone try to (need to) access the sheet from someone else's computer/user account (even if it's those with full privileges! . . . and, again, this is assuming that they use a different user account on that computer!), it won't unhide any sheets if the user account doesn't match the user's login credentials from the Workbook's Login Form.

For example, if Brenda happens to log into the Workbook at the exact same time as you, the Workbook will unhide/make visible her sheet for her and your sheet for you. But what happens when you each save the Workbook? You're still signed in and she is too. Will it be that after she saves, your sheet will become invisible to you but hers will become visible? So VBA will need to check before every time you save who is who. That way when Brenda saves, her sheet won't all of a sudden become potentially visible when another user opens the Workbook at any time after she saves. But once she closes the Workbook from her computer, it will hide her sheet(s) and save again to secure her sheet(s) not visible to others.

And speaking of save, what about AutoSave? It's a great tool to have that one doesn't loose changes, but it's a real hazard with shared files: What IT administrators should know about AutoSave So, if you all choose to try this out, you would need to all agree (and understand) that Autosave should be turned off.
Auto Save.PNG

(And on my computer, if I try to click on that switch to turn it on, it doesn't allow me to. So if it is the same with everyone on your staff's computers, then this is not an issue. If it is enabled on at least one person's computer, it could be a problem.)

But whoever writes the VBA code will should for course run the following line of code where appropriate to turn it off periodically, just in case:
VBA Code:
Sub Turn_Off_AutoSave()
On Error Resume Next 'Just in case the user cannot toggle autosave on/off anyway (citation/confirmation needed . . . from other VBA coders!)
ThisWorkbook.AutoSaveOn = False
End Sub

I have more to say about this in this post. But for now, I will resume answering your next question.

and some complete the form using their phone instead of a computer
There are apparently video tutorials on how to send SMS messages (and even WhatsApp messages, which the guy giving the tutorial claims is free), but I (in my brief search) did not find that Excel can receive them (which doesn't make sense). So a phone sign-in (where it's like the automated appointment text messages you receive from the doctor's office that you text a letter to confirm it's you going to the appointment) may not be possible. That's all I know about VBA and phones. (Pretty much nothing.) I hope that someone who knows more about that would step in and explain if it is/not possible.

But it's highly doubtful that VBA can run on anything other than the Desktop App. So if you all want to use VBA, it cannot be accessed from phones!

And I thought about it, and for those who make the changes to their timesheet form do it by their phones instead of by computer, if at least one of you has Outlook (ideally the person who is responsible to put in the timesheet data into the spreadsheet for those who fill out the form via a phone), it would be sufficient to make it so that the form is in the form of an email. (The text of email body can be parsed so that it's just like a form.) And therefore everyone can submit the form via a simple email where they type "in" and "out" and whatever other required information (where the VBA code should be programmed to be case-insensitive and detect minor typos). I'm not sure what information they need to fill in, but if it's just to put the time they clock in/out, the date of the email will determine that, and whether they clocked in and out will be determed by if they typed in in or out.

Then the next time the Outlook recipient (the one who is in charge of taking in the forms) opens up Outlook on his or her computer, VBA code that is written in Outlook can (in theory) ask him/her if they want to load in the data into the Excel Workbook automatically. But if Outlook VBA code is to do this, understand that it should be coded so that it will close the Workbook after updating the time-sheet (that's why there would be a confirmation window before the action, because if the Workbook is being edited by that person at the time, we wouldn't want him/her to lose changes he/she made).

It should be told to close the Workbook after updating the time-sheet, because if April is at David's computer for some reason while he's signed in, and David is the one with Outlook, if David's Outlook happens to be opened, the Outlook VBA code will assume that it IS David and prompt to update the Workbook in front of April. So Outlook opening up (and keeping opened) the Workbook in April's presence is undesirable.

Does their use of phone have an impact on any of my questions?
If they are going to be able to access Excel on their phone, it doesn't necessarily mean (most likely not) that they will be able to run VBA macros on their phone. (Just like how there's Microsoft Office basically for free when viewing it from online, but you can't actually run VBA until you open the Excel Workbook on a computer.) And this sounds like another potential security risk (should they be allowed to view their sheets anywhere but from a computer) in that, should any sheets that you don't want to be viewed by at least part of the staff be made visible (and not xlSheetVeryHidden), and they are made visible by default, then Excel will not be able to hide them upon opening the file on the phone. This is not really an issue because this will force whoever is to write this VBA script to simply have it have ALL sheets are hidden from view at the start. (Even for those who have rightful access to ALL sheets.) And the number of sheets that VBA unhides upon signing/logging in will obviously depends on who is logging in.

Since I have not written VBA code previously, does this code go into a workbook or a worksheet?
Ah, so you have some background with VBA! That's wonderful!

If you mean "by workbook as in the Workbook module,
Workbook module.PNG
, where the user login can be initiated immediately as the Workbook is opened, with the Event sub:
VBA Code:
Private Sub Workbook_Open()

End Sub

Well, based on my experience, it's not a good idea (due to Microsoft security measures detecting code that immediately wants to immediately run and will most likely make users have to click some time of confirmation that they want to open the Workbook). It can be in the Worksheet code itself (triggered by when you go to view a sheet, for example). It can be that for every user (including those with full access) will start in a "welcome tab". Then they need to physically click on the "login tab" which will they activate the login form to popup. Then once they enter the proper credentials, sheet(s) will start being unhidden.

But as I mentioned previously above, the code for the design of the Login Form itself is here in the VBA project:
Forms module.PNG
. The part that triggers it to come up (and the part that carries out the action/verifies correct credentials, etc.) is in the "Login Tab".


Also, whoever is to write the VBA code, before save, a little code can be run in
VBA Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub
Which Go to the "Welcome" sheet tab, (2) hide the timesheet "owned" by that current user (besides the Login sheet), (3) save it, (4) unhides the sheets again, and (5) goes back to the sheet which was last being viewed before the save, of course.

And it is when they close the workbook, VBA code in
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub
can (1) Go to the "Welcome" sheet tab, (2) hide the timesheet "owned" by that current user (again, besides the Login sheet), (3) save it, and (4) close the Workbook.

And speaking of "hide the timesheet owned by that current user", from your first post:
I also need to allow their boss to see/update all employee timesheets (all workbook tabs), like a super user.
I also need to allow the finance person who does payroll to have this same super user access.
This would be a conflict with the above proposal. If the boss and finance person view the Workbook/make updates at a time that no other employees are potentially signing in (including them two) not editing/updating at the same time! (Although this is obviously not an option for those who want to maintain their sanity, very little VBA code is required to detect the current date and time). Because they technically "own" all of the sheets. So whenever they save and/or close the Workbook, it will hide everyone's sheet. I assume that the timesheets they are updating are for those that send a form via phone. So that's not a problem. But just for those who do not submit by phone.

So in this way, if you all have a VBA script/program/macro which does the "Outlook Email System" that I mentioned earlier (where, again, only the one responsible . . . the boss or finance person, must have Outlook), then a VBA script can be written in Excel to do the updating for you. It would collect all emails/forms with the exact same subject line (that's a way for Outlook VBA to distinguish the "form emails" from other email) and then parse them and then fill in their specific time sheet in the Workbook (where the Outlook VBA would tell the Excel VBA which sheet to edit based on the email sender).
 
Last edited:
Upvote 0
And also, I did think of a way to deal with multiple edits by different users to the same sheet at the same time (with absolutely no room for conflict). But it is of course independent of the attempt to hide/make visible sheets to the users (which I attempted to handle above). Instead of filling out time sheets, it was placing order in this thread: Creating a new system. Excel direction needed.
 
Upvote 0
Does their use of phone have an impact on any of my questions?
Yes, VBA does not work on phones.
If you want to prevent people from seeing certain sheets, then the only way to do that securely is to have different workbooks for each person.
 
Upvote 0

Forum statistics

Threads
1,224,812
Messages
6,181,104
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top