Preventing change of a cell value
 

Hi,

Value in column A determines values in other cells in the same row.
I want the user to be warned before changing the value in column A
that the contents of the row will be deleted as values in other cells
depend on the value in column A. I am using the worksheet change event
for this. Unfortunately, this works only after the value in Column A
is changed. The code I am using in the worksheet change event is:
If Inputsheet.Cells(Selection.Row, 9) < "". Then etc.

Here column 9 is selected as the value in column 9 is derived through
a series of changes in the intervening columns: all decided by the
value in column 1(A).

Any help on how to prevent change to cell in column A before changing
the value will be welcome.

Thanks in advance for the help.

Regards,
Raj

Preventing change of a cell value
 

Hi Raj,

In the worksheet's code module replace
your existing Workshhet_Change code
with the following version:

'==========
Option Explicit

Private Sub Worksheet_Change( _
ByVal Target As Range)
Dim Rng As Range
Dim rcell As Range
Const sCol As String = "I:I" '\\ Column 9
Const sMsg As String = _
"Your Alert message" '<<==== CHANGE

Set Rng = Intersect(Me.Range(sCol), Target)

If Not Rng Is Nothing Then
With Rng
If Rng.Count Application.CountA(.Cells) Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
MsgBox Prompt:=sMsg, Buttons:=vbCritical
End With
End If
End With
End If
End Sub
'<<==========
---
Regards.
Norman


"Raj" wrote in message
...
Hi,

Value in column A determines values in other cells in the same row.
I want the user to be warned before changing the value in column A
that the contents of the row will be deleted as values in other cells
depend on the value in column A. I am using the worksheet change event
for this. Unfortunately, this works only after the value in Column A
is changed. The code I am using in the worksheet change event is:
If Inputsheet.Cells(Selection.Row, 9) < "". Then etc.

Here column 9 is selected as the value in column 9 is derived through
a series of changes in the intervening columns: all decided by the
value in column 1(A).

Any help on how to prevent change to cell in column A before changing
the value will be welcome.

Thanks in advance for the help.

Regards,
Raj

Preventing change of a cell value
 

Give the following worksheet Change event code try (the user will be asked
if he/she really wants to change the value and, if the answer is no, the
original value for the cell will be restored)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Answer As Long
If Target.Column = 1 Then
On Error GoTo Done
Application.EnableEvents = False
Answer = MsgBox("Changing this value will delete other values " & _
"in this row." & vbCrLf & vbCrLf & "Did you " & _
"want to still change the cell's value?", vbYesNo)
If Answer = vbNo Then Application.Undo
Else
'
' Your non-Column "A" change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub


Rick


"Raj" wrote in message
...
Hi,

Value in column A determines values in other cells in the same row.
I want the user to be warned before changing the value in column A
that the contents of the row will be deleted as values in other cells
depend on the value in column A. I am using the worksheet change event
for this. Unfortunately, this works only after the value in Column A
is changed. The code I am using in the worksheet change event is:
If Inputsheet.Cells(Selection.Row, 9) < "". Then etc.

Here column 9 is selected as the value in column 9 is derived through
a series of changes in the intervening columns: all decided by the
value in column 1(A).

Any help on how to prevent change to cell in column A before changing
the value will be welcome.

Thanks in advance for the help.

Regards,
Raj