one click selection

C.S.

Board Regular
Joined
Nov 20, 2008
Messages
89
If I have a worksheet with a range of cells (ex. A1:G10) containing dates, is there any way I can setup the worksheet to return the value whatever cell I click on (one click or double click) to another specified cell? Example: Say I've designated F1 to be the cell I want the selected value to return to. I click on cell A1 (which contains 1/1/2010) it will return that value to cell F1.

Is this possible? I'm imagining something similar to a "little hand" selection icon.

Also, if so, can this be done without using macros. And if not, will the macro be usable in both excel 2003 and 2007?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
1. No, can't be done without Macros
2. Yes, this will work in 2003 and 2007

I designated J1.
It didn't make sense to me for the designated cell(F1) to be within the target range A1:G10

Right click the sheet tab, click View code
Paste the following

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:G10")) Is Nothing Then
    Range("J1").Value = Target.Value
End If
End Sub
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
    Cancel = True
    Target.Offset(, 5).Value = Target.Value
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,015
Messages
5,575,564
Members
412,676
Latest member
Davejf81
Top