Call sub from worksheet_change

[JMJ]

Hi all,
I've been wrestling with this and I cannot see where I have the error. For
some reason it does not call the macro: "Validate". Any help would be greatly
appreciated.

I have the following code in Sheet 1

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
Application.ScreenUpdating = True
If Not Application.Intersect(Target, Range("F14")) Is Nothing Then
With Target
If .Value <> "" Then

Validate

Else ' do other..
End If
End With
End If

Exit Sub
ws_exit:
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "Error ...."
End Sub
------------------------
In Module1 I have the code to validate cell B8.

Sub Validate()
With Range("B8").Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=A1", Formula2:="=A5"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub

 

[Gary''s Student]

You need:

Application.EnableEvents = True
Application.ScreenUpdating = True

before exiting ( you are currently executing them only on error)

 

[JMJ]

It still doesn't call it. ...

You need:

Application.EnableEvents = True
Application.ScreenUpdating = True

before exiting ( you are currently executing them only on error)

 

[Gary''s Student]

First fix the event code
Next run something like:

Sub en()
Application.EnableEvents = True
End Sub

Then try the data entry

 

[JMJ]

The events are enabled.

First fix the event code
Next run something like:

Sub en()
Application.EnableEvents = True
End Sub

Then try the data entry

 

[Gary''s Student]

This works for me:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
Application.ScreenUpdating = True
If Not Intersect(Target, Range("F14")) Is Nothing Then
With Target
If .Value <> "" Then
Call Validate
Else
End If
End With
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

ws_exit:
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "Error ...."
End Sub

Note I changed about three lines.

 

[JMJ]

Thanks!

For some reason it did not like the "Application.Intersect" (?) ...

Correction: At the beginning of the sub I have an
Application.ScreenUpdating = True
and it should be an "Application.ScreenUpdating = False"