How to add data to any specific sheet with a user form using VBA. I’m sure you can see the power of being able to transfer data to any sheet via the user-form because you may be able to use a single form to populate data into different worksheets as in accounting. Expenses can be posted to the expense worksheet and income data can be transferred to the income worksheet.

Watch the video below:

Watch this video on YouTube.

Here’s the complete VBA code:

Private Sub CommandButton1_Click()
whichSheet = InputBox(“In which sheet do you wish to enter data? Specify sheet number as Sheet1, Sheet2 & Sheet3 only.”, “Sheet Number”)
If whichSheet = “” Then
MsgBox “You didn’t specify a sheet!”
Exit Sub
End If

Worksheets(whichSheet).Activate
Dim lastrow
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

lastrow = lastrow + 1
Cells(lastrow, 1) = TextBox1
If Application.WorksheetFunction.CountIf(Range(“A2:A” & lastrow), Cells(lastrow, 1)) > 1 Then
MsgBox “Duplicate data! Only unique IDs allowed”, vbCritical, “Remove Data”
Cells(lastrow, 1) = “”
ElseIf Application.WorksheetFunction.CountIf(Range(“A2:A” & lastrow), Cells(lastrow, 1)) = 1 Then
answer = MsgBox(“Are you sure you want to add the record?”, vbYesNo + vbQuestion, “Add Record”)
If answer = vbYes Then
Cells(lastrow, 1) = TextBox1.Text
Cells(lastrow, 2) = TextBox2.Text
Cells(lastrow, 3) = TextBox3.Value
End If
End If

 

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Further reading:

Add Data to Excel Database with UserForm using Countif Function