• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Participant

      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,,,

      Asked by DollyKakkad on April 1, 2017 in VBA: User Forms.
      3 Answers
      Expert

      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!

      Answered by Valli on April 1, 2017..
      Expert

      HI,

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

      You can also find sample projects using Userform Projects

      Regards!

      Answered by Valli on April 1, 2017..
      Top Contributor

      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

       

      Answered by YSR on April 3, 2017..