Custom data validation doesn't work well

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

clara - 18 Jun 2007 13:41 GMT

Hi all,

I want to limit the input order using custom validation. My case is as
following:

From a1 to a10, i want user input one by one in order, so I use custom
validation and my formula is =a1<>"" . after I clear the Ingore Blank, it
works well. But I also limit the order of delete, that means user has to
delete from A10 to a1 one by one in that order so I use formula like:
=if(a2<>"", A1<>"",A3=""),when a2 <>"" means user input data, so check the
above cell a1 and only when a1<>"" user can input, when a2="" mean user
delete data, and check below a3 and only when a3="" user can delete a2.
inserting works fine, but whne deleting even the formula is false, user can
still delete data? Could you help me fingure it out?

Clara
Thank you for your help

Reply to this Message

Bernie Deitrick - 18 Jun 2007 15:16 GMT

Clara,

You need to use the worksheet change event. Copy the code below, right-click the sheet tab, select
"View Code", and paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" And Application.CountA(Range("A1:A10")) >= Target.Row Then
MsgBox "You can only delete cell " & _
Cells(Application.CountA(Range("A1:A10")) + 1, 1).Address(False, False)
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
If Target.Value <> "" And Application.CountA(Range("A1:A10")) < Target.Row Then
MsgBox "You can only fill in cell " & _
Cells(Application.CountA(Range("A1:A10")), 1).Address(False, False)
Application.Undo
Application.EnableEvents = True
End If

End Sub

> Hi all,
>
[quoted text clipped - 13 lines]
> Clara
> Thank you for your help

Reply to this Message