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

**MeiR_ct** · 07-30-2014, 09:13 AM

(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

**stnkynts** · 07-30-2014, 10:13 AM

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?

**MeiR_ct** · 07-31-2014, 01:47 AM

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).

**MeiR_ct** · 08-03-2014, 01:38 AM

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

**Andy Pope** · 08-03-2014, 05:16 AM

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

Quick example of TAB

standard code module

Sub LocalChange()
        If ActiveCell.Column < Selection.Columns(Selection.Columns.Count).Column Then
        ' move across
        ActiveCell.Offset(0, 1).Activate
    Else
        If ActiveCell.Offset(1, 0).Row > Selection.Rows(Selection.Rows.Count).Row Then
            Selection.Cells(1, 1).Activate
        Else
            ActiveCell.Offset(1, -(Selection.Columns.Count - 1)).Activate
        End If
    End If
    Debug.Print ActiveCell.Address
End Sub

sheet object code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.OnKey "{TAB}", "LocalChange"
End Sub

At some point you will also need to cancel the OnKey

**MeiR_ct** · 08-03-2014, 06:57 AM

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?

**Andy Pope** · 08-04-2014, 06:20 AM

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

**MeiR_ct** · 08-06-2014, 03:53 AM

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.

**Andy Pope** · 08-06-2014, 04:41 AM

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

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

**MeiR_ct** · 08-07-2014, 05:28 AM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View