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

hedgeman50

Board Regular
Joined
Sep 20, 2002
Messages
76
How do I have the contents of a cell equal the contents of the currently active cell (i.e., the cell currently selected)? I have a long list of stock symbols (about 3,000) in column 3. I want to be able to simply hit the down arrow key to get to the next cell and have the cell K3 always have in it the contents of the active cell.

Example: If the active cell is A1 (and let's say that A1 has the symbol MSFT in it), then I want K3 to have MSFT in it. If I move to cell A6 (with the arrow key or with the mouse) and it has INTC in it, then I want K3 to have INTC in it.

Thank you very much for any help!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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
 

hedgeman50

Board Regular
Joined
Sep 20, 2002
Messages
76
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.
 

Paul B

Well-known Member
Joined
Feb 15, 2002
Messages
575
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
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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.

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:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
[K3] = ActiveCell
End Sub

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
 

hedgeman50

Board Regular
Joined
Sep 20, 2002
Messages
76

ADVERTISEMENT

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

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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.

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
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top