Debug error

[hopr37]

My spreadsheet takes totals and subtracts totals.

I have about 6 columns and rows that are filled with data.
If I try to insert a new row I get a debug error.
The debug highlights "Application.Undo"
Not sure why
any suggestions?



Private Sub Worksheet_Change(ByVal Target As Range)
'copies the value of colum c into new cell as "old value"
Dim nextRow As Integer
Dim oldValues As Variant
Dim newValues As Variant
Dim oldRev, newRev, diffRev As Double
Dim NumRows, NumCols As Integer
Dim lRow, lCol As Integer
If Not Intersect(Target, Range("C:C")) Is Nothing Then
NumRows = Target.Cells.Rows.Count
NumCols = Target.Cells.Columns.Count
newValues = Target.Value2
Application.EnableEvents = False
Application.Undo
oldValues = Target.Value2
Target.Offset(0, 1).Value = Target.Value
Target = newValues
Application.EnableEvents = True
End If

 

[rlv01]

Application.Undo cancels the last user-interface action and if used in a macro, it must be the first line in a macro.

 

[hopr37]

This did not work. Still got the same error.
This isn't a macro per se. It's just some code inside my worksheet

 

[Joe4]

This isn't a macro per se. It's just some code inside my worksheet

The terms "macro" and "VBA" are synonymous and often used interchangeably.
What you have is, indeed, VBA code, and subject to what rlv01 said.
See: https://www.mrexcel.com/forum/excel-questions/919790-undo-actions-performed-addin.html

 

[hopr37]

Be that it may, the solution did not correct the error.

 

[Joe4]

Can you explain exactly what it is that you are trying to do?
What exactly are you trying to undo?.
Are you just trying to undo the change that triggered the code to run?

 

[Joe4]

Note that in moving it up to the top, you will still need to surround it by the Application.EnableEvents lines so that the Undo command doesn't call itself, and undo the undo.

Here is a simple example to undo any changes made to column B only:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then
[COLOR=#ff0000]        Application.EnableEvents = False[/COLOR]
[COLOR=#ff0000]        Application.Undo[/COLOR]
[COLOR=#ff0000]        Application.EnableEvents = True[/COLOR]
    End If
    
End Sub

Make sure when you move it up, you include all three lines in red, in that order.

 

[hopr37]

Hey Joe. Thanks for responding. Below is the code with the error in it
The problem is that when I try to insert a row or say, copy and drag text down an entire column I get a debug error highlighted at the "undo" stage.

Basically what this code does ( I did not write this) is that when I input a number in cell ( as an example) c it copies that amount to cell D

Here is a link to the spreadsheet.
https://drive.google.com/file/d/1z81DJFFbChhKvL2Skue0IeCGeY-dclEy/view?usp=sharing

 

[Joe4]

I do not have the ability to download anything at my current location or even access those sites (corporate security policy!).
So I cannot see your sheet or code.

Basically what this code does ( I did not write this) is that when I input a number in cell ( as an example) c it copies that amount to cell D

I am not sure what the purpose of the "Application.Undo" line is in this code, if that is really all it is supposed to be doing.
If it is, you may be able to remove that line altogether.
There also seem to be no point to the "NumRows" and "NumCols" variables, as those calculations do not appear to be used anywhere in the code.

 

[hopr37]

when I try to insert a new row I get a debug error at the undo portion.


Private Sub Worksheet_Change(ByVal Target As Range)
'copies the value of colum c into new cell as "old value"
Dim nextRow As Integer
Dim oldValues As Variant
Dim newValues As Variant
Dim oldRev, newRev, diffRev As Double
Dim NumRows, NumCols As Integer
Dim lRow, lCol As Integer
If Not Intersect(Target, Range("C:C")) Is Nothing Then
NumRows = Target.Cells.Rows.Count
NumCols = Target.Cells.Columns.Count
newValues = Target.Value2
Application.EnableEvents = False
'Application.Undo
oldValues = Target.Value2
Target.Offset(0, 1).Value = Target.Value
Target = newValues
Application.EnableEvents = True
End If





If Not Intersect(Target, Range("C4", Range("C" & Rows.Count).End(xlUp))) Is Nothing Then
Application.EnableEvents = False
If Target > 0 Then
Target.Offset(, 2).Value2 = Target.Offset(, 2).Value2 - Target.Value2
If Target.Address = Range("C4").Address Then
End If
End If
End If
Application.EnableEvents = True