How to use Userform to input a list of data

[Jodeyarce]

Hi peeps,

Is there a way to automatically input a list of data using Userform?

For example,

I will have 2 input textboxes:
1. Job Number
2. No. of Shirts

When i enter Job Number 1 and for e.g. 4 no. of shirts, I am hoping the VBA will automatically insert the data into:

Column A
(Job No.)
1 (Cell A1)
Column B
(Shirt No.)
1 (Cell B1)
2 (Cell B2)
3 (Cell B3)
4 (Cell B4)
Also, if i continue with a 2nd Job, the shirt number will reflect continuously (e.g. Userform Job No.=2 & No.of Shirts=3),i.e.
Column A
(Job No.)
1 (Cell A1)
2 (Cell A4)
Column B
(Shirt No.)
1 (Cell B1)
2 (Cell B2)
3 (Cell B3)
4 (Cell B4)
5 (Cell B5)
6 (Cell B6)
7 (Cell B7)


Thanks in advance! :)

[Rob342]

Jodeyarce

Are you filling the text box or do you want to write it back to a ws
Post a copy of your wb and lets see where you are with it.

Rob

[Jodeyarce]

Hi Rob,

Thanks for your prompt reply:)

I've already created the userform, though I don't know how to write the VBA for it. Basically, when i reflect 1 in the textbox1 of "Job No." & 4 in textbox2 of "No. of Shirts" and press "Ok", I'd hope that it will automatically list 1 in cell A1(Job No.) and 1,2,3,4 in cells B1-4(Shirt No.)

Consequently when i add Job 2 with 3 shirts, I hope that it will automatically list 2 in cell A5 and 5,6,7 in cells B5-7.

Hope that you can understand what i mean:)

[Aflatoon]

Add a button to your form, name it cmdAdd and then add this code:

Private Sub cmdAdd_Click()
Dim wks As Excel.Worksheet
Dim lngNextRow As Long
Dim n As Long
Dim lngStartNum As Long
' output sheet - change as required
Set wks = Sheets("Sheet1")

If Me.TextBox1 <> "" And IsNumeric(Me.TextBox2.Text) Then
lngNextRow = wks.Cells(wks.Rows.Count, "B").End(xlUp).Row + 1

wks.Cells(lngNextRow, "A").Value = Me.TextBox1.Text
' check if last input in column B was a number;
' if so, start counting from there
If IsNumeric(wks.Cells(lngNextRow - 1, "B").Value) Then
lngStartNum = wks.Cells(lngNextRow - 1, "B").Value
Else
lngStartNum = 0
End If
For n = 1 To CLng(Me.TextBox2.Text)
wks.Cells(lngNextRow + n - 1, "B").Value = lngStartNum + n
Next n
End If

End Sub


See attached version of your file.

[Jodeyarce]

Omg thanks Aflatoon! It works like a wonder:) exactly what i needed! THANK YOU SO MUCH

[Aflatoon]

You are most welcome.