Filling a WS from a UserForm

[JK]

I am using a UF to fill a WS with unit count and product. For example, 2
Widgets, 2 Gadgets, 2 Gidgets should fill as follows:
1 Widget
2 Widget
3 Gadget
4 Gadget
5 Gidget
6 Gidget
The following code works:
Private Sub CommandButton1_Click()

Dim myCell As Range

Set myCell = ActiveSheet.Range("A1")

Dim j As Long, k As Long, i As Long

ActiveSheet.Range("A1:B15").Value = ""

On Error Resume Next

j = 0

For k = 1 To 10 Step 2

For i = 1 To CLng(UserForm1.Controls("TB" & k).Text)

j = j + 1

myCell.Offset(j - 1, 0).Value = j

myCell.Offset(j - 1, 1).Value = _

UserForm1.Controls("TB" & k + 1).Text

Next i

Next k

myCell.Select

On Error GoTo 0

End Sub

However, my UF allows a unit count for up to 5 products. So when I enter
only the 3 products (Widget, Gadget, Gidget) the WS looks like this:

1 Widget

2 Widget
3 Gadget
4 Gadget
5 Gidget
6 Gidget
7
8
How can I correct the code to only count the 3 products entered?
Also (if I'm not pushing my luck), I would prefer to use a TB for the unit
count only, and a list box for the product. If a code can be written for
that, I would very much help with that also.
Thank you in advance.
Jim Kobzeff

 

[Dave Peterson]

Maybe...

Option Explicit
Private Sub CommandButton1_Click()

Dim myCell As Range
Set myCell = ActiveSheet.Range("A1")
Dim j As Long, k As Long, i As Long
ActiveSheet.Range("A1:B15").Value = ""
On Error Resume Next
j = 0
For k = 1 To 10 Step 2
For i = 1 To CLng(UserForm1.Controls("TB" & k).Text)
If Trim(UserForm1.Controls("TB" & k + 1).Text) = "" Then
'do nothing
Else
j = j + 1
myCell.Offset(j - 1, 0).Value = j
myCell.Offset(j - 1, 1).Value = _
UserForm1.Controls("TB" & k + 1).Text
End If
Next i
Next k
myCell.Select
On Error GoTo 0

End Sub

I'm not sure why you're using the "on error" stuff. I don't see anything that
can blow it up real good.

========
As an alternative, have you thought about just having 3 textboxes for the
quantities--but have the widgets, gadgets, and gidgets as labels on your user
form--or even comboboxes that support 0 to 5 for each item.

Kind of like:

Option Explicit
Private Sub CommandButton1_Click()

Dim myCell As Range
Set myCell = ActiveSheet.Range("A1")
Dim CtrlCtr As Long
Dim QtyCtr As Long
Dim RowCtr As Long
ActiveSheet.Range("A1:B15").Value = ""

RowCtr = 0
For CtrlCtr = 1 To 3
For QtyCtr = 1 To Val(Me.Controls("combobox" & CtrlCtr).Value)
myCell.Offset(RowCtr, 0).Value = RowCtr + 1
myCell.Offset(RowCtr, 1).Value = Me.Controls("label" & CtrlCtr).Caption
RowCtr = RowCtr + 1
Next QtyCtr
Next CtrlCtr
myCell.Select

End Sub

Private Sub UserForm_Initialize()
Dim CtrlCtr As Long
Dim QtyCtr As Long

For CtrlCtr = 1 To 3
With Me.Controls("combobox" & CtrlCtr)
.Style = fmStyleDropDownList
For QtyCtr = 0 To 5
.AddItem QtyCtr
Next QtyCtr
End With
Next CtrlCtr
End Sub