Adding data to rows with a UserForm - Each entry populates a new row

Dean Kubiak

unread,

May 20, 2015, 2:19:42 PM5/20/15

to exce...@googlegroups.com

I am building a UserForm in Excel that will automatically add data to specific cells when data is entered. For example: I've added a text box called "YourName" and I've added the following code to the text box:

Private Sub YourName_Change()
Worksheets("RSG Activity Tracker").[a2].Value = YourName.Value
End Sub

As the user enters His/er name, cell A2 automatically populates which is fine. My problem is there will be multiple users filling out this UserForm so I need the next users data to be entered in the next row down. Currently any new Names overwrite what is already in cell A2.

What is the code I need to:

1) have the first entry begin in row 2 (My code above works so I believe I am good on this step)

2) all subsequent future entries populate the next row down from the previous entry for each combo box, text box, button, etc.

3) Would also be great to have a Button in the User Form called "New Entry". So once a user completes all the Text and Combo Boxes for one entry they can click the button, which clears the form (BUT NOT THE DATA ENTERED IN THE SPREADSHEET) and they can enter another row of data.

Hope this makes sense.

I've searched on Google for solve's and have found some that come close but I cannot get anything to work.

Thank you for your help!

Vaibhav Joshi

unread,

May 21, 2015, 10:55:41 AM5/21/15

to exce...@googlegroups.com

Hi

can you share sample file with your code...

Cheers!!
https://groups.google.com/forum/#!forum/ExcelVbaLab

Vaibhav Joshi

unread,

May 21, 2015, 11:05:03 AM5/21/15

to exce...@googlegroups.com

Try replacing your code with below one:

Private Sub YourName_Change()

Dim lr As Long

lr = Sheets("RSG Activity Tracker").Cells(Sheets("RSG Activity Tracker").Rows.Count, "A").End(xlUp).Row + 1

Sheets("RSG Activity Tracker").Range("A" & lr).Value = YourName.Value

End Sub

Cheers!!

https://groups.google.com/forum/#!forum/ExcelVbaLab

On Wednesday, May 20, 2015 at 11:49:42 PM UTC+5:30, Dean Kubiak wrote:

Dean Kubiak

unread,

May 21, 2015, 3:38:44 PM5/21/15

to exce...@googlegroups.com

Vaibhav,

That's getting closer but it populates 1 letter in cell A2, 2 letters in Cell A3, 3 letters in Cell A4.

Example:

A

AB

ABC
ABCD

ABCDE
ABCDEF

I copied your formula exactly.

Thank you for your help! It's closer than my previous attempts. Let me know if you have any other ideas.

Thanks!

Vaibhav Joshi

unread,

May 22, 2015, 5:50:39 AM5/22/15

to exce...@googlegroups.com

can you share me your whole code to guide you further..

--
You received this message because you are subscribed to a topic in the Google Groups "excel vba" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/excel-vba/hc8MRjQXzfE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to excel-vba+...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-vba.
For more options, visit https://groups.google.com/d/optout.

Dean Kubiak

unread,

May 22, 2015, 10:27:31 AM5/22/15

to exce...@googlegroups.com

Sure thing! Here you go. Very simple but I'm stumped.

Private Sub Appointment_Date_Change()

Worksheets("RSG Activity Tracker").[d2].Value = Appointment_Date.Value

End Sub

Private Sub Catname_Change()

Worksheets("RSG Activity Tracker").[c2].Value = Catname.Value

End Sub

Private Sub Close_Button_Click()

'close the form (itself)

Unload Me

End Sub

Private Sub Custname_Change()

Worksheets("RSG Activity Tracker").[b2].Value = Custname.Value

End Sub

Private Sub Enter_Data_Click()

End Sub

Private Sub Impdate_Change()

Worksheets("RSG Activity Tracker").[e2].Value = Impdate.Value

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub MAPS_Topic_Click()

Worksheets("RSG Activity Tracker").[f2].Value = MAPS_Topic.Value

End Sub

Private Sub TextBox_Comments_Change()

Worksheets("RSG Activity Tracker").[h2].Value = TextBox_Comments.Value

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub Yes_No_Box_Click()

Worksheets("RSG Activity Tracker").[g2].Value = Yes_No_Box.Value

End Sub

Private Sub YourName_Change()

Worksheets("RSG Activity Tracker").[a2].Value = YourName.Value

End Sub

Vaibhav Joshi

unread,

May 22, 2015, 12:27:35 PM5/22/15

to exce...@googlegroups.com

can u send me screenshot of user form..

actually it should not be textbox change event, but textbox exit event ..

Vaibhav Joshi

unread,

May 23, 2015, 8:50:37 AM5/23/15

to exce...@googlegroups.com

check this demo...

do let me know if you have any query..

Cheers!!

Userform.xlsb

Dean Kubiak

unread,

May 26, 2015, 5:33:30 PM5/26/15

to exce...@googlegroups.com

Here's a screenshot of the userform:

Dean Kubiak

unread,

May 26, 2015, 5:38:16 PM5/26/15

to exce...@googlegroups.com

So instead of: Private Sub YourName_Change()

It should be: Private Sub YourName_Exit()

??

And I would actually need to do this for each of the Text, Combo, or other types of input boxes I have, correct?

Dean Kubiak

unread,

Jun 3, 2015, 1:48:21 PM6/3/15

to exce...@googlegroups.com

Vaibhav,

I downloaded your Userform.xlsb and and got my userform to do exactly what I wanted!! Thank you so much. Cheers!

Vaibhav Joshi

unread,

Jun 3, 2015, 2:48:51 PM6/3/15

to exce...@googlegroups.com

great..

Cheers

Nina Manns

unread,

Apr 12, 2016, 12:06:17 PM4/12/16

to excel vba

Hello Vaibhav Joshi:

Wondering if you could possibly help me find a solution.

I have created a shared workbook containing a userform that has VBA code to transfer input data to a worksheet. However, when multiple users enter data a Resolve Conflict dialog box is displayed as follows:

Your changes on sheet "SrOfcSpc" changed cell A4 from '' to '4/15/2016' Conflicting changes by John Doe -- 4/11/16 3:44PM changed cell A4 from '' to '4/11/2016'

How can I prevent this from happening so that multiple users can input at the same time but data is written to separate lines without overwriting the previous user? Below is a snippet of my existing code. Thank you.

lrCD4 = Sheets("SrOfcSpc").Range("A" & Rows.Count).End(xlUp).Row
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "A").Value = TextBox43.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "B").Value = TextBox44.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "C").Value = TextBox25.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "D").Value = TextBox26.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "E").Value = TextBox27.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "F").Value = TextBox31.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "G").Value = TextBox23.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "H").Value = TextBox24.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "I").Value = TextBox29.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "J").Value = TextBox30.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "K").Value = TextBox195.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "L").Value = TextBox196.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "M").Value = TextBox204.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "N").Value = TextBox203.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "O").Value = TextBox200.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "P").Value = TextBox199.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "Q").Value = TextBox198.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "R").Value = TextBox197.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "S").Value = TextBox202.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "T").Value = TextBox201.Text
Sheets("SrOfcSpc").Cells(lrCD4 + 1, "U").Value = TextBox205.Text

Vaibhav Joshi

unread,

Apr 12, 2016, 12:31:36 PM4/12/16

to exce...@googlegroups.com

hi

try below code

On Error GoTo ExcelVbaLab:

lrCD4 = Sheets("SrOfcSpc").Range("A" & Rows.Count).End(xlUp).Row

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "A").Value = TextBox43.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "B").Value = TextBox44.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "C").Value = TextBox25.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "D").Value = TextBox26.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "E").Value = TextBox27.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "F").Value = TextBox31.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "G").Value = TextBox23.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "H").Value = TextBox24.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "I").Value = TextBox29.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "J").Value = TextBox30.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "K").Value = TextBox195.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "L").Value = TextBox196.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "M").Value = TextBox204.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "N").Value = TextBox203.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "O").Value = TextBox200.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "P").Value = TextBox199.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "Q").Value = TextBox198.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "R").Value = TextBox197.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "S").Value = TextBox202.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "T").Value = TextBox201.Text

Sheets("SrOfcSpc").Cells(lrCD4 + 1, "U").Value = TextBox205.Text

ExcelVbaLab:

lrCD4 = lrCD4 + 1