Selected Answer
A recurring task in your project is to find the row in List where columns A, B and C match the entries in your ComboBoxes 1, 2 and 3. I have provided this code to you and now you are asking for it again. That is not efficient use of my time.
Because of the way you formulated your question at the time the code you received doesn't directly provide the answer you need now. Your job is to find the question you now need to ask and then adapt the answer you have to provide the answer you need. Perhaps you need help with that. But just to ask me to do the same coding all over again is not the right way.
Your question now is "Which is the matching row?" For that you need a function of the design demonstrated below.
Function MatchingRowNumber(Brand As String, _
Typ As String, _
Origin As String) As Long
' 290
Dim Fun As Long
With Worksheet("List")
' search for the match here
' Fun interim result
End With
MatchingRowNumber = Fun
End Function
The function takes 3 arguments which are the values from Cbx1, Cbx2 and Cbx3. I will return the number of the row from which you can read the quantiy. So, your function call might look like this:-
Private Function IsAvailable(ByVal Qty As Long) As Boolean
' 290
Dim R As Long
Dim AvailQty As Long
R = MatchingRowNumber(ComboBox1.Value, _
ComboBox2.Value, _
ComboBox3.Value)
AvailQty = List.Cells(R, "D").Value
If AvailQty < Qty Then
MsgBox "The quantity you entered exceeds availability." & vbCr & _
"Available quantity is " & AvailQty & ".", _
vbInformation, "Limited availability"
Else
IsAvailable = True
End If
End Function
The second function will return True or False, depending on the comparison of the argument "Qty" with the quantity retrieved from the List with the help of the first function. The second function must be a function because you want to run it twice, once for each TextBox. So, in the form's code module you would have an even procedure like tghis one:-
Private Sub TextBox1_Change()
' 290
If IsAvailable(TextBox1.Value) Then
' continue with the program
Else
' return to the textbox
TextBox1.SetFocus
End If
End Sub
And another one just like it for TextBox2.
All of the above hinges on code you already have. Therefore I haven't provided it again.
BTW, I don't feel very good about the formatting of your code. Every time you ask a question you received perfectly formatted code in response but every one of your questions has code in it that looks like a garbage dump. You could encourage us to help you more if you would show some capability to learn.
