add message to the code if the number is excceeded



 I  search  way  to  pop warning message  if  the  number is excceeded . so   when  I  put  the  number whether in textbox1 or 2  . it  should  show  message box " it's not avalaible  for this  quantitiy  , the real quantity is ...,please take  qty less"  , in case the  number  bigger  what  is  existed  in sheet LIST  in COL  D. this  debends on match cmoboboxes(1,2,3) with COL A,B,C also  cmoboboxes(4,5,6) with COL A,B,C and  the  textbox1,2 with COL D 

Private Sub CommandButton1_Click()
Dim LAS As Long
LAS = Cells(Rows.Count, 5).End(xlUp).Row + 1
Range("A" & LAS).Resize(, 5) = Array(ComboBox1.Value, ComboBox2.Value, ComboBox3.Value, TextBox1.Value)
Range("A" & LAS + 1).Resize(, 5) = Array(ComboBox4.Value, ComboBox5.Value, ComboBox6.Value, TextBox2.Value)
End Sub

thanks  in advance




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, _
    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"
        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
        ' return to the textbox
    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.



first   thanks ,  but  it  gives  application defined or object defined error in this  line
AvailQty = List.Cells(R, "D").Value
second   I  don't  think to got like  this  code  before . third   about  this  ,  but every one of your questions has code in it that looks like a garbage dump you  don't  expect to  deal  with  professional  to  desighn  perfect  code   as  you  know  I'm begginner   . I try  searching and learning   from   the  internet  and  try  writing  some lines  .
leap (rep: 22) Jul 22, '21 at 10:25 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login