Use of NOW() function - followed by locking the cell

[Roger on Excel]

I have a table in Excel with a date/time column and a comment column.

I would like to select a cell in the date/time column and then push a button
which enters the current date and time into that cell. I would then like to
make it so that the cell is locked so that the the user cannot change the
cell again without entering a password. The date/time would then be
preserved next to when the comment is entered.

Ideally once the date/time is entered, upon selecting the same cell again,
the cell would call up the password dialog box to enter a password to unlock
the cell so that a new date/time could be entered if desired.

The code would be able to determine if the cell is locked for editing when
it is selected.

Sounds complicated...

Can anyone help?

Best regards, Roger

 

[Per Jessen]

Hi Roger

Look at the code below, just remember that you have to unlock all cells
which user should be allowed to cange.

Sub TimeStamp()
Dim TimeCol As String
Dim shtpWord As String
Dim AllowChangepWord As String
Dim pWord As String

TimeCol = "A" ' Change to suit
shtpWord = "JustMe"
AllowChangepWord = "AllowChange"
Set isect = Intersect(ActiveCell, Columns(TimeCol))
If Not isect Is Nothing Then
If ActiveCell.Locked = False Then
ActiveSheet.Unprotect Password:=shtpWord
ActiveCell = Now()
ActiveCell.Locked = True
ActiveSheet.Protect Password:=shtpWord
Else
pWord = InputBox("Enter password to change date/Time in cell")
If pWord = AllowChangepWord Then
ActiveSheet.Unprotect Password:=shtpWord
ActiveCell = Now()
ActiveCell.Locked = True
ActiveSheet.Protect Password:=shtpWord
End If
End If
End If
End Sub

Regards,
Per

 

[Roger on Excel]

Dear Per,

Thanks for the code. Where should I paste it - i tried to paste it in a
module without success.

Regards,

Roger

 

[Per Jessen]

Dear Roger,

Paste it in a ordinary module, the insert a Button from the Control Toolbox
menu (ActiveX control if you are using excel 2007).

Then right click the button and select 'View Code'. Between 'Private Sub...'
and 'End Sub' insert 'Call TimeStamp'.

Return to the worksheet and exit Design mode.

Hopes this helps.

Per

 

[Roger on Excel]

Thanks Per,

All the best, Roger

Dear Roger,

Paste it in a ordinary module, the insert a Button from the Control Toolbox
menu (ActiveX control if you are using excel 2007).

Then right click the button and select 'View Code'. Between 'Private Sub...'
and 'End Sub' insert 'Call TimeStamp'.

Return to the worksheet and exit Design mode.

Hopes this helps.

Per