PDA

View Full Version : Excel XP: auto-locating Active Cell



Tinakarori
27-05-2004, 11:09 AM
Does anyone know of an Excel function or formula that will dynamically return, ie track, the current "active cell" reference and display the location in another cell?

I am trying to set up an automated monthly processing statistics report, and think this information would simplify setting formulae to update to the current reporting month.

I know it is possible to display this information by use of a VB macro, but if possible I want to avoid use of macros.

All suggestions gratefully received!

Mrs Bump
28-05-2004, 03:37 PM
Bump

parry
28-05-2004, 07:10 PM
Hi, there is an old XL4 Macro called Active.Cell which is supposed to return a reference of the active cell, however I cant get it to work. See here (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q128185&ID=KB;EN-US;Q128185&FR=1) to download the XL4Macro Help file and maybe you can work it out.

In lieu of this, heres 2 options:-

1) Custom Function
Select Alt+F11 to enter the VBE then select Insert|Module then paste the following code in the right hand window. To use, simply enter the formula =Activecell(). As this is a formula it will only update when Excel calculates and selecting a cell does not cause a calculation in itself.


Function ActiveCell()
Application.Volatile
ActiveCell = Selection.Address
End Function

2) Worksheet Selection Change event
This is a more bullet proof option. This event runs whenever you select a cell so is custom made to fit your purpose. Replace the reference to cell A1 with the cell where you want to display the active cell address. To use this macro, right click the sheet concerned, select view code then paste in right hand window.


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

johnboy
28-05-2004, 07:11 PM
This is a good excel info sitemrexcel (http://www.mrexcel.com/board2/)

tinakarori
22-08-2005, 10:01 AM
Parry

Many thanks for your (as always) expert and useful advice, and my apologies for not responding at the time. I now have another need for this function/macro, vaguely remembered asking about it once before, and was able to locate this thread - thankfully, before posting the same query again!

BTW, what does "Bump" mean in this context, as posted by Mrs Bump in post #2 above, anyone?

Cheers

Tinakarori

Parry
23-08-2005, 04:42 AM
No problem. Bump is just a term used to indicate a thread hasnt received a reply. Whenever you make a post to a thread it gets moved (bumped) up the list of posts in the forum so theres a chance people may read the thread and respond.

tinakarori
23-08-2005, 11:02 PM
Parry

Today a colleague and I tried inserting both your sub routines as set out above, and neither of them would run - ie both returned VBA error messages. We are using Excel 2000 at work, just in case that makes a difference. Can you advise, please?

gibler
24-08-2005, 12:02 AM
Parry

Today a colleague and I tried inserting both your sub routines as set out above, and neither of them would run - ie both returned VBA error messages. We are using Excel 2000 at work, just in case that makes a difference. Can you advise, please?

Well the last one seems to work fine with Excel 2000 (service pack 3).
I added the routine to a sheet by:

1) Pressing ALT + F11 to bring up the Visual Basic Editor
2) Pasting in the code
3) ALT + Q to close the editor and return to Excel.

Parry
24-08-2005, 11:39 AM
Parry

Today a colleague and I tried inserting both your sub routines as set out above, and neither of them would run - ie both returned VBA error messages. We are using Excel 2000 at work, just in case that makes a difference. Can you advise, please?

Hi, could be a couple of things...

1) You have pasted the code in the wrong place.
Assuming your using the second piece of code (the Worksheet_SelectionChange event) then this must be saved in the sheet object in the VBE which you want to monitor. Right click the sheet tab concerned, select view code then paste in right hand window. This mewthod takes you straight to the Sheet object in the VBE as opposed to Alt+F11 where you will need to d-click the specific sheet in the left hand window.

If its the 1st macro (the function), this must be saved in a stand alone module. Select Alt+F11 to enter the VBE then Insert|Module and paste into the right hand window. You can now use the formula in a cell eg =Activecell() -note the empty brackets as this function has no arguments.

2) Excel macro security settings are preventing macros from running.
Select Tools|Macro|Security, select the Medium option then save the workbook and close Excel then restart Excel again. When you start Excel a prompt will appear for the workbook so ensure you select the Enable Macros button.

EDIT: What VBA error message? The code works fine on my machine at work running xl2000

Also ensure that there is only one version of these macros in your workbook. For the Worksheet_SelectionChange event there can only be one instance in any one sheet, so if you already have one of these events the code would need to be amalgamted with existing code. You would need to post the existing code for me to amend it for you.

tinakarori
16-09-2005, 01:36 PM
Hi Parry

Have been away on holiday (at last!), so have only just re-tried your VBA macro to auto-locate the active cell, with the help of your last post. It now works like a charm!

A couple of points re additional potentially useful features:

1. The macro works for every sheet in the workbook, ie the cell displaying the current active cell address provides the address including sheet tab name if the active cell is on any sheet other than the one with the display.

2. If you select a range of cells or a range of merged cells, the display shows the addresses of the full range, ie not just the top left cell.

Cheers

Tinakarori