View Full Version : Using a userform with a for loop

04-22-2016, 07:24 AM

I have the following for loop in my workbook:

Sub IND_External_Find_NAs()

'finds N/A's in column U of IND-External tab, if found, adds item# to Oct 2012 PL tab and asks user for Product Line and Type information

Dim CellsToProcess As Range
Dim sht as Worksheet

Set sht = Sheets("IND-External")

'Delete any extra formulas entered after last row of data in column J of IND-External tab
lastrow = sht.Cells(Rows.Count, 10).End(xlUp).Row + 1
sht.Range("L" & lastrow & ":U" & lastrow).Select
Range(Selection, Selection.End(xlDown)).Select

'Loops through each N/A# found in column U, searches Item list, adds if not found and gets product line and type from user input
With Sheets("Oct_2012_Item_PL")
On Error Resume Next
Set CellsToProcess = sht.Range("U:U").SpecialCells(xlCellTypeFormulas, 16)
On Error GoTo 0
If Not CellsToProcess Is Nothing Then
For Each cll In CellsToProcess.Cells
zzz = Application.Match(sht.Cells(cll.Row, "F").Value, .Range("A:A"), 0)
If IsError(zzz) Then
Set lastitem = .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
lastitem.Value = sht.Cells(cll.Row, "F").Value
lastitem.Offset(0, 1).Value = sht.Cells(cll.Row, "G").Value
lastitem.Offset(, 4).Value = "'" & InputBox("Please enter Product Line # for item: " & lastitem.Value)
lastitem.Offset(, 6).Value = "'" & InputBox("Please enter Product Type # for item: " & lastitem.Value)
lastitem.Offset(, 15).Value = "'" & lastitem.Offset(, 4).Value
lastitem.Offset(, 16).Value = "'" & lastitem.Offset(, 6).Value
End If
Next cll
End If
End With
End Sub

This code loops through a column on one sheet row by row looking for N/As in a vlookup. When one is found, it takes the corresponding item# and description and adds it to the next blank row of the product line sheet. It then prompts the user to enter the product line and product type via input boxes which are also added to the product line sheet. What I would like to do, is replace these two input boxes with a userform. I would like the userform to populate with the item# (lastitem.value) and then have two boxes for the user to input the necessary information. When they press next, I would like the for loop to continue on to the next N/A and repeat the process until there are none left. I am new to userforms and am not sure how to incorporate this into the code above.

Here is a ss of the form:

Any tips or help would be greatly appreciated!

04-22-2016, 07:45 AM
Reverse the paradigm. . . instead of a UserForm inside a loop, consider a loop inside a UserForm.

04-23-2016, 07:48 PM
Don't think of it as a user form, think of it as a custom InputBox and write it to return a comma delimited string of the two values.

The attached has this in the user form code module

' in userform code module

Public Function LineAndType(ItemNumber As String) As String
With Me
.txtItem = ItemNumber
.txtItem.Locked = True
.butOK.Enabled = False
End With
With UserForm1
If .Tag = "OK" Then
LineAndType = .txtLine & "," & .txtType
End If
End With
UnLoad Userform1
End Function

Private Sub butCancel_Click()
Unload Me
End Sub

Private Sub butOK_Click()
Me.Tag = "OK"
End Sub

Private Sub txtLine_Change()
Me.butOK.Enabled = ((Me.txtLine <> vbNullString) And (Me.txtType <> vbNullString))
End Sub

Private Sub txtType_Change()
Me.butOK.Enabled = ((Me.txtLine <> vbNullString) And (Me.txtType <> vbNullString))
End Sub

And this behind the demo button.

Sub test()
Dim userValues As String

userValues = UserForm1.LineAndType(1234)

If userValues = vbNullString Then
MsgBox "User canceled"
MsgBox "User entered " & Split(userValues, ",")(0) & " and " & Split(userValues, ",")(1)
End If
End Sub