Target.Value <> OriginalValue

[TheRobRush]

In my code below I was expecting the data to be saved to the storage file only when a item in column b was changed a different value.

However, even when you change (for example) Tickets to Tickets it adds these changes to the log file.

What am I missing?

Private Sub Worksheet_Change(ByVal Target As Range)


' so far takes updates and stores them on cell a1 of sheet2 ONLY if column 2 (location) is edited
' stores all data as a csv file format scheduled,name,location,time,date,NEXTPERSON ad infinitum


Application.ScreenUpdating = False
Application.EnableEvents = False
'==============================================================

Dim assname
Dim asscombo
asssch = "Tickets"
assr = Target.Row
assc = Target.Column
If Target.Column = 2 Then
' assloc gives you the position changed to
' assname gives you the name of ermployee edited
' *******************REQUIRES CHANGE IN EACH SHEET USED FOR SHEET NAME
' try and change to active sheet

assloc = Worksheets("Sheet1").Cells(assr, 2).Value
assname = Worksheets("Sheet1").Cells(assr, 1).Value


' checks if change was to previous value, if so skips
Dim a, b
a = originalvalue
b = Target.Value
       If Target.Value <> originalvalue Then
'===============trying to save directly all changes
    
    Dim xl0 As New Excel.Application
    Dim xlw As New Excel.Workbook
    Dim j, v
    Set xlw = xl0.Workbooks.Open("C:\Users\604034107\Desktop\BOARD TEST\AIO BOARD\TESTASSIGNMENTSTORAGE.xlsm")
    ' xl0.Worksheets.Add
    
     j = xl0.Range("A1048576").End(xlUp).Row
     v = j + 1
     xl0.ActiveSheet.Cells(v, 1) = "Tickets"
     xl0.ActiveSheet.Cells(v, 2) = assname
     xl0.ActiveSheet.Cells(v, 3) = assloc
     xl0.ActiveSheet.Cells(v, 4) = Date
     xl0.ActiveSheet.Cells(v, 5) = Time
    
    xlw.Save
    xlw.Close
    Set xl0 = Nothing
    Set xlw = Nothing
    
'=================end experiment

        End If

'end if for first if statement checking columnn
End If

Application.EnableEvents = True
Application.ScreenUpdating = True


'==================================================================

End Sub

 

[MARK858]

I haven't been through all your code but maybe...

a = asssch
b = Target.Value
       If a <> b Then

 

[TheRobRush]

Hey Mark, thanks for looking. That unfortunately wouldn't work in this situation because asssch is just a static value referencing the sheet they came from. assloc is the actual value that will be changing (and it will have about 20 or so different things that may be put there, not just tickets)

using same idea
Dim a, b
a = originalvalue
b = Target.Value

and assloc<> originalvalue
or assloc <>a

neither stop it from backing up the changes if they are the same

losing my mind lmao

 

[TheRobRush]

starting to think this may not be possible.

tried the following code on a new sheet

when you select a cell it DOES store its old value, but by time you change that cell oval gets its stored value wiped and replaced by the cell value below it (due to selection change being activated again from pressing enter after changing cell)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False
oldvalue = oval
If Target.Value <> oldvalue Then
    
    Range("A2").Value = "CHANGE"
    
    Range("A3").Value = Target.Value
    
    Range("A4").Value = oldvalue
    
    Else
    Range("A2").Value = "NO CHANGE"
    
    
    
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub





Public Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim oval
oval = Target.Value
Range("A1").Value = oval

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[TheRobRush]

changing selection change to store the value on a separate sheet will work, or further right on current sheet either way, just have to store it in same row as changed data. see code I changed below for the selection change portion.
this at least makes I functional now

Public Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False
testrow = Target.Row
testcolumn = Target.Column

Dim oval
oval = Target.Value
Worksheets("Sheet5").Cells(testrow, 1).Value = oval
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[TheRobRush]

JUST in case anyone else needs to do something similar to this one day, below is the full working code that would be placed into the worksheet event area.

THIS backups cell values from column 2 in a separate sheet every time a new cell in column 2 is selected.
If that cell is then typed in it checks if the new cell value is different, if it is not nothing happens.
If it is the position change is saved to a second wb, along with accompanying data.

the on error goto done skips to end sub if multiple cells are deleted together. doesn't affect me but if you use this and need to capture that event remove the on error and add a FOR

Private Sub Worksheet_Change(ByVal Target As Range)

'===================WORKING STORE POSITION CHANGE FOR COLUMN 2
' Stores the original scheduled venue, Name, Position Change, Date & Time in a backup workbook

Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo done
'==============================================================

Dim assname
asssch = "Tickets"
assr = Target.Row
assc = Target.Column
If Target.Column = 2 Then
' assloc gives you the position changed to
' assname gives you the name of ermployee edited
' *******************REQUIRES CHANGE IN EACH SHEET USED FOR SHEET NAME i.e. "Sheet3" below
' change to active sheet in next update

assloc = Worksheets("Sheet3").Cells(assr, 2).Value
assname = Worksheets("Sheet3").Cells(assr, 1).Value

' checks if cell actually changed value, if not skips
If Target.Value <> Worksheets("Sheet6").Cells(assr, 1).Value Then
'===============trying to save directly all changes
    
    Dim xl0 As New Excel.Application
    Dim xlw As New Excel.Workbook
    Dim j, v
    Set xlw = xl0.Workbooks.Open("C:\Users\604034107\Desktop\BOARD TEST\AIO BOARD\TESTASSIGNMENTSTORAGE.xlsm")
   
    
     j = xl0.Range("A1048576").End(xlUp).Row
     v = j + 1
     xl0.ActiveSheet.Cells(v, 1) = "Tickets"
     xl0.ActiveSheet.Cells(v, 2) = assname
     xl0.ActiveSheet.Cells(v, 3) = assloc
     xl0.ActiveSheet.Cells(v, 4) = Date
     xl0.ActiveSheet.Cells(v, 5) = Time
    
    xlw.Save
    xlw.Close
    Set xl0 = Nothing
    Set xlw = Nothing
    
'=================end experiment

        End If

'end if for first if statement checking columnn
End If
done:
Application.EnableEvents = True
Application.ScreenUpdating = True


'==================================================================

End Sub






Public Sub Worksheet_SelectionChange(ByVal Target As Range)

'===================WORKING STORE ORIGINAL VALUE FOR COLUMN 2

Application.ScreenUpdating = False
Application.EnableEvents = False
testrow = Target.Row
testColumn = Target.Column
Dim original
original = Target.Value
If testColumn = 2 Then
Worksheets("Sheet6").Cells(testrow, 1).Value = original
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub