Using a userform with a for loop

[qitjch]

Hello,

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
                Selection.ClearContents
    
'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:
userform2.jpg

Any tips or help would be greatly appreciated!

[SamT]

Reverse the paradigm. . . instead of a UserForm inside a loop, consider a loop inside a UserForm.

[mikerickson]

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
        .Show
    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"
    Me.Hide
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"
    Else
        MsgBox "User entered " & Split(userValues, ",")(0) & " and " & Split(userValues, ",")(1)
    End If
End Sub