+ Reply to Thread
Results 1 to 4 of 4

Worksheet_Change problem

  1. #1
    Alex
    Guest

    Worksheet_Change problem

    The below code runs fine if cell B5 has changed. However, if I replace B5 in
    the code below with E5, the code 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?" I can replace the below code with any cell except
    E5 and it works perfectly. I've tried changing the format of cell E5 and
    various other things and I still can't get it to work. Any ideas?

    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


  2. #2
    Tom Ogilvy
    Guest

    RE: Worksheet_Change problem

    one big typo on my part

    Errhandler:
    Application.EnableEvent = True

    should be

    ErrHandler:
    Application.EnableEvents = True

    Put an s on EnableEvent.

    --
    Regards,
    tom Ogilvy



    "Alex" wrote:

    > The below code runs fine if cell B5 has changed. However, if I replace B5 in
    > the code below with E5, the code 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?" I can replace the below code with any cell except
    > E5 and it works perfectly. I've tried changing the format of cell E5 and
    > various other things and I still can't get it to work. Any ideas?
    >
    > 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
    >


  3. #3
    Alex
    Guest

    RE: Worksheet_Change problem

    That's not it - I caught that right away

    "Tom Ogilvy" wrote:

    > one big typo on my part
    >
    > Errhandler:
    > Application.EnableEvent = True
    >
    > should be
    >
    > ErrHandler:
    > Application.EnableEvents = True
    >
    > Put an s on EnableEvent.
    >
    > --
    > Regards,
    > tom Ogilvy
    >
    >
    >
    > "Alex" wrote:
    >
    > > The below code runs fine if cell B5 has changed. However, if I replace B5 in
    > > the code below with E5, the code 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?" I can replace the below code with any cell except
    > > E5 and it works perfectly. I've tried changing the format of cell E5 and
    > > various other things and I still can't get it to work. Any ideas?
    > >
    > > 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
    > >


  4. #4
    Dave Peterson
    Guest

    Re: Worksheet_Change problem

    I'd guess that there's a space (or some kind of whitespace) in AA5.

    msgbox len(range("aa5").value)
    may help

    or
    If Target.Address = "$B$5" And trim(Range("AA5").Value) <> "" Then

    And I'd suspect something in that Input_fieldsnoPartNumber as the cause of the
    other problem.


    Alex wrote:
    >
    > The below code runs fine if cell B5 has changed. However, if I replace B5 in
    > the code below with E5, the code 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?" I can replace the below code with any cell except
    > E5 and it works perfectly. I've tried changing the format of cell E5 and
    > various other things and I still can't get it to work. Any ideas?
    >
    > 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


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1