First of all, thanks for taking the time to read and consider this question!
I am very new to using VBA in Excel, and am feeling my way around the language. I have come across a situation in which I am not sure how to approach the problem for a solution.
Scenario part 1:
I have timesheets where staff can sign in for their hours, etc. Staff members by row and days/dates of the week by column. It is not just a simple table on the worksheet, as there are other elements in the worksheet that make a lot of calculations and display relevant information for the staff member in reference to the shifts that they have signed in for; however, the section that they have editing access to is essentially an n x 14 table (start time and finish time for 7 days/week per sheet)
Scenario part 2:
While testing out this system of signing in for shifts, I set it up quite simply: Each staff member picked and set a password for their "row", which was configured to allow them access via "Allow Users to Edit Ranges".
Scenario part 3:
I now am trying to write code such that each staff member still only has access to their row with their existing password, but can only put in their times IF the date at the top of the "column" matches the current date on the system. (Aside: they do not have access to changing the current date/time on the system without a bit of effort)
Appraoches I have considered:
1. Am I able to combine the functionalities of using VBA as well as the pre-existing function to allow users to edit ranges?
Method A: Keep the current user range set-up, protect the sheet, and use VBA to unlock the column that corresponds to the current date.
[Tested: Did not work. It completely unlocked the column that corresponded to the current date and did not prompt for a password. As expected.]
Method B: Keep the current user range set-up, unprotect the sheet, and use VBA to lock columns that do not correspond to the current date.
[Same result as above]
2. Maybe try and find a way to stop user from SELECTING cells, with a method that is not tied to the "Lock" function? That way, I can keep the "Allow Users to Edit Range" setting to password prompt to unlock a cell, while not allowing users to select any columns that don't correspond to the date.
3. The final method I can think of is programming each staff member's password into VBA itself, i.e. use VBA to identify the allowable columns for editing, and then prompt the user for a password specific to each row. This is obviously a more time-consuming avenue for someone who is only just starting out in VBA, so am trying to see if I just missed a simpler solution before diving head-first into writing the code for this!
3a. I also just realised that I will need to access everyone's passwords that have been set into Excel for their ranges. Is there a place where these are stored in the file that I can access, or is it a security breach for that to be the case? Also, this makes me wonder that if I store each person's password as a string in VBA, I will need to bury the list of passwords in the code somewhere so that someone doesn't just come along, decide to look at the code and have access to everyone's passwords! Any suggestions on this aspect of the problem?
Finally - thank you for reading all the way through. I know that it's a long post for a (probably) simple problem, but I wanted to be sure to paint an accurate picture of the challenge. Your time and any help is much appreciated!
I am very new to using VBA in Excel, and am feeling my way around the language. I have come across a situation in which I am not sure how to approach the problem for a solution.
Scenario part 1:
I have timesheets where staff can sign in for their hours, etc. Staff members by row and days/dates of the week by column. It is not just a simple table on the worksheet, as there are other elements in the worksheet that make a lot of calculations and display relevant information for the staff member in reference to the shifts that they have signed in for; however, the section that they have editing access to is essentially an n x 14 table (start time and finish time for 7 days/week per sheet)
Scenario part 2:
While testing out this system of signing in for shifts, I set it up quite simply: Each staff member picked and set a password for their "row", which was configured to allow them access via "Allow Users to Edit Ranges".
Scenario part 3:
I now am trying to write code such that each staff member still only has access to their row with their existing password, but can only put in their times IF the date at the top of the "column" matches the current date on the system. (Aside: they do not have access to changing the current date/time on the system without a bit of effort)
Appraoches I have considered:
1. Am I able to combine the functionalities of using VBA as well as the pre-existing function to allow users to edit ranges?
Method A: Keep the current user range set-up, protect the sheet, and use VBA to unlock the column that corresponds to the current date.
[Tested: Did not work. It completely unlocked the column that corresponded to the current date and did not prompt for a password. As expected.]
Method B: Keep the current user range set-up, unprotect the sheet, and use VBA to lock columns that do not correspond to the current date.
[Same result as above]
2. Maybe try and find a way to stop user from SELECTING cells, with a method that is not tied to the "Lock" function? That way, I can keep the "Allow Users to Edit Range" setting to password prompt to unlock a cell, while not allowing users to select any columns that don't correspond to the date.
3. The final method I can think of is programming each staff member's password into VBA itself, i.e. use VBA to identify the allowable columns for editing, and then prompt the user for a password specific to each row. This is obviously a more time-consuming avenue for someone who is only just starting out in VBA, so am trying to see if I just missed a simpler solution before diving head-first into writing the code for this!
3a. I also just realised that I will need to access everyone's passwords that have been set into Excel for their ranges. Is there a place where these are stored in the file that I can access, or is it a security breach for that to be the case? Also, this makes me wonder that if I store each person's password as a string in VBA, I will need to bury the list of passwords in the code somewhere so that someone doesn't just come along, decide to look at the code and have access to everyone's passwords! Any suggestions on this aspect of the problem?
Finally - thank you for reading all the way through. I know that it's a long post for a (probably) simple problem, but I wanted to be sure to paint an accurate picture of the challenge. Your time and any help is much appreciated!
