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:
) 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.
(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,
, 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:
. 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).
