Add Data to Any Sheet with User Form

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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

3 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.

Leave a Reply

Your email address will not be published. Required fields are marked *