Hi Will:

Check back tomorrow. The Help Center is really backed up now.
--
Gary''s Student - gsnu200745


"Will Finkle" wrote:

Thanks, Gary's Student, for the quick response and the terrific help.

I'm *almost* there. I've got the uniqueness code, no problem, thank you.

But with the selection change sub, I have modified it thusly to activate the
algorithm whenever the selection is a cell in the A column. But it's exiting
the routing

Here's the code I put in front, so it would only enter the routine for cells
in the A column

If InStr(1, ActiveCell.Address, "$A$") Then
s = "You may not leave the Menu Item empty"
Set r = ActiveCell
End If

But it's exiting out where it checks the intersection of Target & 'r',
because got_there is true (so Exit Sub).

If Not Intersect(Target, r) Is Nothing Then
got_there = True
Exit Sub
End If

So I backed up a step and just tried your code as written, on cell B9, and
even though the code executed all the way through to the end, I was still
able to tab to the next cell without the message box firing. Am I missing
something?

Thanks 1,000,000!

--Will

"Gary''s Student" wrote:

Hi Will:

This is only a single cell example of how to enforce data entry once a cell
has been selected. The example uses cell B9. Once B9 has been Selected by
either the mouse or the arrow keys or the ENTER key or TAB key, data must be
entered before another cell can be Selected. This macro goes in the
worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set r = Range("B9")
s = "You may not leave B9 empty"

If Target.Count 1 Then
Exit Sub
End If

If Not Intersect(Target, r) Is Nothing Then
got_there = True
Exit Sub
End If

If got_there Then
If IsEmpty(r) Then
MsgBox (s)
Application.EnableEvents = False
r.Select
Application.EnableEvents = True
Exit Sub
End If
got_there = False
End If
End Sub


The uniqueness thing is much easier. Say we have a table from Z3 to Z17 and
only want unique entries. Set data validation in Z3 to FormulaIs:

=COUNTIF($Z$3:$Z$17,Z3)<2 and copy the validation down the table

--
Gary''s Student - gsnu200744


"Will Finkle" wrote:

Hi, all,

I've combed the discussion group as well as some of the great web resources
for Excel programming & worksheet functions, and I've come to spend way too
much time on what should be a simple data validation formula. First, I just
wanted to prevent an empty cell in Column A. I noticed that data validation
doesn't seem to kick in if you Tab or Enter over the cell -- DV only is
working when the cursor's in the cell, by typing or clicking in cell or
formular bar.

I have tried the following custom validation rules without success, both
with and without the $ before the A:
=LEN($A)0
=NOT(ISBLANK($A)
=NOT(EMPTY)

They do work if I have the cursor in the cell, but again, Tabbing or
pressing Enter bypasses Data Validation -- am I left with figuring out how to
disable the Tab & Enter keys, or with reexaming the cell entires when the
user leaves this routine? Yuck!

Since I'm in it this far, I also want to have the length of the entry be
from 1 to 64 characters (it's a text field, alphanumeric OK) -- I had this
part working already, but wanted to add the "non-empty" requirement as well,
and now I'm stuck.

If I could also enforce unique entries in the entire column, that woudl be
ideal. I found a formula for this but it's not working. I think it is overly
stringent, preventing even substrings, too, which is not what I want.

Here's the data validation formulas I was using for uniqueness:

This entry goes in DV in cell A1:

=ISERROR(MATCH(A1,A2:A50,0))

and this one in cells A2:A9000

=ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0))

Your help is greatly appreciated, especailly at this late hour,

<grins

Will Finkle
San Diego