Insert Row on Button Click

Question

I have the following code, which I got from this community, which allows me to double click on a cell and insert a row below. It inserts the row then goes back to columns E through R, as well as column T, and clears contents.

All I want to do is take the same code and apply it to a button click instead of a double click (people here at work use double click to edit the cell).

Here's the double-click code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
     Intersect(Target.Offset(1).EntireRow, Range("E:R,T:T")).ClearContents
End Sub

I tried several other codes (found on StackOverflow) that I was just going to modify to go back and ClearContents, but I keep getting errors.

Two other codes I've tried are:

Public Sub insertRowBelow()
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

and

Sub InsertRow()
r = Selection.Row
Cells(r + 1, 1).EntireRow.Insert
Cells(r, 1).Copy Destination:=Cells(r + 1, 1)
End Sub

On both, I get "Variable not defined" errors:

The end goal is for the user to click on a cell, click the button, it copies down the formulas from the line above, and then ClearContents in columns E through R, as well as column T (for only that newly inserted row, not ClearContents for the entire column!)

BONUS COOL POINTS: if you're able to make it so they can't add a row if they are above row 5, that'd be fantastic. Rows 1, 2, 3, and 4, are all header / information type rows, so if they click in any of those (or don't know the active cell is up there) and click this button, since you can't undo VBA, it could mess up the header section pretty good. So they would have to activate a cell in row 5 or below for this to work, that would be the icing on the cake.

Triple cool points if we can add a msg box that says "Please select a cell in row 5 or below" so they don't just think the button's broken... that would be the best of the best of the best.

Thanks for any help in advance!!

It's either xlFormatFromLeftOrAbove or xlFormatFromRightOrBelow - documentation. And I'm assuming you have Option Explicit at the top of your module (which is good), you must declare your variable before using it so inserting Dim r As Long before r = Selection.Row should resolved it for your 2nd code. — Raymond Wu
– Raymond Wu, Commented Nov 4, 2021 at 15:11
And add a If statement checking if the selected row is < 5, if true then [insert row code block...], else display a MsgBox with whatever you want to say. — Raymond Wu
– Raymond Wu, Commented Nov 4, 2021 at 15:16

VBasic2008 · Accepted Answer · 2021-11-05 03:41:11Z

1

Insert a Row Below

Option Explicit

Sub InsertRowBelow()
    Const ProcTitle As String = "Insert a Row Below"
    
    Const msgRange As String = "Please select a cell."
    Const msgRowMin As String = "Please select a cell below row 4."
    Const msgRowMax As String = "Please select a cell above the last row."
    
    On Error GoTo ClearError ' e.g. worksheet is protected
    
    If TypeName(Selection) = "Range" Then
        With Selection.Cells(1).EntireRow ' consider only the first cell's row
            If .Row > 4 Then
                If .Row < .Worksheet.Rows.Count Then
                    .Copy
                    .Offset(1).Insert xlShiftDown ' , xlFormatFromLeftOrAbove
                    Intersect(.Offset(1), .Worksheet.Range("E:R,T:T")) _
                        .ClearContents ' sets 'Application.CutCopyMode' to False
                Else ' '.Row = .Worksheet.Rows.Count' (last Row)
                    MsgBox msgRowMax, vbCritical, ProcTitle
                End If
            Else ' '.Row <= 4'
                MsgBox msgRowMin, vbCritical, ProcTitle
            End If
        End With
    Else ' 'Selection' is not a range (e.g. it's a shape)
        MsgBox msgRange, vbCritical, ProcTitle
    End If

ProcExit:
    Exit Sub
ClearError:
    MsgBox "Unexpected Error" & vbLf & vbLf _
        & "Run-time error '" & Err.Number & "':" & vbLf _
        & Err.Description, vbCritical, ProcTitle
    'If Application.CutCopyMode Then Application.CutCopyMode = False
    Resume ProcExit
End Sub

answered Nov 5, 2021 at 3:41

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

WaveWalker116 Over a year ago

you win all the cool points! Works perfectly. Thank you!!!

WaveWalker116 Over a year ago

Oh, and thanks for the explanations in the code... definitely helps me learn. Thanks again!

Collectives™ on Stack Overflow

Insert Row on Button Click

1 Answer 1

Insert a Row Below

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Insert a Row Below

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related