Issue in record date and time automatically when cell changes

[Gaurangg]

Hi All,

I am not much savvy with the VBA Code building. I have googled below code and modified as my need. But when I have protected the worksheet(Unlocked the target cells) and reopen the excel, I got an error that due to security issue macro is disabled. And then it didn't capture the time.

I have made modification to capture time in two different columns when data is selected in column A and U. When I run this code in fresh column then it works and sudden after got above error & it stops. Can anyone help me with necessary changes in below code?

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng, WorkRng1 As Range
Dim Rng, Rng1 As Range
Dim xOffsetColumn, xOffsetColumn1 As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), Target)
xOffsetColumn = 29
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
Set WorkRng1 = Intersect(Application.ActiveSheet.Range("U:U"), Target)
xOffsetColumn1 = 9
If Not WorkRng1 Is Nothing Then
    Application.EnableEvents = False
    For Each Rng1 In WorkRng1
        If Not VBA.IsEmpty(Rng1.Value) Then
            Rng1.Offset(0, xOffsetColumn1).Value = Now
            Rng1.Offset(0, xOffsetColumn1).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng1.Offset(0, xOffsetColumn1).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

 

[mole999]

to have your macros always work you need to change the trust centre settings in options to enable them

 

[Gaurangg]

After getting the error, I have checked and found that the settings are already enabled. I have also checked by adding this code to new excel and save it with settings. Then I got the error related to antivirus and then it goes disabled.