Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA event - change of active cell

Posted on 2011-03-10
5
Medium Priority
?
978 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 400 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 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.

Long
0
 
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

0
 
LVL 10

Assisted Solution

by:SANTABABY
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.
0
 
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

Kevin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

721 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