Hello to all

When the user CHANGES an entry in range A5:F64, I'd like a msgbox to ask for
confirmation, or else undo the change.

So far I can tell if the change is in the desired range (see below), but I
have no clue as to how to undo the changes if not confirmed by the user.
Any ideas would be appreciated.

Thanks, Brian


Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim ChangedRange As Range

Set WatchRange = Range("A5:F64")
Set ChangedRange = Intersect(Target, WatchRange)

If ChangedRange Is Nothing Then
MsgBox "You changed a cell NOT in the WatchRange", vbOKOnly
Else
MsgBox "You changed a cell in the WatchRange", vbOKOnly
End If

End Sub

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim ChangedRange As Range
Dim Resp As Long

Set WatchRange = Range("A5:F64")
Set ChangedRange = Intersect(Target, WatchRange)

If ChangedRange Is Nothing Then
MsgBox "You changed a cell NOT in the WatchRange", vbOKOnly
Else
Resp = MsgBox(Prompt:="You changed a cell in the WatchRange" & vbLf _
& "Are you sure?", Buttons:=vbYesNo)

If Resp = vbYes Then
'do nothing
Else
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If

End If

End Sub

Bri wrote:

Hello to all

When the user CHANGES an entry in range A5:F64, I'd like a msgbox to ask for
confirmation, or else undo the change.

So far I can tell if the change is in the desired range (see below), but I
have no clue as to how to undo the changes if not confirmed by the user.
Any ideas would be appreciated.

Thanks, Brian

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim ChangedRange As Range

Set WatchRange = Range("A5:F64")
Set ChangedRange = Intersect(Target, WatchRange)

If ChangedRange Is Nothing Then
MsgBox "You changed a cell NOT in the WatchRange", vbOKOnly
Else
MsgBox "You changed a cell in the WatchRange", vbOKOnly
End If

End Sub

--

Dave Peterson

Dave - works like a charm. Thank you.
Bri

Dave - great advice! I'll be able to incorporate this in my code too.

Would it be difficult to NOT give the warning if the cell is originally
blank? For example, I type 'John' in a blank cell. (no msgbox). Now I
CHANGE it to 'Jon' (msgbox asking for confirmation as in your reply to
Bri).

grateful
anny


"Dave Peterson" wrote in message
...
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim ChangedRange As Range
Dim Resp As Long

Set WatchRange = Range("A5:F64")
Set ChangedRange = Intersect(Target, WatchRange)

If ChangedRange Is Nothing Then
MsgBox "You changed a cell NOT in the WatchRange", vbOKOnly
Else
Resp = MsgBox(Prompt:="You changed a cell in the WatchRange" & vbLf
_
& "Are you sure?", Buttons:=vbYesNo)

If Resp = vbYes Then
'do nothing
Else
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If

End If

End Sub

Bri wrote:

Hello to all

When the user CHANGES an entry in range A5:F64, I'd like a msgbox to ask
for
confirmation, or else undo the change.

So far I can tell if the change is in the desired range (see below), but
I
have no clue as to how to undo the changes if not confirmed by the user.
Any ideas would be appreciated.

Thanks, Brian

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim ChangedRange As Range

Set WatchRange = Range("A5:F64")
Set ChangedRange = Intersect(Target, WatchRange)

If ChangedRange Is Nothing Then
MsgBox "You changed a cell NOT in the WatchRange", vbOKOnly
Else
MsgBox "You changed a cell in the WatchRange", vbOKOnly
End If

End Sub

--

Dave Peterson

The bad news is that you could change multiple cells at one time. You could
keep track of the old values and do a comparison. Maybe using a hidden
worksheet that gets populated with the values in that sheet when the workbook
opens???

I put this under the ThisWorkbook module:

Option Explicit
Private Sub Workbook_Open()
Me.Worksheets("sheet1").Cells.Copy
Me.Worksheets("sheet1Mirror").Range("a1").PasteSpe cial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Then this goes behind the worksheet with the "monitoring" going on:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim ChangedRange As Range
Dim Resp As Long
Dim myCell As Range
Dim FoundANonBlank As Boolean

Set WatchRange = Range("A5:F64")
Set ChangedRange = Intersect(Target, WatchRange)

If ChangedRange Is Nothing Then
MsgBox "You changed a cell NOT in the WatchRange", vbOKOnly
Else
FoundANonBlank = False
For Each myCell In ChangedRange.Cells
If IsEmpty(Me.Parent.Worksheets("sheet1Mirror") _
.Range(myCell.Address).Value) Then
'do nothing, it was empty
Else
FoundANonBlank = True
Exit For
End If
Next myCell

Resp = vbYes
If FoundANonBlank = True Then
Resp = MsgBox(Prompt:="You changed a cell in the WatchRange" _
& vbLf _
& "Are you sure?", Buttons:=vbYesNo)
End If

If Resp = vbYes Then
'copy value to mirrored sheet
For Each myCell In ChangedRange.Cells
Me.Parent.Worksheets("sheet1Mirror") _
.Range(myCell.Address).Value = myCell.Value
Next myCell
Else
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If

End If

End Sub

It seemed to work ok under light testing.

anny wrote:

Dave - great advice! I'll be able to incorporate this in my code too.

Would it be difficult to NOT give the warning if the cell is originally
blank? For example, I type 'John' in a blank cell. (no msgbox). Now I
CHANGE it to 'Jon' (msgbox asking for confirmation as in your reply to
Bri).

grateful
anny

"Dave Peterson" wrote in message
...
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim ChangedRange As Range
Dim Resp As Long

Set WatchRange = Range("A5:F64")
Set ChangedRange = Intersect(Target, WatchRange)

If ChangedRange Is Nothing Then
MsgBox "You changed a cell NOT in the WatchRange", vbOKOnly
Else
Resp = MsgBox(Prompt:="You changed a cell in the WatchRange" & vbLf
_
& "Are you sure?", Buttons:=vbYesNo)

If Resp = vbYes Then
'do nothing
Else
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If

End If

End Sub

Bri wrote:

Hello to all

When the user CHANGES an entry in range A5:F64, I'd like a msgbox to ask
for
confirmation, or else undo the change.

So far I can tell if the change is in the desired range (see below), but
I
have no clue as to how to undo the changes if not confirmed by the user.
Any ideas would be appreciated.

Thanks, Brian

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim ChangedRange As Range

Set WatchRange = Range("A5:F64")
Set ChangedRange = Intersect(Target, WatchRange)

If ChangedRange Is Nothing Then
MsgBox "You changed a cell NOT in the WatchRange", vbOKOnly
Else
MsgBox "You changed a cell in the WatchRange", vbOKOnly
End If

End Sub

--

Dave Peterson

--

Dave Peterson

wow - thanks for all your efforts - works well
anny