September 14, 2017

Add Data to Any Sheet with User Form

Posted on by Dinesh Kumar Takyar

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

6 thoughts on “Add Data to Any Sheet with User Form”

  1. how to write excel vba code to add data automatic with userforms to any sheet by date.
    example:day 1/1/2018 to sheet1, 2/1/2018 to sheet2,…

    please, create your video lessons on youtube. i like your channel so much.
    thank you.

  2. how to write excel vba code to add data automatic with userforms to any sheet by with the specific sheet names. Like i want to add my daily sales report month wise have total 12 worksheets Like JAN-18, FEB-18, MAR-18 & APR-18 wise. have given the coding as follow

    Private Sub CommandButton1_Click()
    whichSheet = InputBox(“Select the sheet? Specify sheet Name only.”, “Sheet Name”)
    If whichSheet = “” Then
    MsgBox “Please Specify a Sheet!”
    Exit Sub
    End If

    Worksheets(whichSheet).Active

    but got runtime error 438
    if do debug it is highlighying this line Worksheets(whichSheet).Active

    can you please help to solve this.

  3. If you need your youngsters to learn the way to talk French
    it’s significantly better to expose them to the language as typically
    as potential https://math-problem-solver.com/ . So to speak,
    one in every of the problems that are generally met by students
    in school are their disabilities.

  4. very nice tutorial …
    very help full for me ..
    but Dear Sir I need it in excel formate……

  5. ur explanation good,
    thank u so much
    if it is possible make video on student progress card /all classes/all exams/update /retrieve/customise

  6. Hi, with the ‘Add Data To Any Sheet With User Form’, how do you define ‘workSheet’? thanks

Comments are closed.