Automatically Time Stamp Column Entries On Protected Worksheet

    Hello,


    By having a column (column A) unlocked on a protected sheet the
    user can type a value (1,2,3 etc) and the corresponding cell on
    column B should give the static time. Although i can do it on an
    unprotected sheet VB returns an error when it is protected which
    is natural since the column B which the script updates with the
    static time is locked. Is there a possiblity to unlock the cell from
    column B just after the user entered a valid data in the cell of
    column A, write automatically the static time (.value = Now /
    .entireColumn.Autofit) and then re-lock the cell of column B so
    the user cannot change the value of the date and time?


    Thanks in advance,


    Nik

    Re: Vb That Gives Static Time While Updating A Cell While The Sheet Is Locked


    Welcome to Ozgrid, use the code below in the worksheet module of the sheet you are using

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
If Target <> vbNullString And Target.Offset(0, 1) = vbNullString Then
Me.Unprotect Password:="password" ' password would be the one you set for the sheet
Target.Offset(0, 1).Value = Time
Me.Protect Password:="password"
End If
End Sub

    make sure that the cells in column A are unlocked.

    Re: Vb That Gives Static Time While Updating A Cell While The Sheet Is Locked


    Perfect, thanks a lot Simon for the fast reply


    Regards,


    Nik

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login