Insert row on double click

[adam]

Hi anyone,

How could I make the code to insert rows when the column "F" is double clicked and copy contents from Columns C,D,& Q. My data in column F starts from column F18.

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim LR As Integer
    If Target.Column > 6 Then Exit Sub
    Cancel = True
    
        Target.Offset(1, 0).Rows("1:1").EntireRow.Insert Shift:=xlDown
        Target.Resize(1, 9).AutoFill Destination:=Range(Target, Target.Offset(1, 8)), Type:=xlFillDefault
        Target.Offset(1, 3).Resize(6, 2).ClearContents
    LR = Empty
End Sub

Any help on this would be kindly appreciated.

[sdckapr]

Could you elaborate on exactly what you want done when a cell in column F is doubleclicked?
Where do you want to insert the rows and How many rows do you want to insert?
Which rows from the columns C, D, and Q do you want to copy and where do you want to copy them to?

Do you want this to occur at all rows in col F or only particular rows?

Steve

[adam]

Thanks for the reply Steve.
I want the code to insert a row when a cell in column F is doubleclicked.
I want to insert the rows below the double clicked cell in column F and I want to insert a single row on each double click.
I want the contents of the columns C, D, and Q to get copied to the inserted row.

I want this to occur to all the rows that is being inserted. Meaning the same to happen each time the user double clicks a cell in column F.

Thanks in advance.

[sdckapr]

If I understand what you ask, how about this?

Steve

Code: Select all

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim lRow As Long
  If Not Intersect(Target, Columns("F")) Is Nothing Then
    lRow = Target.Row
    Rows(lRow + 1).EntireRow.Insert
    Range(Cells(lRow, 3), Cells(lRow, 4)).Copy _
      Cells(lRow + 1, 3)
    Cells(lRow, 17).Copy Cells(lRow + 1, 17)
    Application.CutCopyMode = False
    Cancel = True
  End If
End Sub

[adam]

Yes Steve. That was what I had asked for.

How should I incorporate the following code to your code. Any help would be kindly appreciated. Thanks in advance.

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Application.ScreenUpdating = False
   If Target.Address(False, False) = "I10" Then
      frmAddNew.Show
  End If
    If Not Intersect(Target, Range("O9:O9")) Is Nothing Then
        Cancel = True
        If Target.Value = "OLD" Then
            Target.Value = "NEW"
            
        Else
            Target.Value = "OLD"
        End If
    End If
        
        If Not Intersect(Target, Range("I9:I9")) Is Nothing Then
        Cancel = True
        If Target.Value = "Discount" Then
            Target.Value = "Normal"
            
        Else
            Target.Value = "Discount"
        End If
    End If
    
    If Not Intersect(Target, Range("O10:O10")) Is Nothing Then
        Cancel = True
        If Target.Value = "Yes" Then
            Target.Value = "No"
            
        Else
            Target.Value = "Yes"
        End If
   End If
   Application.ScreenUpdating = True
   End Sub

[sdckapr]

Why can't you just add it as another dbl-click range to check?

You already have code to check for I10, O9, I9, and O10. You are just adding code to check for Col F as well.

Note: it is very bad form (and can lead to debugging issues) to just blindly ignore all errors with the line:
On Error Resume Next

This LOC should only be used sparingly when you absolutely can't avoid an error happening. But it should be used and then put errors back on.

Steve

[adam]

Thanks for the recommendation Steve. So you mean I have to remove the line On Error Resume Next from the code?

[sdckapr]

You don't have to, but I recommend it. With the code there all errors at runtime will be ignored which often lead to odd results in logic as variables may not be set or even items cleared and deleted that are not intended.

Why do you want to ignore the errors?

Steve

[adam]

Since I do respect your recommendation; I would remove the line "On Error Resume Next". I guess that would be fine.

Am I right. Steve?