PDA

View Full Version : How to Go to Next Row on a UserForm



mmushtaq
02-17-2016, 04:32 PM
Hi all,

I'm new to VBA and I have to create a userform that asks the user for FirstName, LastName, and Uniqname and also has a cancel button that closes the program. I have got that down as follows:


Private Sub btncancel_Click()
Me.Hide
End Sub


Private Sub buttonAdd_Click()
Cells(2, 1) = txtFirstName.Text
Cells(2, 2) = txtLastName.Text
Cells(2, 3) = txtUniqname.Text
Columns("A:C").EntireColumn.AutoFit


txtFirstName = ""
txtLastName = ""
txtUniqname = ""




End Sub


However, once the user has entered the information, I need to input the second set of data into the next row. Right now, if I enter a set of data into the userform, then add another set of data, it just occupies the same cells as the ones before. What code do I use so that new data is inputed into the next row? Any help is appreciated.

SamT
02-17-2016, 05:47 PM
Dim NextRow as Long
NextRow = Worksheet (?).Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(nextRow, 1) = ...

mikerickson
02-17-2016, 05:50 PM
Perhaps this will work for you


Private Sub buttonAdd_Click()
With Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
.Cells(1, 1) = txtFirstName.Text
.Cells(1, 2) = txtLastName.Text
.Cells(1, 3) = txtUniqname.Text
End With
Columns("A:C").EntireColumn.AutoFit


txtFirstName = ""
txtLastName = ""
txtUniqname = ""

End Sub

SamT
02-17-2016, 07:22 PM
That is a cool technique.

Paul_Hossler
02-17-2016, 08:25 PM
It works as long as Row 1 is not blank. Puts it on Row 2 instead

Non-UF example



Sub test()
Dim txtFirstName As String
Dim txtLastName As String
Dim txtUniqname As String

txtFirstName = "John"
txtLastName = "Smith"
txtUniqname = "Smith, John"

With Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
.Cells(1, 1) = txtFirstName
.Cells(1, 2) = txtLastName
.Cells(1, 3) = txtUniqname
End With
Columns("A:C").EntireColumn.AutoFit


txtFirstName = ""
txtLastName = ""
txtUniqname = ""

End Sub

mikerickson
02-17-2016, 08:29 PM
The OP started in row two. Sounds like there is a header row already present.

Paul_Hossler
02-18-2016, 07:04 PM
The OP started in row two. Sounds like there is a header row already present.

Your assumption is probably correct, but I've seen people leave row 1 empty just to have a little space at the top

shailendranr
02-18-2016, 09:31 PM
This will be the dynamic solution for this problem i hope so

sub add_data()

dim f_name,l_name,u_id as string
'change the userform1 and textbox names as you given

f_name=userform1.textbox1.value
l_name=userform1.textbox2.value
u_id=userform1.textbox3.value

'am assuming sheet name as sheet1 change as you given
thisworkbook.activate
sheet1.select

range("a1000000").end(xlup).offset(1,0).select
activecell.value=f_name
activecell.offset(0,1).value=l_name
activecell.offset(0,2).value=u_id

thisworkbook.save

unlode userform1

End sub