Locking Data after 1st Modification

Hello All.

I've never posted to this forum before and apologize in advance if I've missed the answer to my problem already.

Here goes: I'm redesigning a timesheet for employees where I work. In order to minimize the mistakes on them, there's a lot of data validation using lists. There are also many IF, MID, INDIRECT, SUM, and other logical tests to manipulate the numbers. When first opening, the week ending date is automatically populated to the prior Saturday based on the Julian date and where it falls in a table referenced by a LOOKUP function. Anyway, I'd like to know how to lock the data in (when the employee is done entering hours; before save, after, whatever) so that the same data is there when looked at weeks down the road. I've been staring at VBA Help and Internet "help" sites for 6 hours today and I'm going cross-eyed. In addition to the many logical functions and formulas, there is one control CHECKBOX that, when checked, will cause the "week ending" date to advance by 7...this is for those employees who decide to submit their timesheet on a Friday (before the week actually ends) or who may be going on vacation the next week. Therefore, I would need to disable the checkbox when "locking" in the data.

If needed, I can attach the sheet...but it doesn't seem that it would be necessary.

Thanks very much in advance for your help.

Anyway, I'd like to know how to lock the data in (when the employee is done entering hours; before save, after, whatever) so that the same data is there when looked at weeks down the road.

What do you mean, exactly, by "lock the data"?
Do you mean you want to protect the entry cells from being changed?
Just a thought, but after whatever constitutes the last entry, you could use the Worksheet_Change event to apply protection.

Or, use another checkbox that when checked means "ready to submit" and set the sheet protection when it is checked.

If "lock the data" means something different, please clarify and upload a sample workbook.

By "locking the data", I just mean so that the data that was originally entered by the employee(s) is there when the workbook is reopened for any reason at a later date. For example, I'd like for the "week ending" date to NOT auto update based on the fact that it's a new day, week, month, etc. when the workbook is reopened/reviewed by HR.

I've attached the workbook, which is only 62Kb.

Thanks for taking a look at it...this, with credit to you, will make me a hero in my office.

The only sure way to "lock in the data" is to convert formula cells to values and, optionally, apply sheet protection to prevent further entries.

This would have to be accomplished with VBA code.

In the attached, I've added another checkbox to use when ready to submit the timesheet.

Code used for the checkbox.

Please Login or Register  to view this content.

Note: I did not include code to apply sheet protection as you did not seem to want this. It could be added if desired.

Thank you, Palmetto, for your help. It works awesome. I'm not going to worry about VBA to protect at this point, I just wanted to prevent changes from happening automatically due to formulas when it's reopened. I had no idea about converting formulas to values.

Thanks again and I really appreciate it.

If you're satisfied with the solution, please be sure mark the thread as solved and don't forget to rate the solution.