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" 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

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

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" 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

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" wrote in message
...
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" 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

That was nifty Norman.

Davidm


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=508707

Hello,

I really benifited from this example! It is really close to what I
need to do...but I have two quick add on questions..

1. If the cell change is accepted, how to I automatically change the
text to BOLD and RED??

2. Even if there is no previous change, I would like any change to any
cell in the range indicted with a change in text font and color.

Any thoughts?

Thanks,
Matt


--
matt4003
------------------------------------------------------------------------
matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635
View this thread: http://www.excelforum.com/showthread...hreadid=508707

very COOL
Giselle

Hi Matt,

Try inserting the additional code lines:

With Target
.Font.Bold = True
.Interior.ColorIndex = 3
End With

after:

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


---
Regards,
Norman


"matt4003" wrote in
message ...

Hello,

I really benifited from this example! It is really close to what I
need to do...but I have two quick add on questions..

1. If the cell change is accepted, how to I automatically change the
text to BOLD and RED??

2. Even if there is no previous change, I would like any change to any
cell in the range indicted with a change in text font and color.

Any thoughts?

Thanks,
Matt


--
matt4003
------------------------------------------------------------------------
matt4003's Profile:
http://www.excelforum.com/member.php...fo&userid=9635
View this thread: http://www.excelforum.com/showthread...hreadid=508707

Norman,

This is great!! I am really learning a lot and it is perfect for m
project. Can I ask another question, because I think you will be abl
to answer it without thinking:-)

I have four rows of input for each entry (typically there are 50-10
entries per FCST):
1. Potential Sales
2. Sales FCST
3. Shipping FCST
4. Original Booked

I would like a message box to appear (similar to the one in the earlie
code) that says you "Can't change Original Booked". This is becaus
this field is pre-populated and is for reference.

I would imagine the statement to be:

If range(same row, column B) = "Original Booked" Then
res = MsgBox( _
Prompt:="You can't change Original Booked" & _
"to change the value of " & _
"Cell " & rng.Address(0, 0) & "?", _
Buttons:=vbCancel)
If res = Cancel Then
.Formula = oldVal
End If
End With

Any ideas??

Thanks,
Mat

--
matt400
-----------------------------------------------------------------------
matt4003's Profile: http://www.excelforum.com/member.php...nfo&userid=963
View this thread: http://www.excelforum.com/showthread.php?threadid=50870

Anyone have any ideas, I am trying to close out my project today, and am
just stuck:-(

Norman,

This is great!! I am really learning a lot and it is perfect for my
project. Can I ask another question, because I think you will be able
to answer it without thinking:-)

I have four rows of input for each entry (typically there are 50-100
entries per FCST):
1. Potential Sales
2. Sales FCST
3. Shipping FCST
4. Original Booked

I would like a message box to appear (similar to the one in the earlier
code) that says you "Can't change Original Booked". This is because this
field is pre-populated and is for reference.

I would imagine the statement to be:

If range(same row, column B) = "Original Booked" Then
res = MsgBox( _
Prompt:="You can't change Original Booked" & _
"to change the value of " & _
"Cell " & rng.Address(0, 0) & "?", _
Buttons:=vbCancel)
If res = Cancel Then
..Formula = oldVal
End If
End With

Any ideas??

Thanks,
Matt


--
matt4003
------------------------------------------------------------------------
matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635
View this thread: http://www.excelforum.com/showthread...hreadid=508707

Hi Matt,

Try something like:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rng2 As Range
Dim res As Long
Dim oldVal As Variant
Dim newVal As Variant
Dim sAdd As String
Dim msg As String

msg = "You can't change Original Booked " & _
"to change the value of Cell "

If Target.Count 1 Then Exit Sub

Set rng = Intersect(Me.Range("C5:H7"), Target)
Set rng2 = Intersect(Me.Columns("A"), Target)

On Error GoTo XIT

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

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
If Not res = vbNo Then Me.Range(sAdd).Activate

With Target
.Font.Bold = True
.Interior.ColorIndex = 3
End With
End If

If Not rng2 Is Nothing Then
With rng2
If LCase(.Offset(0, 1).Value) = "original booked" Then
Application.Undo

MsgBox Prompt:=msg & Target.Address(0, 0), _
Buttons:=vbCritical, _
Title:="Locked Field"
End If
End With
End If

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

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

This assumes that the headings are in column B and the data is entered in
column A.

---
Regards,
Norman



"matt4003" wrote in
message ...

