Tom,

I thought it was working, but it's not. Do you know what would cause the
below code to run when it should if cell B5 was the target, but if I only
change the target cell to E5, it runs even if Range("AA5").Value = "". It
only runs once though and at the end I get an error box, "application defined
or object define error?" Thanks.

On Error goto ErrHandler

If Target.Address = "$B$5" And Range("AA5").Value <> "" Then
Application.EnableEvents = False
Call Input_FieldsNoPartNumber
End If

ErrHandler:
Application.EnableEvent = True

End Sub




"Tom Ogilvy" wrote:

> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error goto ErrHandler
>
> If Target.Address = "$B$5" And Range("AA5").Value <> "" Then
> Application.EnableEvents = False
> Call Input_FieldsNoPartNumber
> Elseif Target.Address = "$E$5" And Range("AA5").Value <> "" Then
> Application.EnableEvents = False
> Call Input_FieldsNoChangeLevel
> End If
>
> ErrHandler:
> Application.EnableEvent = True
>
> End Sub
>
> Would be my suggestion.
>
> the procedures shouldn't be called unless you make a change in B5 or E5.
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Alex" wrote:
>
> > When a user first opens a template worksheet he clicks a button which runs a
> > macro containing unput boxes for him to input information. If, during the
> > course of filling out the worksheet, he changes the value in cell B5 or cel
> > E5 I need the input boxes to run again. In order to keep the first input
> > boxes from running continually, I know I need Application.EnableEvents in the
> > worksheet_change code. I can't get the worksheet_change code to work
> > properly, however. The initial input boxes either run continually or the
> > macros listed below don't run at all when B5 or E5 change. Can someone help
> > me fix the below code. Thanks.
> >
> > fyi - I have 3 modules. The first one runs all input boxes. The one
> > referenced below called input_fieldsNoPartNumber runs all input boxes but the
> > partnumber box, which initially populates cell B5 and the module called
> > input_fieldsnochangelevel runs all input boxes except the change level field,
> > which initially populates cell E5.
> >
> > code below is one of the many variations I've tried.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > If Target.Address = "$B$5" And Range("AA5").Value <> "" Then
> > Application.EnableEvents = False
> > Call Input_FieldsNoPartNumber
> > 'Application.EnableEvents = True
> > End If
> > 'Exit Sub
> >
> > If Target.Address = "$E$5" And Range("AA5").Value <> "" Then
> > Application.EnableEvents = False
> > Call Input_FieldsNoChangeLevel
> > Application.EnableEvents = True
> > 'Exit Sub
> > End If
> >
> > End Sub