Using VBA button to add a row with formatting

Question

Hello,

I have a project where I want the user to press a button, and it would add a row below the current one with the same formatting, including a drop down menu.

So the desired result would be:

User clicks on an add row button with OKCancel message.

The current row they are in would then be duplicated below, including the Add Row button and a drop down menu (if included in current row)

Any help would be greatly appreciated.

Thanks

Right now my script is:

Sub AddRow()MsgBox ("Select a cell in the row you want to add") If Result = vbYes Then Else End If ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove ActiveCell.EntireRow.Copy ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats Application.CutCopyMode = False End Sub

Answer 1

Here's a comprehensive guide on how to use a VBA button to add a row with formatting in Excel:

1. Create the VBA Code:

• Open the Visual Basic Editor (Alt+F11).
• Insert a new module (Insert > Module).
• Paste the following code into the module:

VBA

Sub AddFormattedRow()

    ' Determine where to insert the row
    Dim targetRow As Range
    Set targetRow = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Offset(1)

    ' Copy the formatting from the row above
    targetRow.EntireRow.Copy

    ' Insert a new row with the copied formatting
    targetRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    ' Clear any content in the new row, while preserving formulas
    targetRow.SpecialCells(xlCellTypeConstants).ClearContents

End Sub
Dont Forget Visit: https://here-to-care.com/

content_copy

2. Assign the Code to a Button:

• Right-click on a worksheet tab and choose "View Code."
• In the worksheet code module, paste the following:

VBA

Private Sub CommandButton1_Click()
    Call AddFormattedRow
End Sub

content_copy

• Adjust the button name (CommandButton1) if needed.

3. Add a Button to the Worksheet:

• Go to the Developer tab (if not visible, enable it in File > Options > Customize Ribbon).
• Click "Insert" and choose "Button" from the ActiveX Controls section.
• Draw the button on your worksheet.
• Right-click the button and choose "Properties."
• Set the button's Caption property to something descriptive (e.g., "Add Row").

4. Test the Button:

• Click the button. It should insert a new row with the formatting of the row above, leaving formulas intact.

Answer 2

I've done something wrong.

When I click the button, it clears all the text from the worksheet.

I copies/pasted your script, so I'm not sure how I messed it up.

Answer 3

Hi Amy

To solve your problem I took a different approach

1. I chose to make the dropdown list with the Data Validation figure rather than the dropdown from the Active X Controls.
2. I also chose to insert a Shape instead of an Active X button. Only one shape as I don't see the need to copy/add a button on each row.
3. The Model of Travel tab has the list for the dropdown

3)

Here is the working code

Sub AddRow()

Dim Answer As VbMsgBoxResult

'''' This will pop up a message for you to decide

Answer = MsgBox("Are you sure you want to Add a Row", vbYesNo, "ATTENTION")

If Answer = vbNo Then

    Exit Sub 

Else

With ActiveCell

    .EntireRow.Copy 

    .Offset(1).EntireRow.Insert Shift:=xlDown 

     Application.CutCopyMode = False 

    .Offset(1).EntireRow.ClearContents 

    .Offset(1).Select 

End With

End If

End Sub

Here is the sample file for you to download https://we.tl/t-vuU4QGczHd

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany