I want to have the contents of one cell equal active cell

Try this in your worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$K$3" Then
Target.Value = ""
Else
[K3] = ActiveCell
End If
End Sub

I tried this, but I can't seem to make it work. Perhaps I'm doing something incorrectly. Can you suggest a step-by-step guide? Thanks.

I tried toms code, it works for me, I am using excel’97. You will need to put it in the worksheet code, right click on the worksheet tab, view code and paste it in the window that opens then go back to the worksheet and it should work. Hope this helps

On 2002-09-21 18:52, hedgeman50 wrote:
I tried this, but I can't seem to make it work. Perhaps I'm doing something incorrectly. Can you suggest a step-by-step guide? Thanks.

Click to expand...

Hi hedgeman50:

I don't see Tom around ... so let us see if I can help

1. You are creating a SUB in a worksheet for a WorkSheet_SelectionChange event

2. I am going to simplify the code a little (Tom Urtis may have built in some safeguards -- I am going to ignore those statements for now). We want to write code so that the cell K3 takes up the value of the ActiveCell (selection at any given moment)... and that code is:

[k3]=ActiveCell

3. So my simple SUB (without the safeguard features that Tom Urtis may have intended to build in) is:


4. You insert this code in the VBEditor in your worksheet by right clicking on the worksheet tab, then click on View Code

5. The system will take you to the VBEditor -- if you don't see the VBEditor window, click on VIEW ... then select CODE. You will see the following two lines inserted by the system:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

These two are the beginning and ending of the subroutine -- we provide the following code line between the beginning and the ending lines of the SUB

[K3]=ActiveCell

so our complete SUB looks like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[K3] = ActiveCell
End Sub

6. Now return to the worksheet, and say you select cell c5, and say cell c5 has a value 28 in it ... then K3 will take the value of the selection i.e. 28;

If you move to to cell D7 (say with a value of 32), K3 will become 32, ...


I hope this helps.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-09-21 19:42

Success! Thank you very much to both of you (Tom and Paul).

Max

Thanks to you too, Yogi. I really appreciate all the help. I owe you.

Thanks for following up guys, while I was watching all the college football action today.

Yogi --
I originally was about to post the abbreviated version of the code you posted but (and this is totally a personal preference) when cell K3 is selected, it is then the active cell, sort of like looking in a mirror, so it should not (and cannot by definition) display any other active cell's value, hence it displays nothing. Anyway that was my logic; yours works too.

Thanks again everyone.

On 2002-09-21 19:52, Tom Urtis wrote:
Thanks for following up guys, while I was watching all the college football action today.

Yogi --
I originally was about to post the abbreviated version of the code you posted but (and this is totally a personal preference) when cell K3 is selected, it is then the active cell, sort of like looking in a mirror, so it should not (and cannot by definition) display any other active cell's value, hence it displays nothing. Anyway that was my logic; yours works too.

Thanks again everyone.

Click to expand...

Tom:

I agree with you about building in the safeguard regarding not being able to select K3 to assign it the value of K3. I was thinking of this being the same situation of circular reference in the worksheet environment!

However I do have a question for you -- in

If Target.Address = "$K$3" Then
Target.Value = ""

should K3 being referred to herein be considered the Target.Address or the Active.Cell

VBA did not cry foul on my trying both scenarios

Any thoughts on this?

Regards!

Yogi

Yogi --

I guess either case would pass muster with VBA, but because the standard worksheet arguments for SelectionChange invoke the Target reference, I used that to describe the possibility of K3 being selected.

I did not (but we could) disallow the user from selecting K3 altogether and redirecting him/her to a different cell, such as:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$K$3" Then
MsgBox "Cannot select K3.", 16, "Sorry, K3 is off limits."
Range("A1").Select
Else
[K3] = ActiveCell
End If
End Sub

And maybe if the hedgeman50 is still reading this he might want to use it, if he doesn't want K3 to even be selected.

Whatever the case, there was no danger of a circular reference given the code appraoch we were using.

Not sure if this answers your question, but at least all's well that ends well.