Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom data validation doesn't work well
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom data validation doesn't work well
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 "clara" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation | Excel Discussion (Misc queries) | |||
Custom Data Validation | Excel Discussion (Misc queries) | |||
Custom Data Validation | Excel Discussion (Misc queries) | |||
Custom data validation | Excel Discussion (Misc queries) | |||
Data Validation - Custom | Excel Discussion (Misc queries) |