Selected Answer
Add the code below to your user form's code sheet.
Private Sub CommandButton1_Click()
' 05 Jun 2019
Dim TbxNames() As String
Dim Max As Long
Dim Qty As Long
Dim R As Long
Dim i As Integer
TbxNames = Split("TbxItem TbxBrand TbxQty")
For i = 0 To UBound(TbxNames)
With Controls(TbxNames(i))
If Len(.Value) = 0 Then
MsgBox "Please assign a value to textbox " & .Name, _
vbExclamation, "Missing entry"
.SetFocus
Exit Sub
End If
End With
Next i
Qty = Val(TbxQty.Value)
Max = BrandMaximum(TbxBrand.Text)
If Max And Max < Qty Then
MsgBox "Quantity exceeds maximum for " & TbxBrand.Value, _
vbExclamation, "Excessive quantity"
TbxQty.SetFocus
Else
R = Cells(Rows.Count, "A").End(xlUp).Row + 1
For i = 0 To UBound(TbxNames)
With Controls(TbxNames(i))
Cells(R, i + 1).Value = .Value
.Value = ""
End With
Next i
End If
End Sub
Private Function BrandMaximum(Brand As String) As Long
' 05 Jun 2019
' This function must refer to a list of brands and related
' maximum quantities yet to be created,
' and return the quantity found for the brand in TbxBrand
' I suggest to let the function return zero if there is no maximum
BrandMaximum = 300 ' provisional, unqualified function return
End Function
It includes a function that would determine the maximum quantity permissible for each brand according to a list you might have in a separate worksheet of the same workbook. This function currently just returns "300".
The attached workbook demonstrates the code.