Double click cell to execute macro

[Guest]

Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting the
other rows down to avoid overwriting them). Is this possible to do?

 

[Chip Pearson]

Right click on the worksheet tab, choose View Code, and paste in the
following code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim RowNum As Long
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Column <> 1 Then
Exit Sub
End If
Me.Rows(Target.Row + 1).Insert
Me.Rows(Target.Row).Resize(2).FillDown
Cancel = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Zone]

Right-click on the sheet's tab and select View Code. Copy this code and
paste it in there.
James

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Copy
Rows(Target.Row + 1).Insert
Application.CutCopyMode = False
Cancel = True
End Sub

 

[Guest]

Hi,

Try this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Target.EntireRow.Select
Selection.Copy
Target.Offset(1, 0).Select
Selection.Insert Shift:=xlDown
End If
End Sub

Right click sheet tab|view code and paste in

Mike

 

[Guest]

here's one way:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim lLastRow As Long
If Target.Column = 1 Then
lLastRow = Range("A65535").End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=Range("A" & lLastRow)
Cancel = True
End If
End Sub

 

[Guest]

correction. I missed that you wanted to copy it to the next row and shift
everything down.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column = 1 Then
Target.Offset(1).EntireRow.Rows.Insert
Target.EntireRow.Copy Destination:=Target.Offset(1)
Cancel = True
End If
End Sub

 

[Guest]

I have tried each of the suggested methods, however, when I double click the
cells in column A, it throws me into the standard cell edit mode (it assumes
I want to edit the cell contents.) Am I missing something?

 

[Guest]

did you omit the line that says "Cancel = True" ?

 

[Guest]

I don't know about the code sent by others but with mine if you right click
the sheet tab, view code and paste it in then it does what you require.

Mike

 

[Guest]

I copied the code exactly. Could the problem be that I have text in that
cell? I have the words "dbl-click" in each cell of column A to remind the
user that they are supposed to double-click the cell to replicate the row.
Here is an example of what I'm talking about:

A B C
1 (Header) (Header) (Header)
2 dbl-click Mr. Jones 1239
3 dbl-click Mrs. Smith 45678

 

[Guest]

you also need to make sure you paste the code in the worksheet code
module...follow the instructions given by Zone and Mike H.

 

[Zone]

The only other thing is that events may be turned off. To turn them on,
with the code editor on the screen, press Ctrl-g to open the immediate pane.
Type in the immediate pane
application.enableevents=true
and press Enter, then try again. James

 

[Guest]

I just realized ... When I right-clicked the sheet tab to View Code, then did
the copy/paste, I didn't realize that the drop-down at the top of the VB
module was set to "General" vs. "Worksheet". I changed the selector to
"Worksheet", then recopied the code & everything worked fine. Thanks for all
your help!!