If cell contains specific text, and is double clicked, activate macro

[pontiff]

Hi, this might sound a bit unusual but here goes... could vba be used to do the following?
If a cell on the sheet contains the text “Total”, when double clicked...
1 Copy the row above and paste it one row up
2. Insert new row below newly pasted row.

Thanks in advance.

 

[ranman256]

there is not DBL-CLICK event for cells.
put the macro on the quick access toolbar, and it can run the current row the cursor is on.

 

[JoeMo]

Does this do what you want?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If InStr(1, Target.Value, "Total") > 0 Then
    If Target.Row > 2 Then
        Application.EnableEvents = False
        With Target.Offset(-1, 0).EntireRow
            .Copy Destination:=Target.Offset(-2, 0).EntireRow
            .Insert
        End With
        Application.EnableEvents = True
    End If
End If
End Sub

 

[pontiff]

Does this do what you want?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If InStr(1, Target.Value, "Total") > 0 Then
    If Target.Row > 2 Then
        Application.EnableEvents = False
        With Target.Offset(-1, 0).EntireRow
            .Copy Destination:=Target.Offset(-2, 0).EntireRow
            .Insert
        End With
        Application.EnableEvents = True
    End If
End If
End Sub

Thank for this, I’ll try it first thing tomorrow and let you know. Much appreciated!

 

[kweaver]

What version of Excel do you run? Please update your profile.

 

[pontiff]

What version of Excel do you run? Please update your profile.

2016

 

[pontiff]

Thank for this, I’ll try it first thing tomorrow and let you know. Much appreciated!

Apologies, I thought I’d replied to this. It works great but due to a change in my design I was wondering if it can select the first 9 cells of the row rather than the entire row and do the same job. Thanks for posting, much appreciated.

 

[JoeMo]

Apologies, I thought I’d replied to this. It works great but due to a change in my design I was wondering if it can select the first 9 cells of the row rather than the entire row and do the same job. Thanks for posting, much appreciated.

I assume you mean limit the copy/paste and insertion of a new row to the first 9 columns of any data row. Below assumes that the first 9 columns are columns A:I.

Replace the existing code with this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column > 9 Then Exit Sub
If InStr(1, Target.Value, "Total") > 0 Then
    If Target.Row > 2 Then
        Application.EnableEvents = False
        With Target.Offset(-1, -Target.Column + 1).Resize(1, 9)
            .Copy Destination:=Target.Offset(-2, -Target.Column + 1)
            .Insert
        End With
        Application.EnableEvents = True
    End If
End If
End Sub

 

[pontiff]

I assume you mean limit the copy/paste and insertion of a new row to the first 9 columns of any data row. Below assumes that the first 9 columns are columns A:I.

Replace the existing code with this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column > 9 Then Exit Sub
If InStr(1, Target.Value, "Total") > 0 Then
    If Target.Row > 2 Then
        Application.EnableEvents = False
        With Target.Offset(-1, -Target.Column + 1).Resize(1, 9)
            .Copy Destination:=Target.Offset(-2, -Target.Column + 1)
            .Insert
        End With
        Application.EnableEvents = True
    End If
End If
End Sub

Thanks so much for this, very much appreciate all the help I get and how much I've learnt from this