Formula to update cell w/ActiveCell.Value?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

Can I put a formula in a certain cell that will, on recalculation, update
that cell with the value of the current ActiveCell?

Ed
 
Ed,

Don't think so. But this sub will do it. Paste it into the sheet module.
Change the location as necessary.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False ' prevent retriggering
Range("A1") = ActiveCell.Text
Application.EnableEvents = True

End Sub
 
Hi Ed

You could try the Workbook_SheetCalculate

Try this. Right click on the Excel icon, top left next to "File" and in
here paste the code below

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Application.EnableEvents = False
Sheets(1).Range("A1") = ActiveCell
Application.EnableEvents = True
End Sub

Change Sheets(1) and Range("A1") to suit.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
Dave and Earl - Thanks for the suggestions, but I'm trying to get away from
code (there are threads all over the Excel.programming and vb.general
discussion NG about my recent code difficulties due to an "upgrade"!). I'm
automating Excel from a Visual Basic application, and I need to be able to
have VB get the ActiveCell value when a button is clicked on the VB form.
But VB doesn't accept the ActiveCell parameter (as far as I can tell - I
have a thread exploring this option on the vb.general discussion NG). And
since I don't know which cell the user will have active when the VB button
is clicked, I can't code in a specific cell reference.

If, though, I can have a specific cell with a formula that will grab the
value of whatever cell is active, then I can have my VB sub call
workbook.calculate to update the formula and read that particular cell. Uh,
that is - *IF* my idea has any basis in the real world! If not, well, it
was an idea anyway ... 8>/

Ed
 
Back
Top