+ Reply to Thread
Results 1 to 11 of 11

Adding a new worksheet_change event

  1. #1
    Forum Contributor
    Join Date
    09-19-2012
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    137

    Adding a new worksheet_change event

    i have a worksheet change event and want to add another. Here is the one I already have:
    Please Login or Register  to view this content.
    Now I would like to add something like:

    Case "E47:E2000"
    'If (the value in any cell in the above range is changed to "apples*") Then
    Call Find_Document
    Else
    End Select

    Much appreciation to anyone who can point me in the right direction!

    Willardio

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Adding a new worksheet_change event

    Try this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    09-19-2012
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Adding a new worksheet_change event

    Returns "syntax error"
    Please Login or Register  to view this content.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Adding a new worksheet_change event

    Just a couple of typos, a missing closeing parentheses in the first If and a missing closing quote in the second If.

  5. #5
    Forum Contributor
    Join Date
    09-19-2012
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Adding a new worksheet_change event

    Yes, it works great, thank you!
    How can it be modified to include all text starting with "apples", like "apples" & "*"?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Adding a new worksheet_change event

    Not sure what you mean by like "*", that would mean any text.

    If you just want to check if the cell contains 'apples' you can use Like.
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    09-19-2012
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Adding a new worksheet_change event

    I tried it like this, it does nothing for "apples":
    Please Login or Register  to view this content.
    also, if the cell contents are deleted it returns "Type mismatch"
    Is there a way around that?

  8. #8
    Forum Contributor
    Join Date
    09-19-2012
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Adding a new worksheet_change event

    I tried it like this, it does nothing for "apples":
    Please Login or Register  to view this content.
    also, if the cell contents are deleted it returns "Type mismatch"
    Is there a way around that?

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Adding a new worksheet_change event

    The code works for me if 'apples' is entered in E47:E2000, and I don't receive an error when the cell is deleted, only when multiple cells are deleted or changed.

    One way the error can be dealt with is by checking the no of cells in Target.
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    09-19-2012
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Adding a new worksheet_change event

    Excellent Norie, thanks very much!
    the like doesn't work here for some strange reason, but since there are only two specific selections in a drop-list that i need to trigger the event, I went with:
    Please Login or Register  to view this content.
    Thanks again!

    Best Regards,

    Willardio

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Adding a new worksheet_change event

    The Like should work.

    What are the possible values in the dropdown?

+ 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