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.
--
Gary''s Student - gsnu200783
"JMJ" wrote:
>
> The events are enabled.
>
>
> "Gary''s Student" wrote:
>
> > First fix the event code
> > Next run something like:
> >
> > Sub en()
> > Application.EnableEvents = True
> > End Sub
> >
> > Then try the data entry
> > --
> > Gary''s Student - gsnu200783
> >
> >
> > "JMJ" wrote:
> >
> > > It still doesn't call it. ...
> > >
> > > "Gary''s Student" wrote:
> > >
> > > > You need:
> > > >
> > > > Application.EnableEvents = True
> > > > Application.ScreenUpdating = True
> > > >
> > > > before exiting ( you are currently executing them only on error)
> > > > --
> > > > Gary''s Student - gsnu200783
> > > >
> > > >
> > > > "JMJ" wrote:
> > > >
> > > > > 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
> > > > > ----------------------
> > > > > Thanks in advance for any help you can give me.
> > > > >
> > > > >
|
Similar Threads
