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
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.
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.
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.
very nice tutorial …
very help full for me ..
but Dear Sir I need it in excel formate……
ur explanation good,
thank u so much
if it is possible make video on student progress card /all classes/all exams/update /retrieve/customise
Hi, with the ‘Add Data To Any Sheet With User Form’, how do you define ‘workSheet’? thanks