CODEWITHSUNDEEP
vbaexcel
Abo Sarah
Abo Sarah

Reputation: 11

Excel 2007 VBA Auditing

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

Upvotes: 1

Views: 244

Answers (1)

ChipsLetten
ChipsLetten

Reputation: 2953

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

Upvotes: 1

Related Questions