How to get a cell ref when selected?

Posted by Daniel on November 15, 2001 1:08 PM

It seams like a simple thing to me...

What I want to be able to do is, when one clicks on a cell, the address of that cell will be returned to another cell. Then I would be able to use the returned cells address for a VLOOKUP table.
The closest thing I can find is =ADDRESS but it only works after the cell contents have been changed wich is after the fact for me. I would appreciate a clue on this one. - Thanks

Posted by Juan Pablo on November 15, 2001 1:14 PM

Is this what you want ? it goes in the sheet's module.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("A1") = Selection.Address
End Sub

Juan Pablo

Posted by Dank on November 15, 2001 1:15 PM

There's possibly a non-VBA way of doing this, but I don't know what it is. Therefore, VBA it is.

Say for example that you want the current selection to appear in cell A1. Try this:-

Right click the worksheet tab and choose View Code and enter this....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = Target.Address
End Sub

Is that what you want?

Regards,
Daniel.

Posted by Daniel on November 15, 2001 1:36 PM

That's it!! - Thanks. Can you recommend a VBA reference related to Excel?

Posted by Dank on November 15, 2001 1:44 PM

I learnt all of the basics by reading books - Excel 2000 VBA Programmers reference by John Green and others is a good one. However, I've developed my knowledge by using message boards like this, ElementK Journals and the Microsoft newsgroups.

I'm sure a quick look at Google.com will yield loads of Excel VBA tutorials.

Regards,
Daniel.