next empty row

[ashukla1]

I have created a userform and i am able get the form to transfer data in the sheet i require.
however i am unable to move to the next row once i've used the first row

I have 4 text boxes 1 Combobox and 1 list box

Please help me as i am very new to VBA and cant seem to get this right.

Below is my Code

Private Sub cmdExit_Click()

Unload Me

End Sub
Private Sub CMDsubmit_Click()
Sheet2.Activate
Range("a2").Value = TextBox1.Value
Range("b2").Value = CBcollector.Value
Range("c2").Value = ListBox1.Value
Range("d2").Value = TextBox3.Value
Range("E2").Value = TextBox2.Value
End Sub

Private Sub CommandButton1_Click()
Dim z As Control
For Each z In UserForm1.Controls
If TypeName(z) = "TextBox" Then
z.Value = ""
End If
Next z
End Sub


Private Sub TextBox1_AfterUpdate()
If IsDate(Me.TextBox1.Text) Then
Me.TextBox1.Text = Format(Me.TextBox1.Text, "dd/mm/yyyy")
End If
End Sub

 

[dmt32]

Hi,
try this change to your code

Private Sub CMDsubmit_Click()
    Dim Nextrow As Long
    With Sheet2
    Nextrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(Nextrow, 1).Value = TextBox1.Value
        .Cells(Nextrow, 2).Value = CBcollector.Value
        .Cells(Nextrow, 3).Value = ListBox1.Value
        .Cells(Nextrow, 4).Value = TextBox3.Value
        .Cells(Nextrow, 5).Value = TextBox2.Value
    End With
End Sub

Dave

 

[Joe4]

Try this:

Private Sub CMDsubmit_Click()
Dim r as Long
Sheet2.Activate
'Find next blank row
r = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & r).Value = TextBox1.Value
Range("B" & r).Value = CBcollector.Value
Range("C" & r).Value = ListBox1.Value
Range("D" & r).Value = TextBox3.Value
Range("E" & r).Value = TextBox2.Value
End Sub

 

[Fluff]

How about

Private Sub CMDsubmit_Click()
   With Sheet2.Range("A" & Rows.Count).End(xlUp)
      .Offset(1).Value = TextBox1.Value
      .Offset(1, 1).Value = CBcollector.Value
      .Offset(1, 2).Value = ListBox1.Value
      .Offset(1, 3).Value = TextBox3.Value
      .Offset(1, 4).Value = TextBox2.Value
   End With
End Sub

 

[ashukla1]

thanks Guys this worked like a charm...

just one more thing

what code should i add to it if i wish to insert a message box when i hit submit

 

[Fluff]

Maybe

Private Sub CMDsubmit_Click()
   With Sheet2.Range("A" & Rows.Count).End(xlUp)
      .Offset(1).Value = TextBox1.Value
      .Offset(1, 1).Value = CBcollector.Value
      .Offset(1, 2).Value = ListBox1.Value
      .Offset(1, 3).Value = TextBox3.Value
      .Offset(1, 4).Value = TextBox2.Value
   End With
   MsgBox "Done"
End Sub