Call sub from worksheet_change

  • Thread starter Thread starter JMJ
  • Start date Start date
J

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
 
You need:

Application.EnableEvents = True
Application.ScreenUpdating = True

before exiting ( you are currently executing them only on error)
 
It still doesn't call it. ...

Gary''s Student said:
You need:

Application.EnableEvents = True
Application.ScreenUpdating = True

before exiting ( you are currently executing them only on error)
Click to expand...
 
First fix the event code
Next run something like:

Sub en()
Application.EnableEvents = True
End Sub

Then try the data entry
 
The events are enabled.


Gary''s Student said:
First fix the event code
Next run something like:

Sub en()
Application.EnableEvents = True
End Sub

Then try the data entry
Click to expand...
 
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.
 
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"
 
Back
Top