• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1257
  • Last Modified:

Excel VBA event - change of active cell

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
sjgrey
Asked:
sjgrey
5 Solutions
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
 
longtruongCommented:
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
 
Jon von der HeydenCourse Leader & Managing DirectorCommented:
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
 
SANTABABYCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now