Validating cell contentswhen leaving the cell...

[cmarshall01]

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

 

[Doug Glancy]

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