Solved

Excel VBA event - change of active cell

Posted on 2011-03-10
5
890 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

734 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