using Dirty Method

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

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" <gstars@look.ca> wrote in message
news:%23m7WuFmKGHA.3728@tk2msftngp13.phx.gbl...
> 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
>
>
>

thank you Norman
Giselle

"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:eUH2wsmKGHA.2304@TK2MSFTNGP15.phx.gbl...
> 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" <gstars@look.ca> wrote in message
> news:%23m7WuFmKGHA.3728@tk2msftngp13.phx.gbl...
>> 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
>>
>>
>>
>
>
>

Greetings Norman & others

This code works perfectly at asking for confirmation before EXISTING values
in the range("C5:H7") are changed. But, I need to allow users to make
initial entries first, asking for confirmation if they then try to change a
value. (This usually happens when the users are quickly trying to enter
data without realizing they are typing over data they have already entered.)

Any ideas?
Thanks
Giselle



"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:eUH2wsmKGHA.2304@TK2MSFTNGP15.phx.gbl...
> 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" <gstars@look.ca> wrote in message
> news:%23m7WuFmKGHA.3728@tk2msftngp13.phx.gbl...
>> 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
>>
>>
>>
>
>
>

Hi Giselle.

To correct a cell selection problem, to handle possible formula entries, and
to handle the replacement of a cell entry with an identical entry (i.e
effectively no change), try the following revision:

'=============>>
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 = Target.Formula
Application.Undo
oldVal = rng.Formula

With rng
If Not IsEmpty(.Value) And newVal <> oldVal 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
.Formula = oldVal
Else
.Formula = newVal
End If
Else
.Formula = newVal
End If
End With
End If

If Not res = vbNo Then Me.Range(sAdd).Activate

XIT:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub
'<<=============

> But, I need to allow users to make initial entries first, asking for
> confirmation if they then try to change a value.

I believe that the code does this: an entry in an empty cell engenders no
response; a replacement with an identical entry engenders no response. Any
change in value/formula in the specified range invokes the message box.
Depending on the response to the message box, the new entry is retained or
the previous entry is restored. In the latter case, the cursor is returned
to the cell with the aborted entry,


---
Regards,
Norman



"Giselle" <gstars@look.ca> wrote in message
news:OQee$FsKGHA.3732@TK2MSFTNGP10.phx.gbl...
> Greetings Norman & others
>
> This code works perfectly at asking for confirmation before EXISTING
> values in the range("C5:H7") are changed. But, I need to allow users to
> make initial entries first, asking for confirmation if they then try to
> change a value. (This usually happens when the users are quickly trying
> to enter data without realizing they are typing over data they have
> already entered.)
>
> Any ideas?
> Thanks
> Giselle
>
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:eUH2wsmKGHA.2304@TK2MSFTNGP15.phx.gbl...
>> 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" <gstars@look.ca> wrote in message
>> news:%23m7WuFmKGHA.3728@tk2msftngp13.phx.gbl...
>>> 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
>>>
>>>
>>>
>>
>>
>>
>
>

That was nifty Norman.

Davidm

very COOL
Giselle