+ Reply to Thread
Results 1 to 9 of 9

Is there a way to trigger active cell change event within a *remained* range selection?

  1. #1
    Registered User
    Join Date
    09-05-2010
    Location
    N/A
    MS-Off Ver
    2007
    Posts
    5

    Question Is there a way to trigger active cell change event within a *remained* range selection?

    (cross-posting from a post on MrExcel)

    Hello.

    I wonder how can I trigger a code when the active cell is changed inside a selected range, like when pressing the tab key.
    The "Worksheet_SelectionChange" event fires only when the whole selection is changed.

    I especially need this when using the find dialog:
    When I select a whole column and search inside it, I want the code to insert a comment with information to the active cell, whenever I press "Find Next".
    the problem is that the whole column remains selected, and only the active cell changes and get highlighted with every pressing on "Find Next", therefore "Worksheet_SelectionChange" doesn't fire up.

    I'm using Excel 2007.

    Thanks in advance,
    Meir

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Is there a way to trigger active cell change event within a *remained* range selection

    It sounds to me like you are using Find and Find Next to apply a set of parameters to cells in a range that match the search criteria. Would it be an option for you to do all of that via a subroutine?

  3. #3
    Registered User
    Join Date
    09-05-2010
    Location
    N/A
    MS-Off Ver
    2007
    Posts
    5

    Re: Is there a way to trigger active cell change event within a *remained* range selection

    No. I have to use an event, since I wish to remove the added comment from the previously active cell, and add a new one to the current active cell.
    Besides that, I want this event to be fired up also when I select a whole column, and then press the "Tab" key to browse the selected cells (has nothing to do with search).
    Last edited by MeiR_ct; 08-03-2014 at 01:39 AM.

  4. #4
    Registered User
    Join Date
    09-05-2010
    Location
    N/A
    MS-Off Ver
    2007
    Posts
    5

    Re: Is there a way to trigger active cell change event within a *remained* range selection

    Bump.
    Please tell me if I didn't explain my issue properly.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,434

    Re: Is there a way to trigger active cell change event within a *remained* range selection

    You would need to capture TAB and TAB+SHIFT via the onkey method and reposition the activecell.

    Quick example of TAB

    standard code module
    Please Login or Register  to view this content.
    sheet object code
    Please Login or Register  to view this content.
    At some point you will also need to cancel the OnKey
    Cheers
    Andy
    www.andypope.info

  6. #6
    Registered User
    Join Date
    09-05-2010
    Location
    N/A
    MS-Off Ver
    2007
    Posts
    5

    Re: Is there a way to trigger active cell change event within a *remained* range selection

    Thanks for your help Andy.
    From your reply, I understand there is no any possible way to trap the browsing inside a selected range.
    Do you have an idea what to do about the "Find Next" issue?

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,434

    Re: Is there a way to trigger active cell change event within a *remained* range selection

    I don't think you can capture the Find Next movement as it is actioned within a dialog.

    You could try replicating the Find dialog with user form

  8. #8
    Registered User
    Join Date
    09-05-2010
    Location
    N/A
    MS-Off Ver
    2007
    Posts
    5

    Re: Is there a way to trigger active cell change event within a *remained* range selection

    Thank you. I will consider that.
    Do you think it should be suggested to Microsoft for future Office releases? If you know how, please tell me.
    After all, it is definitely a change in the active cell, but doesn't fire up any event.

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,434

    Re: Is there a way to trigger active cell change event within a *remained* range selection

    You could post a suggestion but don't hold your breath.

    http://answers.microsoft.com/en-us#Office

  10. #10
    Registered User
    Join Date
    09-05-2010
    Location
    N/A
    MS-Off Ver
    2007
    Posts
    5

    Re: Is there a way to trigger active cell change event within a *remained* range selection

    Ok. Thanks again!
    I'm not sure, so please you tell me if I should mark this thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to trigger event change
    By jrholden in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-26-2014, 11:34 AM
  2. [SOLVED] Trigger Change Event
    By alienware in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-27-2012, 07:25 AM
  3. [SOLVED] How to trigger a selection change between a cell and a shape
    By Gerold Kriechbaumer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2006, 01:35 PM
  4. [SOLVED] Cell value change to trigger macro (worksheet change event?)
    By Neil Goldwasser in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2006, 10:00 AM
  5. Worksheet Change Event-change event to trigger
    By Steph in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-18-2005, 06:05 PM

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