Getting the name of highlithed cell

[Tim C]

Is there a way of getting the name of the highlight cell?

I want a cell to get the name of the highlighted cell.
Ex. if I was on C13 cell for instance (not in edit mode), I want let's say F3 to have C13 displayed on recalculation. If I changed the highlighted cell to C15 and recalculate, cell F3 would now show C13.
any ideas.. this maybe an easy one, but I;m not all the greatest in excel..

Thanx

Tim C

 

[Mudface]

Not entirely with you, but this might do what you want. Right-click on your sheet tab, select View Code and copy and paste in the following. Whenever you recalculate, the activecell's address will appear in cell F3: -
<pre>
Private Sub Worksheet_Calculate()

Range("F3").Value = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)

End Sub
</pre>

 

[Juan Pablo González]

You can do this with a VBA macro, something like this:

<pre>Function CELLADDRESS(Optional RefType As Integer = 1) As String
Dim RowAbs As Boolean
Dim ColAbs As Boolean
Application.Volatile True
RowAbs = (RefType = 1) + (RefType = 2)
ColAbs = (RefType = 1) + (RefType = 3)
If TypeName(Selection) = "Range" Then
CELLADDRESS = Selection.Address(RowAbs, ColAbs)
Else
CELLADDRESS = ActiveCell.Address(RowAbs, ColAbs)
End If
End Function</pre>and enter it in a cell like this

=CELLADDRESS()

you can use the RefType parameter if you want to return the address with absolute or relative references (1 = both absolute, 2 = Row abs, col relative, 3 = row relative, col abs, 4 = both relative)

 

[Tim C]

This works great..

One thing though.. it's seems to take a little time.. it comes up with the answere real quick but pauses for 10 secs afterwards.. anyway to solve this?

 

[Tim C]

I'll try that Juan, thanks..

 

[Mudface]

On 2002-10-15 15:37, Tim C wrote:
This works great..

One thing though.. it's seems to take a little time.. it comes up with the answere real quick but pauses for 10 secs afterwards.. anyway to solve this?

Does your worksheet take a long time to recalculate without the code? If not, then I'm not sure what's going on. Juan's code looks good though...

 

[Noir]

Mudface,
Is there a way to have F3 update automatically as the cursor moves?

Noir

 

[Mudface]

Yes, use it in the Selection Change event: -
<pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("F3").Value = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)

End Sub
</pre>

 

[Tim C]

Yes... good question.. that would be even better to have the update happen as the highlighted cell changes.. is there a way?

 

[Mudface]

Note that this returns exactly the same as what appears in the Name box, so it's a bit redundant... .