Want to lock range of cells after time stamp entered.

[surajit_raj]

Hi,
Please help me to write a code for the below mentioned scenario.

I want to put a time stamp using =now command in VB in a range of cells (example B2:C25) and cells will be locked once the data is entered with a password and apart from B2:C25 all the cells are editable and unlocked.

Need an urgent help. Please help.

 

[Drrellik]

Locking a range of cell is actually changing the cells properties to protected vs unprotected and then locking the entire sheet. select and right click on cells in a range goto properties and protect.

then look for VBA to toggle on/off sheet protect with password

 

[mumps]

Will all the cells in the range have a time stamp or only some of the cells? Do you want each cell locked as you enter the time stamp in each one?

 

[surajit_raj]

Yes! I want each cell to be locked as i enter the time stamp by a button in each cell.

 

[mumps]

Begin by unlocking all the cells in the sheet. Then protect the sheet with a password of your choice. The macro assigned to the buttons should look something like this. Change myPasswordHere (in red) to your password. Change the range (in blue) accordingly.

Sub InsertTimeStamp()
    ActiveSheet.Unprotect Password:="[COLOR="#FF0000"]myPasswordHere[/COLOR]"
    Range("[COLOR="#0000FF"]C20[/COLOR]") = Now()
    ActiveSheet.Protect Password:="[COLOR="#FF0000"]myPasswordHere[/COLOR]"
End Sub

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2:C25")) Is Nothing Then Exit Sub
    Target.Locked = True
End Sub

Click a button to enter the time stamp.