Solved

Excel VBA event - change of active cell

Posted on 2011-03-10
5
849 Views
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.

Thanks
0
Comment
Question by:sjgrey
5 Comments
 
LVL 59

Accepted Solution

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

Chris
Option Explicit

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

Open in new window

0
 
LVL 3

Assisted Solution

by:longtruong
longtruong earned 100 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.

Long
0
 
LVL 10

Assisted Solution

by:Jon von der Heyden
Jon von der Heyden earned 100 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

0
 
LVL 10

Assisted Solution

by:SANTABABY
SANTABABY earned 100 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.
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 100 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

Kevin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Copy Macro down foe each row 4 21
Excel 2016 formulas 5 32
NEED TO UPDATE DATA IN EXCEL 18 32
Autofit all columns of all worksheets using VBA 14 17
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now