Next Row...

[Chris6911]

Hi Guys!

I'm currently working on a little project. It's a VBA UserForm.

The user enter his name, choose between 2 option (for Age +18 or -18) and it send information

NAME

Private Sub NAMEValue()
    Sheets("TRACKER").Range("A1") = NAME.Value
End Sub

For the age (OptionButton)

Private Sub AGEValue()
 If OB1.Value = True Then
    Sheets("TRACKER").Range("B1").Value = "1"
    Sheets("TRACKER").Range("C1").Value = "0"
ElseIf OB2.Value = True Then    
    Sheets("TRACKER").Range("B1").Value = "0"
    Sheets("TRACKER").Range("C1").Value = "1"

And a Submit button who call the NAMEValue & Age Value.

My issue is... When the next user enter his name, it print over on the A1, B1 & C1. I'd like, when he/she click on submit, that the excel go on A2-B2-C2 then A3-B3-C3 etc.

Then, I will be able to have a list of name and age.

Thank's in advance for help

 

[Roderick_E]

Hi Guys!

I'm currently working on a little project. It's a VBA UserForm.

The user enter his name, choose between 2 option (for Age +18 or -18) and it send information

NAME

Private Sub NAMEValue()
    Sheets("TRACKER").Range("A1") = NAME.Value
End Sub

For the age (OptionButton)

Private Sub AGEValue()
 If OB1.Value = True Then
    Sheets("TRACKER").Range("B1").Value = "1"
    Sheets("TRACKER").Range("C1").Value = "0"
ElseIf OB2.Value = True Then    
    Sheets("TRACKER").Range("B1").Value = "0"
    Sheets("TRACKER").Range("C1").Value = "1"

And a Submit button who call the NAMEValue & Age Value.

My issue is... When the next user enter his name, it print over on the A1, B1 & C1. I'd like, when he/she click on submit, that the excel go on A2-B2-C2 then A3-B3-C3 etc.

Then, I will be able to have a list of name and age.

Thank's in advance for help

So you need to dynamically determine the lastrow upon each submit. Can you combine your routines into one so the user only needs to press submit once? Something like:

sub processit
lastrow =Sheets("TRACKER").Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row + 1
 Sheets("TRACKER").cells(lastrow,"A") = NAME.Value
 If OB1.Value = True Then
    Sheets("TRACKER").cells(lastrow,"B").Value = "1"
    Sheets("TRACKER").cells(lastrow,"C").Value = "0"
ElseIf OB2.Value = True Then    
    Sheets("TRACKER").cells(lastrow,"B").Value = "0"
    Sheets("TRACKER").cells(lastrow,"C").Value = "1"
endif
end sub

 

[Chris6911]

So you need to dynamically determine the lastrow upon each submit. Can you combine your routines into one so the user only needs to press submit once? Something like:

sub processit
lastrow =Sheets("TRACKER").Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row + 1
 Sheets("TRACKER").cells(lastrow,"A") = NAME.Value
 If OB1.Value = True Then
    Sheets("TRACKER").cells(lastrow,"B").Value = "1"
    Sheets("TRACKER").cells(lastrow,"C").Value = "0"
ElseIf OB2.Value = True Then    
    Sheets("TRACKER").cells(lastrow,"B").Value = "0"
    Sheets("TRACKER").cells(lastrow,"C").Value = "1"
endif
end sub

I tried and it's not working ; ROW dosen't change From A to B (etc.)

 

[Dryver14]

try changing this line

lastrow =Sheets("TRACKER").Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row + 1

to lastrow = Sheets("Tracker").cells(rows.count,1).end(xlup).row + 1

 

[Chris6911]

Didn't work neither ; BUT I found the solution.

For some reason, it was not changing row ; so was printing on same row SUBMIT after SUBMIT

So I modified a lil bit your idea... I canged

Sheets("TRACKER").cells(lastrow,"B").Value = "1"

Into:

Sheets("TRACKER").Range("B" & ActiveCell.Row).Value = "1"

And then I created a "jumpnext()" script to change ROW. The jumpnext() change to row 2 and the new code w/ ActiveCell is copying the info at the right place

Sub jumpnext()
    Range("B" & ActiveCell.Row + 1).Select
End Sub

Thanks again!

 

[Dryver14]

A little long winded but If your happy I'm happy