CODEWITHSUNDEEP
vbaexcelselectionchanged
imageicb
imageicb

Reputation: 21

Changing old values and new values

I have this code that I am using to call old and new values. However I am now having a issue where if the user simply highlights a and hits delete it will run and note that change, however it will run a number of more instances of that same request for some reason. Is there a way so that it will only run the one time? Thanks

Public OldValues As New Collection

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Pagination").Range("J11") <> "Yes" Then Exit Sub

    'Copy old values
    Set OldValues = Nothing
    Dim c As Range
    For Each c In Target
        OldValues.Add c.Value, c.Address
    Next c
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Sheets("Pagination").Range("J11") <> "Yes" Then Exit Sub

    On Local Error Resume Next  ' To avoid error if the old value of the cell address you're looking for has not been copied
    Dim c As Range
    For Each c In Target
        Sheets("corrections").Cells(Rows.Count, "A").End(xlUp)(2).Value = Now & " Sheet " & ActiveSheet.Name & " Cell " & Target.Address(0, 0) & " has a new value of " & c.Value & "; old value was " & OldValues(c.Address)
    Next c
    'Copy old values (in case you made any changes in previous lines of code)
    Set OldValues = Nothing
    For Each c In Target
        OldValues.Add c.Value, c.Address
    Next c
End Sub

Upvotes: 1

Views: 244

Answers (1)

mattboy
mattboy

Reputation: 2910

Add Application.EnableEvents = False to the beginning of the Worksheet_Change event, and then back to Application.EnableEvents = True at the end of it.

EDIT: I can't recreate your problem myself, and since the macro isn't making changes on the same sheet, the above solution shouldn't make a difference. Do you have any other Worksheet_Change events on the "corrections" sheet where you need to disable the events like this perhaps?

Application.EnableEvents should also be added to other functions which may afftect any cell in sheet which has the worksheet_change event.

For example even if the value of Cell A1 changes the worksheet_change event will be called.

Upvotes: 1

Related Questions