Forum Discussion

BiG123's avatar
BiG123
Copper Contributor
Aug 12, 2024

Makro excel lock cell

Hi, I would like to add an option to excel to display the date and time after adding data to the cell next to it. Everything works fine through macros. The only problem is that when I protect sheet access to the date and time cells, macros do not work.

Is it possible to close editing date and time cells and at the same time make macros in them work?

 

 

 

 

 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


If Target.Column = 4 Then
ThisRow = Target.Row
Range("e" & ThisRow).Value = Date
Range("f" & ThisRow).Value = Format(Time, "Long Time")
ElseIf Target.Column = 7 Then
ThisRow = Target.Row
Range("h" & ThisRow).Value = Date
Range("i" & ThisRow).Value = Format(Time, "Long Time")

End If
End Sub

excel
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Aug 12, 2024

    BiG123 

    Here’s how you can modify your macro to unlock the cells, update them, and then re-lock them:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    ' Disable worksheet protection to allow updates
    Me.Unprotect Password:="YourPassword"

    If Target.Column = 4 Then
        ThisRow = Target.Row
        Range("e" & ThisRow).Value = Date
        Range("f" & ThisRow).Value = Format(Time, "Long Time")
    ElseIf Target.Column = 7 Then
        ThisRow = Target.Row
        Range("h" & ThisRow).Value = Date
        Range("i" & ThisRow).Value = Format(Time, "Long Time")
    End If

    ' Re-enable protection after the updates
    Me.Protect Password:="YourPassword"

End Sub

    The code were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources