Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post Lock the cell after the date is added.

    Hi Experts
    I need your help to deal with this situation
    I have to column A and B. I add the date in column A and with the help of below code the time the date is added gets captured in column B.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        Dim rCell As Range
        Dim rChange As Range
        
        On Error GoTo ErrHandler
        Set rChange = Intersect(Target, Range("A:A"))
        If Not rChange Is Nothing Then
            Application.EnableEvents = False
            For Each rCell In rChange
                If rCell > "" Then
                    With rCell.Offset(0, 1)
                        .Value = Now
                        .NumberFormat = "hh:mm:ss"
                    End With
                Else
                    rCell.Offset(0, 1).Clear
                End If
            Next
        End If
    ExitHandler:
        Set rCell = Nothing
        Set rChange = Nothing
        Application.EnableEvents = True
        Exit Sub
    ErrHandler:
        MsgBox Err.Description
        Resume ExitHandler
    End Sub
    What I am looking for it once the date is added in the Sheet (column A) it should get lock. No one should be able to modify the date in column A and time in column B.
    Regards,
    JD
    Last edited by RetiredGeek; 2015-05-11 at 08:48. Reason: Fixed Code Tags

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Jaggi

    see attached example file.

    Cells in the data-entry range are initially unlocked.
    When an entry is made, the cell is then locked.
    A password could be assigned to the sheet if required.
    Modify the vba code as required.

    (You would also need to check that the entry is a date)

    Code:
    Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range
    
    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("A:A"))
    If Not rChange Is Nothing Then
    
    ActiveSheet.Unprotect
    
    Application.EnableEvents = False
    For Each rCell In rChange
    If rCell > "" Then
    rCell.Locked = True             'lock cell
    With rCell.Offset(0, 1)
    .Value = Now
    .NumberFormat = "hh:mm:ss"
    End With
    Else
    rCell.Offset(0, 1).Clear
    End If
    Next
    End If
    ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    
    ActiveSheet.Protect
    
    Exit Sub
    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub
    zeddy
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post

    Hi Zeddy

    Please find the macro attached on which I am working now.

    The dates will be added on Column A by users and the date will be captured in column B. Once a user adds the date in any cell on column A, the respective date will reflect in column B. Is it possible that the user will not able to change the date and time of the cells which they already used.

    I am not able to incorporate the changes in this sheet. Please help me with it.

    Regards,
    JD
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Jaggi

    You just needed to replace your sheet code with the code I posted.
    I have done this in the attached file.

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    Jaggi (2015-05-11)

  6. #5
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Thanks Zeddy

    Regards,
    JD

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •