Excel 2007 VBA Auditing

Question

New to VBA thanx in advance.. is it possible to automatically undo changes to a row range depending on another corresponding cell value?

for example : cells A2,B2,C2 are the cells that user inter data within Cell G2 the cell which the auditor Approve the interned data on the raw by typing "yes"

so if G2 value is "yes" any change to the values in A2,B2,C2 is canceled "undo" and return to its original data, if G2 is not "yes" then user can alter the value in cells A2,B2,C2 as he wants and that goes for the other cells in sequnce A3,B3,C3 versus G3, A4,B4,C4 versus G4 and so on...

Code copied from comment:

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo er1
  Application.EnableEvents = False
  If Not Intersect(Target, Range("A1:d10")) Is Nothing Then
    If Target.Range("g1:g10").Value = "Yes" Then
      Application.Undo
    Else
    End If
  End If
err2:
  Application.EnableEvents = True
  Exit Sub
er1:
  MsgBox Err.Description
  Resume err2
End Sub

enter image description here

I am bit suspicious that you can get the previous values of cell. — Garry, Jun 25 '15 at 20:14
`Application.Undo` exists. [`Worksheet_Change` exists](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640). I suspect the two could be combined... If you give it a shot, let us know what does/does not work with a specific question. — Byron Wall, Jun 25 '15 at 20:58
maybe I miss-spoken man Its not The previous values I want .. after the auditor Type Yes and save Any Change By user to the Cells In the Same Row of "Yes" will Undo Immediately.. like the action you mentioned "Application.Undo on Worksheet_Change" but related to word "Yes" in the same Raw G: — Abo Sarah, Jun 25 '15 at 21:12
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo er1 Application.EnableEvents = False If Not Intersect(Target, Range("A1:d10")) Is Nothing Then If Target.Range("g1:g10").Value = "Yes" Then Application.Undo Else End If End If err2: Application.EnableEvents = True Exit Sub er1: MsgBox Err.Description Resume err2 End Sub — Abo Sarah, Jun 25 '15 at 21:32
i found this work around but still not quite right https://www.dropbox.com/s/g9squuofwhp4gif/New%20Microsoft%20Office%20Excel2007.rar?dl=0 when yes is presented sometimes it Undo the changes maid to the target raw other times its not or its affecting other raws not the same one which has the "yes" ,also if i deleted "yes" its it may or may not permit to alter the target raw — Abo Sarah, Jun 26 '15 at 01:43

score 1 · Answer 1 · answered Jun 26 '15 at 11:01

The problem is in the code where you are trying to check if column G is "Yes":

If Target.Range("g1:g10").Value = "Yes" Then
  Application.Undo
Else
End If

The Target variable is already a range and will not be column G if the user has entered something in columns A-D. Instead, you must work out the row number which has just been changed and then look at column G for that row. Replace your block of code with this:

Dim rowNumber As Long
' This **assumes** only 1 cell has been changed
rowNumber = Target.Row

If Target.Parent.Cells(RowIndex:=rowNumber, ColumnIndex:="G").Value = "Yes" Then
  Application.Undo
Else
End If

Warning This code assumes that only one cell has been changed. If you think that Target might be more than one cell, you should loop through the cells checking them all:

Dim rowNumber As Long
Dim cell As Range

For Each cell In Target.Cells
    rowNumber = cell.Row

    If Target.Parent.Cells(RowIndex:=rowNumber, ColumnIndex:="G").Value = "Yes" Then
      Application.Undo
      Exit For
    Else
    End If
Next cell

Excel 2007 VBA Auditing

1 Answers1