Excel VBA event - change of active cell

Posted on 2011-03-10
Medium Priority
Last Modified: 2012-05-11
Is there a way to trigger a macro when the user moves the active cell, i.e. as the user clicks on a cell or uses the up/down/left/right arrows to move between cells?

I want to display information about the row and column selected in a matrix as the user navigates around.

Question by:sjgrey
LVL 59

Accepted Solution

Chris Bottomley earned 400 total points
ID: 35105984
selection change is triggered as the focus changes and returns the new cell address

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
End Sub

Open in new window


Assisted Solution

longtruong earned 400 total points
ID: 35105986
Hello Sjgrey,

You may need to look at the tutorial at this link http://www.ozgrid.com/VBA/run-macros-change.htm.

I hope it helps.

LVL 10

Assisted Solution

by:Jon von der Heyden
Jon von der Heyden earned 400 total points
ID: 35105992
Yes, you would use the Worksheet_SelectionChange event:

For example, right-click your sheet tab > view code and paste this into the code pane.  Close the VBE again, return to the sheet and move around the different cells:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "You have selected " & Target.Address(0, 0)
End Sub

Open in new window

LVL 10

Assisted Solution

SANTABABY earned 400 total points
ID: 35106009
Paste the foollwing in the code for the worksheet of your interest:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells(1, 1) = "Row:" & Target.Row & "  Col:" & Target.Column
End Sub

It displays the Row# & col #  for the selected cell in the top left cell (ie.e A1 or 1,1).
When a range is selected the position of the top left cell of the selected range is displayed.
Customize as necessary.
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 400 total points
ID: 35106199
Technically, no. When you change the active cell the SelectionChange event only triggers IF the selection changes at the same time. But if you only move the active cell within a selection then no event is triggered.

If you are interested in giving the user feedback or helping them visualize the full row and/or column then you might find this row and column liner add-in interesting:

Title: RowLiner
Link: http://www.cpearson.com/excel/rowliner.htm


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question