using Dirty Method
thank you Norman
Giselle
"Norman Jones" wrote in message
...
Hi Giselle:
Try:
'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim res As Long
Dim oldVal As Variant
Dim newVal As Variant
Dim sAdd As String
If Target.Count 1 Then Exit Sub
On Error GoTo XIT
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set rng = Intersect(Me.Range("C5:H7"), Target)
If Not rng Is Nothing Then
sAdd = ActiveCell.Address
newVal = rng.Value
Application.Undo
oldVal = rng.Value
With rng
If Not IsEmpty(.Value) Then
res = MsgBox( _
Prompt:="Are you sure you want " & _
"to change the value of " & _
"Cell " & rng.Address(0, 0) & "?", _
Buttons:=vbYesNo)
If res = vbNo Then
.Value = oldVal
Else
.Value = newVal
End If
End If
End With
End If
Me.Range(sAdd).Activate
XIT:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
'<<=============
This is worksheet event code and should be pasted into the worksheets's
code module (not a standard module and not the workbook's ThisWorkbook
module):
Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.
---
Regards,
Norman
"Giselle" wrote in message
...
greetings
I have a range of cells (C5:H7) where users can enter data. But, if they
go back to change a previously entered value, I'd like a msg box to say
"Are you sure you want to change this value?".
I suspect the Dirty Method is involved, but I can't get the syntax to
work.
Any clues?
Cheers, Giselle
|