Secure Timestamp - VBA

Question

I want to create a secure timestamp on my Excel sheet. The VBA I am using will automatically add the current users user name, the time, and the date when a user puts information into column A. So if the users puts something into cell A1 then B1 automatically gets filled with their username and C1 gets filled with the time and date. The only problem is that this method isn’t secure because the user could alter the information after it is automatically populated. I would like to add code to this VBA so it will lock all three cells after the information is populated.

I was planning on using the Protect Sheet feature and only allowing users to “Select unlocked cells” So if the VBA could auto lock the cells then the users would not be able to alter the information.

Moreover I have used Me.Unprotect before changing cells and Me.Protect after that still it is not working

Any help would be much appreciated!

Hi, I need to lock each cell after data entry in given range. Like once I enter Y or N in it. cant edit it again. below is my code — 3689

Gary's Student Gary's Student · Accepted Answer · 2015-02-11T13:08:27

Assume that we start with all the cells on the worksheet unlocked and the sheet password-protected with the password:

6LgSdHjc2uOssv0e1LDI

The following Event Macro will:

unprotect the workbook
detect entries in column A
place the username in column B and the date/timestamp in column C
lock the entries in columns A,B,C
re-protect the worksheet.

This goes in the Worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, MyPass As String, sh As Worksheet
    Dim unit As Range
    Set A = Range("A:A")
    MyPass = "6LgSdHjc2uOssv0e1LDI"
    Set sh = ActiveSheet
    If Intersect(Target, A) Is Nothing Then Exit Sub

    Set unit = Union(Target, Target.Offset(0, 1), Target.Offset(0, 2))

    Application.EnableEvents = False
    sh.Unprotect (MyPass)
    unit.Locked = False
        Target.Offset(0, 1) = Environ("Username")
        Target.Offset(0, 2) = Now()
    unit.Locked = True
    sh.Protect (MyPass)
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

right-click the tab name near the bottom of the Excel window
select View Code - this brings up a VBE window
paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

bring up the VBE windows as above
clear the code out
close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Secure Timestamp - VBA

1 Answers