Require the code for Add Command using VBA

I am preparing Form in excel using VBA.

I am trying to code a add command, but I am not able to figure it out.

here is the code I prepare,

Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim Row As Long
Dim ws As Worksheet
Set ws = Worksheets("DPDIAdhocRequestUserForm")
1 Row = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(A1, 1).Value = Me.RequesterName.Value
.Cells(B1, 2).Value = Me.RequesterPhoneNumber.Value
.Cells(C1, 3).Value = Me.RequesterBureau.Value
.Cells(D1, 4).Value = Me.ChosenDate1.Value
.Cells(E1, 5).Value = Me.ChoosenDate2.Value
.Cells(F1, 6).Value = Me.PurposeofRequest.Value
.Cells(G1, 7).Value = Me.ExpectedDataSaurce.Value
.Cells(H1, 8).Value = Me.Timeperiodofdatarequested.Value
.Cells(I1, 9).Value = Me.ReoccurringRequest.Value
.Cells(J1, 10).Value = Me.RequestNumber.Value
.Cells(K1, 11).Value = Me.AnalystAssigned.Value
.Cells(L1, 12).Value = Me.ChoosenDate3.Value
.Cells(M1, 13).Value = Me.ChoosenDate4.Value
.Cells(N1, 14).Value = Me.SupervisiorName.Value
End With
End Sub

but when I am trying to add data to my excel sheet using ADD command , it doesn’t work.

can you please guide me with this?

thank you so much for everything,,,

Participant Asked on April 1, 2017 in VBA: User Forms.
Add Comment
3 Answer(s)

HI,

 

Please find the below solution for your requirement.

Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim Row As Long
Dim ws As Worksheet
Set ws = Worksheets("DPDIAdhocRequestUserForm")
Row = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(1, 1).Value = Me.RequesterName.Value
.Cells(1, 2).Value = Me.RequesterPhoneNumber.Value
.Cells(1, 3).Value = Me.RequesterBureau.Value
.Cells(1, 4).Value = Me.ChosenDate1.Value
.Cells(1, 5).Value = Me.ChoosenDate2.Value
.Cells(1, 6).Value = Me.PurposeofRequest.Value
.Cells(1, 7).Value = Me.ExpectedDataSaurce.Value
.Cells(1, 8).Value = Me.Timeperiodofdatarequested.Value
.Cells(1, 9).Value = Me.ReoccurringRequest.Value
.Cells(1, 10).Value = Me.RequestNumber.Value
.Cells(1, 11).Value = Me.AnalystAssigned.Value
.Cells(1, 12).Value = Me.ChoosenDate3.Value
.Cells(1, 13).Value = Me.ChoosenDate4.Value
.Cells(1, 14).Value = Me.SupervisiorName.Value
End With
End Sub

 

or

You can also specify range instead of cells(1,1) with Range(“A1”) like this.

Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim Row As Long
Dim ws As Worksheet
Set ws = Worksheets("DPDIAdhocRequestUserForm")
Row = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Range("A1").Value = Me.RequesterName.Value
.Range("B1").Value = Me.RequesterPhoneNumber.Value
.Range("C1").Value = Me.RequesterBureau.Value
.Range("D1").Value = Me.ChosenDate1.Value
.Range("E1").Value = Me.ChoosenDate2.Value
.Range("F1").Value = Me.PurposeofRequest.Value
.Range("G1").Value = Me.ExpectedDataSaurce.Value
.Range("H1").Value = Me.Timeperiodofdatarequested.Value
.Range("I1").Value = Me.ReoccurringRequest.Value
.Range("J1").Value = Me.RequestNumber.Value
.Range("K1").Value = Me.AnalystAssigned.Value
.Range("L1").Value = Me.ChoosenDate3.Value
.Range("M1").Value = Me.ChoosenDate4.Value
.Range("N1").Value = Me.SupervisiorName.Value
End With
End Sub

 

Hope this clarifies your requirement.

Regards!

Expert Answered on April 1, 2017.
Add Comment

HI,

Please find more examples on Userform controls and explained with examples.

You can also find sample projects using Userform Projects

Regards!

Expert Answered on April 1, 2017.
Add Comment

and also we can use it below code ..

Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim Row As Long
Dim ws As Worksheet
Set ws = Worksheets("DPDIAdhocRequestUserForm")
Row = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(1, "A").Value = Me.RequesterName.Value
.Cells(1, "B").Value = Me.RequesterPhoneNumber.Value
.Cells(1, "C").Value = Me.RequesterBureau.Value
.Cells(1, "D").Value = Me.ChosenDate1.Value
.Cells(1, "E").Value = Me.ChoosenDate2.Value
.Cells(1, "F").Value = Me.PurposeofRequest.Value
.Cells(1, "G").Value = Me.ExpectedDataSaurce.Value
.Cells(1, "H").Value = Me.Timeperiodofdatarequested.Value
.Cells(1, "I").Value = Me.ReoccurringRequest.Value
.Cells(1, "J").Value = Me.RequestNumber.Value
.Cells(1, "K").Value = Me.AnalystAssigned.Value
.Cells(1, "L").Value = Me.ChoosenDate3.Value
.Cells(1, "M").Value = Me.ChoosenDate4.Value
.Cells(1, "N").Value = Me.SupervisiorName.Value
End With
End Sub

 

Regards

Srinivas

 

Top Contributor Answered on April 3, 2017.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.