MACRO Private Sub That No Longer Works
mima1803
-
Mima1803 -
Mima1803 -
Bonjour,
I would like to place the modification date of the cells in column A into column B.
I used the private sub change. Everything worked well until I locked my sheet.
An error message appeared, so I added unprotect and protect at the end. I no longer have an error message, but the macro no longer works and sometimes it only works for one modification (for example, when I modify A4, the date is correctly placed in B4, but nothing for other modifications).
I cannot see where the error comes from, I am just starting.
Thank you for your help.
Here is the code used:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = Range("A:A").Column Then
Application.ScreenUpdating = True
For Each c In Intersect(Target, Range("A:A"))
Unprotect ("mdp")
c.Offset(, 1) = Format(Date, "dd/mm/yy")
Next
Application.EnableEvents = False
Protect ("mdp")
End If
End Sub
I would like to place the modification date of the cells in column A into column B.
I used the private sub change. Everything worked well until I locked my sheet.
An error message appeared, so I added unprotect and protect at the end. I no longer have an error message, but the macro no longer works and sometimes it only works for one modification (for example, when I modify A4, the date is correctly placed in B4, but nothing for other modifications).
I cannot see where the error comes from, I am just starting.
Thank you for your help.
Here is the code used:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = Range("A:A").Column Then
Application.ScreenUpdating = True
For Each c In Intersect(Target, Range("A:A"))
Unprotect ("mdp")
c.Offset(, 1) = Format(Date, "dd/mm/yy")
Next
Application.EnableEvents = False
Protect ("mdp")
End If
End Sub
1 answer
-
Hello,
1 - Events should not be disabled at the end of the procedure but before the loop and especially reactivated after the loop.
2 - You only need to unprotect once before the loop.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Application.ScreenUpdating = True Me.Unprotect ("mdp") Application.EnableEvents = False For Each c In Intersect(Target, Range("A:A")) c.Offset(, 1) = Format(Date, "dd/mm/yy") Next Application.EnableEvents = True Me.Protect ("mdp") End If End Sub
Best regards
Patrice
No one can hold all the knowledge, that's why we share it.
