Command Button to clear form and add more rows in Excel

[aaronmendes]

Hi,

I have assigned a code in Userform for the command button to update data as per the input in the Textboxes in Excel.

However, after clicking OK (command button caption name), I want the data to be cleared in the form and also after inputting more data in the form, the OK button should add additional rows in excel.

My code is below

Private Sub OK1_Click()


Dim emptyRow As Long

'Make Sheet1 Active
Sheets(1).Activate

'Determine EmptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Export Data to worksheet
Cells(emptyRow, 5).Value = Trades_Asset.Value
Cells(emptyRow, 6).Value = DisputeReason.Value
Cells(emptyRow, 7).Value = CartNo.Value
Cells(emptyRow, 8).Value = CallTrack.Value
Cells(emptyRow, 9).Value = MOcontact.Value
Cells(emptyRow, 10).Value = Misc_comments.Value


End Sub

 

[dmt32]

Not tested & intended only as a possible approach you could take. See if following code helps:

Private Sub OK1_Click()
    Dim emptyRow As Long
    Dim arr As Variant
    arr = Array("Trades_Asset", _
                "DisputeReason", _
                "CartNo", _
                "CallTrack", _
                "MOcontact", _
                "Misc_comments")
    'Make Sheet1 Active
    With Sheets(1)
        .Activate
        'Determine EmptyRow
        emptyRow = WorksheetFunction.CountA(.Range("A:A")) + 1
        'Export Data to worksheet
        For i = 0 To 5
            .Cells(emptyRow, i + 5).Value = Me.Controls(arr(i)).Text
            'clear textboxes
            Me.Controls(arr(i)).Text = ""
        Next i
        'insert 6 rows
        .Range(.Cells(emptyRow + 1, 1), .Cells(emptyRow + 6, 1)).EntireRow.Insert
    End With
End Sub

You will need to adust the range & number of rows you want to insert but code should give some direction for you. Also, note that I have qualified the ranges to your worksheet. Even though you have activated it, its always good practice to do this to ensure that your data does go where intended.

Hope helpful

Dave.