Hi Jon,

You can't create additional events as you have done. You are restricted to
the ones you can create by selecting Worksheet from the dropdown at the top
of the VBA editor and then the events allowable are the ones you can see at
the dropdown to the right top. You have to then code based on identifying the
target as below.

Note I have included the test for length of the string with the IsEmpty test.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub

If IsEmpty(Target.Value) Or _
Len(Target) < 11 Then Exit Sub

Select Case Target.Row
Case 11
Target.Offset(-10, 2).Select
Case 23
Target.Offset(-10, 2).Select
Case 35
Target.Offset(-10, 2).Select
Case 47
Target.Offset(-10, 2).Select
End Select
End Sub


--
Regards,

OssieMac


"Jon M." wrote:

Hi all,
I have a worksheet with the following code listed below. The idea is that
once a cell in a particular row is populated with an 11 digit claim# it will
jump to the first cell 2 columns over at the top of the particular table of
claim#'s. I hope that makes sense. Anyways the first Function works fine,
when I put an 11 digit claim# in row 11 and press enter the correct cell is
selected at the top of the next column I want to enter in.

However when I try to do the same in Rows 23, 35, and 47 nothing happens.
My code is the same for these functions so I can't figure out why the other
rows won't function the same. As always any help is always appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Row = 11 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
Else
Exit Sub
End If
End If
End Sub

Private Sub Worksheet_Change_2(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Row = 23 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
Else
Exit Sub
End If
End If
End Sub

Private Sub Worksheet_Change_3(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Row = 35 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
Else
Exit Sub
End If
End If
End Sub

Private Sub Worksheet_Change_4(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Row = 47 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
Else
Exit Sub
End If
End If
End Sub
--
Jon M.