Data validation through code

[Diva]

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

 

[Otto Moehrbach]

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

 

[Otto Moehrbach]

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

 

[Diva]

Oh!!!, It works....., I thought it to be impossible. I am greatful to
you Otto, Thank you very much. It helps me a lot.
Regards,
Diva

 

[Otto Moehrbach]

Glad to help anytime. Otto