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.
Private Sub CheckBox2_Click()
Dim msg As String, ans As Variant
msg = "You have checked the box indicating the time sheet is ready to be submitted"
msg = msg & vbLf & vbLf & "PLEASE NOTE"
msg = msg & vbLf & vbLf & vbLg & "No further changes are allowed once you have clicked OK"
msg = msg & vbLf & vbLf & vbLf & "Click OK to continue or Cancel to Quit this action"
Application.ScreenUpdating = False
ans = MsgBox(msg, vbExclamation + vbOKCancel)
Select Case ans
Case vbCancel
Exit Sub
Case vbOK
With ActiveSheet
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Select
End With
End Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.Goto reference:=Sheet1.Range("A7")
MsgBox ("All formula cells have been coverted to values")
End Sub
Note: I did not include code to apply sheet protection as you did not seem to want this. It could be added if desired.
Bookmarks