Command Button to clear form and add more rows in Excel

aaronmendes

New Member
Joined
Jul 1, 2013
Messages
3
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

Code:
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,825
Office Version
2019
Platform
Windows
Not tested & intended only as a possible approach you could take. See if following code helps:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,509
Messages
5,469,038
Members
406,629
Latest member
Alan365

This Week's Hot Topics

Top