VBA UserForm ListBox and Data Table Writing

Question

I am creating a UserForm for an Inventory Clerk who physically counts inventory for auditing purposes. The current process is on paper - I'd like to put it on a tablet.

Goal:

1) Single row comes up on form with item location, product, quantity, and description 2) If the quantity is correct, the user hits "correct" and the next item comes up 3) If the quantity is incorrect, the user keys the observed amount which gets written to column J of the corresponding data table 4) A scroll option to go forward and backward if the user wants to check/re-work an item

    Private Sub CommandButton1_Click()

'GET DATA FROM TABLE

ListBox1.ColumnCount = 4
ListBox1.RowSource = "A2:D500"

End Sub

'IF QTY IS CORRECT, NEXT ROW

Private Sub CommandButton_QtyCorrect_Click()

    Call SpinButton1_SpinUp

End Sub

'IF QTY DOESN'T MATCH, USER KEYS CORRECT

Private Sub CommandButton2_Click()

Range("K1") = TextBox2_Qty.Value
TextBox2_Qty.Value = ""

End Sub

Private Sub ListBox1_Click()
'DISPLAY DATA TABLE
End Sub


Private Sub SpinButton1_SpinDown()
    If ListBox1.ListIndex > 0 Then
        ListBox1.Selected(ListBox1.ListIndex - 1) = True
    End If
End Sub

Private Sub SpinButton1_SpinUp()
    If ListBox1.ListIndex + 1 < ListBox1.ListCount Then
        ListBox1.Selected(ListBox1.ListIndex + 1) = True
    End If
End Sub



Private Sub TextBox2_Qty_Change()
'USER OVERWRITE

End Sub

Questions:

1) With the current setup, all the rows populate the ListBox. How do I get one row at a time to display?

2) When the current row is displayed on the ListBox, how do I write to the corresponding row in column J in the case of a non-match?

Answers

1) Solved by Ralf S below.

2) Solution:

Private Sub AdjustButton_Click()

'IF QTY DOESN'T MATCH, USER KEYS CORRECT

Range("J" & SpinButton1.Value) = TextBox2_Qty.Value

TextBox2_Qty.Value = ""

End Sub

Answer 1

the following code would be my ansatz:

Private UserForm1_Activate()
    ListBox1.ColumnCount = 4
    ListBox1.RowSource = "A2:D2"  ' show only first row 

    SpinButton1.Min = 2
    SpinButton1.Max = Range("A1048576").end(xlUp).row  ' last row as maximum value of spin button
End Sub

Private Sub CommandButton_QtyCorrect_Click()
    If SpinButton1.Value < SpinButton1.Max Then _
            SpinButton1.Value = SpinButton1.Value + 1
End Sub

Private Sub CommandButton2_Click()
    Range("K1") = TextBox2_Qty.Value 
    TextBox2_Qty.Value = ""
End Sub

Private Sub SpinButton1_Change()
    ListBox1.RowSource = "A" & SpinButton1.Value & ":D" & SpinButton1.Value
End Sub

Thus, you could use the spin button's value as the row index for your listbox. This shows only one row at a time and it might help you out for now. But there is still a lot optimization potential...