Otto
Wed May 10 15:49:50 CDT 2006
Here is some example code. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
If Not Intersect(Target, Range("B9:F20")) Is Nothing Then
vVal = Target.Value
Application.EnableEvents = False
Application.Undo
vVal1 = Target.Value
res = MsgBox("Do you want to replace " & vVal1 & _
vbNewLine & "with " & vVal, vbQuestion + vbYesNo)
If res = vbYes Then
Target.Value = vVal
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub
"Otto Moehrbach" <ottokmnop@comcast.net> wrote in message
news:%2380R3AHdGHA.5116@TK2MSFTNGP02.phx.gbl...
> You are right that Excel will not react until after the entry is made.
> But you are not right when you say that Excel cannot restore original
> values. The code logic goes something like this:
> The user makes an entry.
> The code sets a variable, say NewVal, equal to that new entry.
> The code issues an Undo command.
> The code sets a variable, say OldVal, equal to that old entry.
> The code evaluates the old and new entries and evaluates them.
> The code takes action as required. This action can be leave the old value
> or re-enter the new value or do something else..
> When writing this Undo coding, you have to be careful. Certain actions,
> by the code, can clear the Undo buffer. If the code then issues and Undo
> command, you will get an error because the Undo buffer is empty. For this
> reason, the Undo command is usually placed very near the start of the
> code.
> I hope this clears some of this up for you. HTH Otto
>
> "Diva" <divakarprabhu2000@yahoo.com> wrote in message
> news:1147225139.137724.10270@y43g2000cwc.googlegroups.com...
>> Hi Experts,
>> You know that DATA VALIDATION works only in case of entering values in
>> cells by typing (prvents entering wrong values). It does not work in
>> case of pasting. It also does not work in case of cells having formula.
>> I read in some books that we can use worksheet_Change event or
>> worksheet_Calculate to validate data through code. But code can
>> validate data only after cell is changed. It means we can not prevent
>> wrong entries, we also can not restore original values. Please tell me,
>> is my conception right? or is there any way to handle this problem.
>> Regards,
>> Diva
>>
>
>