Copy to Specific Cell on Double click (Excel 2003)

Topic

New Reply

I am looking for some help on a specific worksheet.
I would like to be able to double click on a cell ( that has data in it ) and when I do so, the contents of the cell double clicked is copied to cell E2
Then be able to double click on another cell and have that cell’s contents copied to cell E2
The Code I’m working with is :
Sub Worksheet_AfterDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rInt As Range
Dim rCell As Range
Set rInt =Intersect( Target, Range(“C3:P312”))
Set rCell = Range (“E2”)
If Not rInt Is Nothing Then
rInt.Value = rCell
End If
Cancel = True
End Sub

Reply | Quote

Replies

Double-clicking a cell is used to edit the cell’s formula; I wouldn’t use it for other purposes. But if you really want to:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Range(“E2”), ActiveCell) Is Nothing Then
If Not ActiveCell = “” Then
Application.EnableEvents = False
Range(“E2”) = ActiveCell
Application.EnableEvents = True
Cancel = True
End If
End If
End Sub

Reply | Quote

Thanks Hans. Excellent as always
Question.
How does the Application.EnableEvents = False affect the program?

Reply | Quote

The line Application.EnableEvents = False suspends the processing of other events that might be fired as a consequence of changing the value of E2. It’s a precaution against generating an endless cascade of events. Event processing is turned on again immediately after setting the value of E2.

Reply | Quote

Hans

Thanks for the education

Reply | Quote