address of active cell

I have a cell (ie. c1). How do I make it show the address of the active cell in the worksheet? So, if I click on A1, it will show a1.

Originally Posted by swatsp0p

What is it you are trying to accomplish with this? When you click in a cell (making it the 'Active Cell'), the address of the cell is displayed in the 'Name Box' (just to the left of the Formula bar).

It would require VBA code to insert the cell reference within the cell when clicked. What would you do with a cell that already has data or a formula in it?

The formula: =CELL("address",A1) returns $A$1 in that cell.

More info, please.

Once the cell is clicked, I can do a vlookup on a table I will create to display text, depending on which cell is clicked. I do not like the comment or validation option to display text, so I am trying it this way.
So, =CELL() will not work because it doesn't tell me which cell the user has selected.

I got the solution:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Worksheets("Sheet1").Range("A1").Value = _
Target.Cells(1).Address


End Sub