Hi All,

I've been reading all the posts here to try to figure out what I
thought would be a simple issue. I would like to check the contents of
a cell when I tab out, arrow out or click on some other cell in the
worksheet after having entered some text. If the contents don't meet
certain validation criteria, I need to keep the user in the cell and
not allow movement to another cell. Data/Validation is too easy to
deafeat. I'm using VBA to do this.

Example: I enter "123abcdef" in a cell and than tab to the next cell.
The validation rule states a maximum of 6 charachters can be entered.
The result in this case should leave me in the cell and force me to
reduce the number of charachters to some number 6 or below.

I tried using the Change and the SelectionChange events but they only
seem partially work for my needs.

Can anyone help?

Thanks a lot,
Lakehills

Lakehills,

I know you said you tried the Change event, but this seems like it does what
you want (assuming the cell is A1):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Len(Target.Text) 6 Then
MsgBox "too long!"
Target.Select
End If
End If
End Sub

hth,

Doug

wrote in message
oups.com...
Hi All,

I've been reading all the posts here to try to figure out what I
thought would be a simple issue. I would like to check the contents of
a cell when I tab out, arrow out or click on some other cell in the
worksheet after having entered some text. If the contents don't meet
certain validation criteria, I need to keep the user in the cell and
not allow movement to another cell. Data/Validation is too easy to
deafeat. I'm using VBA to do this.

Example: I enter "123abcdef" in a cell and than tab to the next cell.
The validation rule states a maximum of 6 charachters can be entered.
The result in this case should leave me in the cell and force me to
reduce the number of charachters to some number 6 or below.

I tried using the Change and the SelectionChange events but they only
seem partially work for my needs.

Can anyone help?

Thanks a lot,
Lakehills