How to return value of highlight cell?

[2L8IWON]

I think this is VERY simple, but can't seem to figure it out.


Let's say I have 40 cells with various values in them.


In another cell on the same sheet, I want to return the value of whatever cell is selected.


Ex. I put my cursor on cell B3, and in B3 it says "JACK" - in a designated cell in the spreadsheet, let's call it cell Z1, I want to return the value "JACK".


If I then put my cursor on cell D2, and it says "JILL", I want the same Z1 cell to return the value of "JILL", and so on.


In other words, I always want Z1 to display the contents of whichever cell is selected in a particular range.


I can't figure out if it is VBA activecell, and index formula, or something else. Help!?!?

 

[Luke M]

Right click on sheet tab, view code, paste this event macro in:

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyCell As Range

'Which cell do you want value to appear in?
Set MyCell = Range("Z1")

Application.EnableEvents = False
MyCell = ActiveCell.Value
Application.EnableEvents = True
End Sub

[/pre]

 

[2L8IWON]

Luke - thanks for the quick response. When I do this, nothing shows up in the cell (Z1 in your formula). Do I need to put a formula in the destination cell, like =MyCell() or something like that?


And would this automatically run each time I select a new cell, or would I need to run a macro?


Thanks again!

 

[Luke M]

Hmm. It should be running automatically each time a cell is selected, and it should not need a formula within the cell.


Are you sure the code is in the Sheet module, and not in a regular module?

Do you have a macro that might have turned EnableEvents to False?

 

[2L8IWON]

Luke - apologies, you're right. When I restarted, it appears to work now.


Do you know how I can add a 'range' to the formula? In other words, I only want the macro to run when I highlight cells A1:F99 or something of the like? That way if they click on a blank cell outside the range of options, the selection won't change?


Thanks much - I owe you big time!


Mike

 

[SirJB7]

Hi, 2L8IWON!


Try adding the following line after Dim statement:

-----

If Application.Intersect(Target, Range("A1:F99")) is Nothing then Exit Sub

-----


Regards!