Mind-numbing simple data validation ... string from 1 to 64 chars

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi Will:

Check back tomorrow. The Help Center is really backed up now.

 

[Guest]

Thanks, Gary, will do -- I appreciate your letting me know!

 

[Guest]

Hi Will:

1. First delete the old macro. “B9†is just too “benignâ€!
2. In a standard module, enter:

Public where_was_I As Range
Sub startup()
Set where_was_I = ActiveCell
Application.EnableEvents = True
End Sub

The Public, static, variable where_was_I “remembers†where we were before
changing Selection. Startup initializes this variable.

3. In the worksheet code area, enter:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' Version 2 - The Sequel
'
Dim on_a As Boolean
Dim was_on_a As Boolean
Set ra = Range("A:A")
s = "You may not leave a column A cell empty"

was_on_a = True
If Intersect(where_was_I, ra) Is Nothing Then
was_on_a = False
End If

If Target.Address = where_was_I.Address Then Exit Sub

If was_on_a Then
If IsEmpty(where_was_I) Then
MsgBox (s)
Application.EnableEvents = False
where_was_I.Select
Application.EnableEvents = True
Exit Sub
Else
Set where_was_I = Target
End If
Else
Set where_was_I = Target
End If
End Sub

4. Before changing Selection, run the startup macro


The logic is a little funky. Whenever we move to a new cell, we look back
to where we came from (previous cell). If the previous cell was on column A
and that previous cell is empty, the warning is issued and the user is kicked
back to that previous cell.

Just remember to delete the old version prior to installing the new one.

Update this post to let us know how you are doing.

 

[Guest]

Gary's Student,

Thank you so much for the thoughtful posting of your Version 2 ! It indeed
works like a charm. It's even almost *too* restrictive... just kidding...
it's great.

I've lots more to tackle before thsi project is complete, though, so I may
be back later with questions on anything from ...
--programmatic data validation, to
--printing all the visible worksheets & charts in a workbook from a Forms
2.0 button on a worksheet, in 1 step.. or at least getting all the pages into
Print Preview at the same time...
--to using named ranges to move around columns of data several times, versus
other methods I've heard of, like Lists or Arrays or Scenarios

But yes, this issue is closed out & thanks again for your help.

--Will Cyclometric