Validation Tool Help

[Courtney Ruggles]

I am currently using the validation tool to limit the number of selections in column A of my worksheet to 3 or less (The selections are made using a double-click VB code). The formulat that I am using for the allowance criteria is: =COUNTIF($A$11:$A$10001,"a")<4. Currently, the formula and the limitation is working fine and the error box is popping up at the appropriate time (i.e. when the 4th selection is made). However, if I click "Cancel" in the Error box, the spreadsheet allows the 4th selection to remain. Is it possible to use VB code to force the 4th selection to be deleted once the error box pops up?

Thank you in advance for your time and help!! I appreciate it!
~CR

 

[cornflakegirl]

Courtney

I don't really understand why the BeforeDoubleClick stops the validation working properly - but this code fixed it for me:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If [countif( a11 : a10001, "a")<3] Then
    Target.Value = "a"
Else
    MsgBox "Only 3 selections are allowed"
    Cancel = True
End If
End Sub

You'll want to keep the cell validation too, in case anyone types the "a" directly into the cell.

 

[Courtney Ruggles]

Thank you so much for your help!!

I added:

Target.Cell = Null

after the Cancel = True in order to clear the contents of the 4th selected cell and this worked perfectly! I really appreaciate your help!

Take Care,

~CR

 

[cornflakegirl]

Hmm... I didn't need anything extra to clear the cell - wonder why you did?
Glad it worked though.