updating code to show message if the number is exceeded based on fill in textbox

[leap out]

hi

in earlier Mr. Dante solve my thread in this link match data based on three combobox & three columns and populate values into textbox
now I want updating the code but not populate the values after match . what I want if the value in textbox1,2 after fill manually is bigger than the values in COL D then should show message 'the qty is exceeded , the qty is... please try again"
and if the value smaller then should copy to the sheet to the bottom

Private Sub CommandButton1_Click()
  Dim a As Variant
  Dim i As Long
 
  TextBox1 = ""
  TextBox2 = ""
  a = Sheets("list").Range("A2", Sheets("list").Range("D" & Rows.Count).End(3)).Value
  For i = 1 To UBound(a)
    If a(i, 1) = ComboBox1 And a(i, 2) = ComboBox2 And a(i, 3) = ComboBox3 Then TextBox1 = a(i, 4)
    If a(i, 1) = ComboBox4 And a(i, 2) = ComboBox5 And a(i, 3) = ComboBox6 Then TextBox2 = a(i, 4)
  Next
  If TextBox1 = "" Then MsgBox "For textbox1. The items are not matched, please try again"
  If TextBox2 = "" Then MsgBox "For textbox2. The items are not matched, please try again"
End Sub

 

[richh]

Hi leap out,

So it appears that your users can manually enter a value into the textbox first; one key thing you may want to do is have a change event on the three combo boxes to first display the max quantity they can enter. Surprise constraints tend to anger users.

Also, if your users can enter in values in the boxes upfront and you want to check their value, you shouldn't be clearing the textboxes right off the bat. How can you then check to see what they entered if you just erased it?

ALSO also (lol), I would highly recommend naming your userform controls. "CommandButton1" and "Textbox1" might be easy to comprehend with the userform at its current state, but as you get further into your code (and in your coding career), you'll find yourself having to pop back to the form to see which button is which, which textbox is which, etc.

I'm not entirely certain this will solve your problem, but here's something that may. I've added in some Data Validation techniques that ensure the data entered is a number, does not exceed the max value, and is not <= 0. Also, sorry I didn't test it - I'm lazy:

Private Sub CommandButton1_Click()
  Dim a         As Variant
  Dim i         As Long
  Dim uAns      As Integer
  Dim ws        As Worksheet
  Dim WriteRow  As Long
  
  'If you need to check what has been manually written
  'into the textboxes, don't clear them
  'Textbox1 = ""
  'TextBox2 = ""
  Set ws = Application.ThisWorkbook.Worksheets("list")
  lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

  If Textbox1 = "" Or Textbox2 = "" Then 
    Msgbox "Please enter a quantity for each item."
    Exit sub
  End if  
  
  a = Sheets("list").Range("A2", Sheets("list").Range("D" & Rows.Count).End(3)).Value
  For i = 1 To UBound(a)
    If a(i, 1) = ComboBox1 And a(i, 2) = ComboBox2 And a(i, 3) = ComboBox3 Then
        If IsNumeric(Textbox1.Value) = False Then
            MsgBox "Text enetered for item 1 quantity is not a number. Try again.", vbCritical + vbOKOnly, "Entry Error"
            Textbox1 = ""
            Exit Sub
        If Textbox1 > a(i, 4) Then
            uAns = MsgBox("The quantity entered for item 1 item has been exceed." & _
            vbNewLine & vbNewLine & _
            "The maximum quantity is " & a(i, 4) & ". Accept max value as your entry?", _
            vbYesNo + vbCritical, _
            "Maximum value exceeded")
            
            If uAns = vbYes Then
                Textbox1 = a(i, 4)
            Else
                Textbox1 = ""
                Exit Sub
            End If
        ElseIf Textbox1 > 0 And Textbox1 <= a(i, 4) Then
            ws.Cells(WriteRow, 1) = a(i, 1)
            ws.Cells(WriteRow, 2) = a(i, 2)
            ws.Cells(WriteRow, 3) = a(i, 3)
            ws.Cells(WriteRow, 4) = Textbox1.Value
            WriteRow = WriteRow + 1
        ElseIf Textbox1 <= 0 Then
            MsgBox "Quantity cannot be less than or equal to 0."
            Textbox1 = ""
            Exit Sub
        End If
    End If
    
    If a(i, 1) = ComboBox4 And a(i, 2) = ComboBox5 And a(i, 3) = ComboBox6 Then
        If IsNumeric(Textbox2.Value) = False Then
            MsgBox "Text enetered for item 2 quantity is not a number. Try again.", vbCritical + vbOKOnly, "Entry Error"
            Textbox2 = ""
            Exit Sub
        If Textbox2 > a(i, 4) Then
            uAns = MsgBox("The quantity entered for item 2 item has been exceed." & _
            vbNewLine & vbNewLine & _
            "The maximum quantity is " & a(i, 4) & ". Accept max value as your entry?", _
            vbYesNo + vbCritical, _
            "Maximum value exceeded")
            
            If uAns = vbYes Then
                Textbox2 = a(i, 4)
            Else
                Textbox2 = ""
                Exit Sub
            End If
        ElseIf Textbox2 > 0 And Textbox2 <= a(i, 4) Then
            ws.Cells(WriteRow, 1) = a(i, 1)
            ws.Cells(WriteRow, 2) = a(i, 2)
            ws.Cells(WriteRow, 3) = a(i, 3)
            ws.Cells(WriteRow, 4) = Textbox2.Value
            WriteRow = WriteRow + 1
        ElseIf Textbox2 <= 0 Then
            MsgBox "Quantity cannot be less than or equal to 0."
            Textbox2 = ""
            Exit Sub
        End If
    End If
  Next
  If Textbox1 = "" Then MsgBox "For textbox1. The items are not matched, please try again"
  If Textbox2 = "" Then MsgBox "For textbox2. The items are not matched, please try again"
End Sub

 

[leap out]

thanks but it gives error

 

[leap out]

another option experts ?