I cannot get my code to work! i am trying to just check
that when the user enters a value in cell Y13 that its a 9
digit numeric value

but every time i enter a value. it always goes to the last
condition "Must be 9 digits" even when i enter in a
correct value

what's wrong here?! help! :)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y13")) Is Nothing
Then
With Target
'do something
If Len(Y13) = 9 Then
If IsNumeric(Y13) Then
Y13.NumberFormat = "###-###-###"
Else
MsgBox "Must be a number"
End If
Else
MsgBox "Must be 9 digits"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Instead of nesting your if statements, try do it
sequentially. Example:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y13")) Is Nothing
Then
With Target
'do something
If Len(Y13) < 9 Then
MsgBox "Must be 9 digits"
Exit Sub
End If

If IsNumeric(Y13) Then
Y13.NumberFormat = "###-###-###"
Else
MsgBox "Must be a number"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Not sure if that will do it, but logically it will still
flow through the proper validation steps.
-----Original Message-----
I cannot get my code to work! i am trying to just check
that when the user enters a value in cell Y13 that its a
9
digit numeric value

but every time i enter a value. it always goes to the
last
condition "Must be 9 digits" even when i enter in a
correct value

what's wrong here?! help! :)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y13")) Is Nothing
Then
With Target
'do something
If Len(Y13) = 9 Then
If IsNumeric(Y13) Then
Y13.NumberFormat = "###-###-###"
Else
MsgBox "Must be a number"
End If
Else
MsgBox "Must be 9 digits"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


.

Your first if is doing just that. Why not simplify it with something like.


if target.address< "$Y$13" then exit sub

if Len(target) = 9 and IsNumeric(target) Then
target.NumberFormat = "###-###-###"
Else
MsgBox "Error"
End If



--
Don Guillett
SalesAid Software

wrote in message
...
I cannot get my code to work! i am trying to just check
that when the user enters a value in cell Y13 that its a 9
digit numeric value

but every time i enter a value. it always goes to the last
condition "Must be 9 digits" even when i enter in a
correct value

what's wrong here?! help! :)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y13")) Is Nothing
Then
With Target
'do something
If Len(Y13) = 9 Then
If IsNumeric(Y13) Then
Y13.NumberFormat = "###-###-###"
Else
MsgBox "Must be a number"
End If
Else
MsgBox "Must be 9 digits"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Y13 as Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y13")) Is Nothing Then
set Y13 = Target
'do something
If Len(Y13) = 9 Then
If IsNumeric(Y13) Then
Y13.NumberFormat = "###-###-###"
Else
MsgBox "Must be a number"
End If
Else
MsgBox "Must be 9 digits"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


wrote in message
...
I cannot get my code to work! i am trying to just check
that when the user enters a value in cell Y13 that its a 9
digit numeric value

but every time i enter a value. it always goes to the last
condition "Must be 9 digits" even when i enter in a
correct value

what's wrong here?! help! :)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y13")) Is Nothing
Then
With Target
'do something
If Len(Y13) = 9 Then
If IsNumeric(Y13) Then
Y13.NumberFormat = "###-###-###"
Else
MsgBox "Must be a number"
End If
Else
MsgBox "Must be 9 digits"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Y13 as Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y13")) Is Nothing Then
Set Y13 = Target
'do something
If Len(Y13) < 9 Then
MsgBox "Must be 9 digits"
Exit Sub
End If

If IsNumeric(Y13) Then
Y13.NumberFormat = "###-###-###"
Else
MsgBox "Must be a number"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...

Instead of nesting your if statements, try do it
sequentially. Example:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y13")) Is Nothing
Then
With Target
'do something
If Len(Y13) < 9 Then
MsgBox "Must be 9 digits"
Exit Sub
End If

If IsNumeric(Y13) Then
Y13.NumberFormat = "###-###-###"
Else
MsgBox "Must be a number"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Not sure if that will do it, but logically it will still
flow through the proper validation steps.
-----Original Message-----
I cannot get my code to work! i am trying to just check
that when the user enters a value in cell Y13 that its a
9
digit numeric value

but every time i enter a value. it always goes to the
last
condition "Must be 9 digits" even when i enter in a
correct value

what's wrong here?! help! :)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y13")) Is Nothing
Then
With Target
'do something
If Len(Y13) = 9 Then
If IsNumeric(Y13) Then
Y13.NumberFormat = "###-###-###"
Else
MsgBox "Must be a number"
End If
Else
MsgBox "Must be 9 digits"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


.

"Error" isn't very informative. If that is sufficient, then good
suggestion.

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
Your first if is doing just that. Why not simplify it with something like.


if target.address< "$Y$13" then exit sub

if Len(target) = 9 and IsNumeric(target) Then
target.NumberFormat = "###-###-###"
Else
MsgBox "Error"
End If



--
Don Guillett
SalesAid Software

wrote in message
...
I cannot get my code to work! i am trying to just check
that when the user enters a value in cell Y13 that its a 9
digit numeric value

but every time i enter a value. it always goes to the last
condition "Must be 9 digits" even when i enter in a
correct value

what's wrong here?! help! :)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y13")) Is Nothing
Then
With Target
'do something
If Len(Y13) = 9 Then
If IsNumeric(Y13) Then
Y13.NumberFormat = "###-###-###"
Else
MsgBox "Must be a number"
End If
Else
MsgBox "Must be 9 digits"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub