MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Userform Array to worksheet

  Asked By: Cindy    Date: Aug 16    Category: MS Office    Views: 653

Just starting to create a userform to gather user input.

I want to retrieve the userform textbox values into an array.
Then add that array to the bottom of a datalist.

Any Guidance as to the procedure to be used would be most welcome.



4 Answers Found

Answer #1    Answered By: Blaze Fischer     Answered On: Aug 16

I am doing something similar to this, but I don't know if it will help
you or not. I'm collecting info in VBA text boxs and putting them at
the bottom of a list. The code is all in one "submit" button:

Private Sub btnSubmit_Click()
' define dims
Dim sData As String
Dim lRowNum As Long
sData = txtName.Text

' Put the data in the worksheet:
If Cells(1, 1).Value = "" Then
lRowNum = 1
lRowNum = ActiveSheet.UsedRange.Rows.Count + 1
End If
Cells(lRowNum, 1).Value = sData

End Sub

Answer #2    Answered By: Pam Harrison     Answered On: Aug 16

My idea was on clicking the Submit button to loop through the userform
textbox's and collect the data into a single array.
Locate the last record, use offset to move to the next row.
Insert the user  input from the array

I like to make these routines as generic as possible, so would like to
additionally think of combo boxes, list boxes and option groups that I
may add  to the userform  at a later stage, but that is utopia. Just
dealing with plain textboxes at the moment, but if anybody has ideas I
would really appreciate them

Answer #3    Answered By: Shannon Hughes     Answered On: Aug 16

It sounds as though you are on the right track.

I would locate the last+1 row number, and then put the value of each of the
text boxes into its relevant column.

I would not try to make it generic in the sense of using "for each" to loop
through the text boxes because you want to specify which column the value goes
in. So I would use something like:

Newrow = ... last row + 1

With ActiveSheet

.range("A" & Newrow).value = tbxDate.value
.range("B" & Newrow).value = tbxName.value
End with

You may also want to capture the username and current date/time and put those
in the list.

Answer #4    Answered By: Clinton Edwards     Answered On: Aug 16

I do take your point about sequencing, which is very

May I ask, with your "With Activesheet" routine, do you hide the
userform first so that you can still access the userform  data and then
"Unload" after the transfer to the worksheet  is complete?

Your comments re user  & date/time are very valid.

Didn't find what you were looking for? Find more on Userform Array to worksheet Or get search suggestion and latest updates.