Formula that returns address of the active cell.

[Derek]

Hi Guys and Gals

Is there a formula in Excel that returns the active cell address (ie dynamically). Excel updates the activecell address in the Name Box dynamically as you make a selection but I cannot find a standard formula to access it. I know I can achieve this with code using the selection-change event but this action then disallows use of the Undo button - which I specifically want to avoid. Perhaps there is an add-in available??

thanks for any good ideas

Regards

Derek

 

[Richard Schollar]

Mark

That certainly seems to work to me! Still allows Undo.

 

[Peter_SSs]

Looks like a good addition to me.

 

[davepolacheck]

This is helpful: =CELL("address"). I have a similar but different need... Conceptually I'm looking for excel to take action based on which cell a user selects, and I have a preference for formulas over VBA. Using name manager I named "Active" to refer to =CELL("address"). Then I referenced "=Active" in the cell that will trigger the action. F9 will update this cell with a reference to the active cell.

 

[ParaMigi]

Howdy to all,

I'm probably missing something, but never minded a friendly jab so will ask. If the formula is better for undo, and only calculation seems problematic, would a forced calc be disadvantageous?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    [A1].Calculate
End Sub

Thanks,

Mark

Thank you so much Mark, this worked perfectly for my application! I put =CELL("address") in cell A1, and then wrote your code into the VBA, and now I can Undo!