Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Preventing a cell value based on change in other field | Excel Programming | |||
preventing a cell from being referenced? | Excel Discussion (Misc queries) | |||
Preventing only one cell from recalculating | Excel Programming | |||
preventing reference to a particular cell | Excel Programming | |||
Preventing cell from being empty | Excel Programming |