Double Click to Insert New Row - Please Help!!!

[JohnHIII]

Hello,

I posted this question yesterday. 48 views and 3 visits later still no replies. So I figured I'd re-post with a few illustrations to see if it helps. Please don't think that I'm impatient, just on a deadline for work. And any assistance would be very much appreciated.

I want a row to be automatically added below the target cell when I double click a cell in Column B only. I want all the other cells outside of Column B to behave as normal when they are double clicked.

Please see the illustrations attached for further clarification.

This is prior to the double clicking:
Picture1.png


This is what I want to happen when I double click ONLY on a name contained in Column B (in this example I clicked on Ben Rivera in B14):
Picture2.png

With the script I'm using, it does what I want it to:

• Drops the name I click on into the new row automatically.
• Keeps the formulas from the cells in the copied row.
• Clears everything else out of the cells in the new row.

Again, I only want this to happen when a name in Column B is double clicked. Unfortunately, as it works now, this happens no matter what cell in the row I double click. This makes for a real pain any time I inadvertently double click into a cell to add data, edit, etc. And I know my co-workers aren't going to have the patience for that little detail either.

This is the script I cut and pasted form Excel's online help:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.EntireRow.Copy
Cells(Target.Row + 1, 1).EntireRow.Insert
Cells(Target.Row + 1, 1).EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
On Error Resume Next
'-- customize range for what cells constants can be removed --
Intersect(Selection, Range("c:IV")).SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub


Like I said, it's almost perfect but not quite there. Please reply with any help you can offer or any questions I can answer for you to assist.

Just please keep in mind, like I said, I cut and pasted this...and for good reason. I am very, very much a novice at this. With a little common sense I can pick out key elements and understand what they do, but that's as far as it goes for right now. So if you reply, try to put everything into "For Dummies" terms

Thanks for looking and helping if you can!!!

- John

[Norie]

John

You need to use code tags when posting code, kind of a rule.

Also, attaching a workbook is probably better than pictures.

If the only problem is that the code is running whatever column you click then you need to add a check to see which column the cell that has been double clicked, which is Target, is in.

[JohnHIII]

Norie,

Thank you for the reply. Apologies for not using code tags in my original post....new to the forum and didn't know. Here is the code I'm using with the code tags:

Please Login or Register  to view this content.

Also, as I stated, I have no idea how to write my own code. I've just been scouring the net finding things that work for my purposes and pasting them into my spreadsheet. Therefore, I don't know how to add a check to which column the cell has been double clicked as you suggested. My hope was somebody on here could fix up the code I'm currently using so I could simply paste it into my spreadsheet(s). Not trying to sound lazy, but just pressed for time. I have been trying to learn what I'm looking at as I go, but need some assistance in the meantime.

Lastly, per your suggestion, I have attached a sample of the workbook I am working on.

Thanks again for your earlier reply. Anything more assistance would be greatly appreciated.

-John

[jaslake]

Hi JohnHIII

Try this

Please Login or Register  to view this content.

[JohnHIII]

Jaslake - It works like a charm. Thanks a million!!! I am setting this to SOLVED now. Thanks again.

[jaslake]

You're welcome...glad I could help.