I want to have a cell that shows the current position of the selected cell.

So if the formula was in A1 and the current cell selected was B21 - I want
A1 to show "B21" - is this possible?

Thanks

Andrew

On Fri, 3 Feb 2006 08:07:58 -0800, "AJPendragon"
wrote:

I want to have a cell that shows the current position of the selected cell.

So if the formula was in A1 and the current cell selected was B21 - I want
A1 to show "B21" - is this possible?

Thanks

Andrew


The problem with doing this by formula is that formulas only update on a
calculation event; and change the cell selection does not ordinarily do that.

You could "force" that to happen, but then you'd be doing a calculation
everytime you moved the cursor and selected a new cell -- that would really
slow down work on your workbook unless it were extremely simple.

You could display the selected range in A1 with a simple event-triggered
(selection change) macro.

If you did not want to hard-wire that to A1 (or some other specific cell), you
could also have the macro check a different cell in which you stored the
address where you wanted the selected cell address displayed.

To enter this code, right click the sheet tab and select View Code from that
menu. Then paste the code below into the window that opens.

1. To show always in A1:

====================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[a1] = Selection.Address
End Sub
====================

2. To show in the address found in A1

================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Range([a1].Text) = Selection.Address
End Sub
==================================

--ron