Getting the name of highlithed cell

Tim C

New Member
Joined
Oct 7, 2002
Messages
42
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

New Member
Joined
Oct 7, 2002
Messages
42
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

New Member
Joined
Oct 7, 2002
Messages
42

ADVERTISEMENT

I'll try that Juan, thanks..
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

Active Member
Joined
Mar 24, 2002
Messages
362

ADVERTISEMENT

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

Noir
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

New Member
Joined
Oct 7, 2002
Messages
42
Yes... good question.. that would be even better to have the update happen as the highlighted cell changes.. is there a way?
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Note that this returns exactly the same as what appears in the Name box, so it's a bit redundant... :).
 

Forum statistics

Threads
1,144,743
Messages
5,726,022
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top