Anyone have any ideas, I am trying to close out my project today, and am
just stuck:-(

Norman,

This is great!! I am really learning a lot and it is perfect for my
project. Can I ask another question, because I think you will be able
to answer it without thinking:-)

I have four rows of input for each entry (typically there are 50-100
entries per FCST):
1. Potential Sales
2. Sales FCST
3. Shipping FCST
4. Original Booked

I would like a message box to appear (similar to the one in the earlier
code) that says you "Can't change Original Booked". This is because this
field is pre-populated and is for reference.

I would imagine the statement to be:

If range(same row, column B) = "Original Booked" Then
res = MsgBox( _
Prompt:="You can't change Original Booked" & _
"to change the value of " & _
"Cell " & rng.Address(0, 0) & "?", _
Buttons:=vbCancel)
If res = Cancel Then
Formula = oldVal
End If
End With

Any ideas??

Thanks,
Matt


--
matt4003
------------------------------------------------------------------------
matt4003's Profile:
http://www.excelforum.com/member.php...fo&userid=9635
View this thread: http://www.excelforum.com/showthread...hreadid=508707

Hello Norman,

Thanks for the help. Unfortunately, I am using a range of cells
(F92:Q235) Yes, column "B" is where the "Orig. Booked Quantity"
statement is. How do I make the reference more dynamic?

Thanks,
Matt


--
matt4003
------------------------------------------------------------------------
matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635
View this thread: http://www.excelforum.com/showthread...hreadid=508707

Hi Matt,

Try:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rng2 As Range
Dim res As Long
Dim oldVal As Variant
Dim newVal As Variant
Dim sAdd As String
Dim msg As String

msg = "You can't change Original Booked " & _
"to change the value of Cell "

If Target.Count 1 Then Exit Sub

Set rng = Intersect(Me.Range("C5:H7"), Target)
Set rng2 = Intersect(Me.Range("F92:Q235"), Target)

On Error GoTo XIT

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

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
If Not res = vbNo Then Me.Range(sAdd).Activate

With Target
.Font.Bold = True
.Interior.ColorIndex = 3
End With
End If

If Not rng2 Is Nothing Then
With rng2
If LCase(Cells(.Row, "B").Value) = "original booked" Then
Application.Undo

MsgBox Prompt:=msg & Target.Address(0, 0), _
Buttons:=vbCritical, _
Title:="Locked Field"
End If
End With
End If

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

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

--
---
Regards,
Norman



"matt4003" wrote in
message ...

Hello Norman,

Thanks for the help. Unfortunately, I am using a range of cells
(F92:Q235) Yes, column "B" is where the "Orig. Booked Quantity"
statement is. How do I make the reference more dynamic?

Thanks,
Matt


--
matt4003
------------------------------------------------------------------------
matt4003's Profile:
http://www.excelforum.com/member.php...fo&userid=9635
View this thread: http://www.excelforum.com/showthread...hreadid=508707

Hello Norman,

Thanks for your quick response. Still fighting this.

My data falls in F92:Q235
"Original Booked" indicator is in column B. So how do I change the rng
to match this?? Is this the only thing that needs to be changed so that
if I try to add or change a value in the row which contains "Original
Booked" in column "B", it would give me a msg saying NO...

Set rng = Intersect(Me.Range("C5:H7"), Target)
Set rng2 = Intersect(Me.Range("F92:Q235"), Target)

Thanks,
Matt


--
matt4003
------------------------------------------------------------------------
matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635
View this thread: http://www.excelforum.com/showthread...hreadid=508707

Hi Matt,

so that if I try to add or change a value in the row which contains
"Original Booked" in column "B", it would give me a msg saying NO...

In my tests, the code included in my last post does exactly that - with the
more detailed message previously proposed by you.


---
Regards,
Norman


"matt4003" wrote in
message ...

Hello Norman,

Thanks for your quick response. Still fighting this.

My data falls in F92:Q235
"Original Booked" indicator is in column B. So how do I change the rng
to match this?? Is this the only thing that needs to be changed so that
if I try to add or change a value in the row which contains "Original
Booked" in column "B", it would give me a msg saying NO...

Set rng = Intersect(Me.Range("C5:H7"), Target)
Set rng2 = Intersect(Me.Range("F92:Q235"), Target)

Thanks,
Matt


--
matt4003
------------------------------------------------------------------------
matt4003's Profile:
http://www.excelforum.com/member.php...fo&userid=9635
View this thread: http://www.excelforum.com/showthread...hreadid=508707