I hope all of you are staying safe and healthy. I also hope you can help with this problem.
This is a small section of allot of code that is giving me trouble. It is to track worksheet changes. I have this code in each sheet of my workbook.
It works "almost" perfectly. I say almost, because drop down lists give me 1004 run time errors. It happens as soon as you change the list, every time. With any other change, to any other cell, the code works fine.
Attached are pictures of the errors.
What is so different about a drop down list that causes the error? How do I fix it?
Thank you in advance,
Jim
This is a small section of allot of code that is giving me trouble. It is to track worksheet changes. I have this code in each sheet of my workbook.
VBA Code:
Dim V(1)
If Target.CountLarge > 1 Then
End If
If Not Intersect(Target, Range("B6:L5000, N6:N5000")) Is Nothing Then
If Target.Locked = True Then
With Application
.EnableEvents = False
V(1) = Target.Value
.Undo
V(0) = Target.Value
Target.Value = V(1)
With ThisWorkbook.Worksheets("Sheet2").UsedRange.Rows
Sheets("Sheet2").Unprotect "password"
.Item(.Count + 1).Columns("B:C").Value = V
.Item(.Count + 1).Columns("D").Value = Environ("username")
.Item(.Count + 1).Columns("E").Value = Now
.Item(.Count + 1).Columns("F").Value = Target.Row
.Item(.Count + 1).Columns("G").Value = Target.Column
End With
Application.ScreenUpdating = False
Dim outlookApp As Object
Dim myMail As Object
Set outlookApp = CreateObject("Outlook.Application")
Set myMail = outlookApp.CreateItem(0)
myMail.To = "John.Doe@acompany.com"
myMail.Subject = "Changes made"
myMail.HTMLBody = "Changes to file " & Application.ActiveWorkbook.FullName
myMail.send
.EnableEvents = True
End With
End If
End If
Sheets("Sheet2").Protect "password"
It works "almost" perfectly. I say almost, because drop down lists give me 1004 run time errors. It happens as soon as you change the list, every time. With any other change, to any other cell, the code works fine.
Attached are pictures of the errors.
What is so different about a drop down list that causes the error? How do I fix it?
Thank you in advance,
Jim