add multiples message boxes to procedure if exceed the QTY in textbox on userform

[Maklil]

Hi
I need adapting this code by add message box if I fill number in textbox4 under ENTERING bigger or small than textbox3 under NET

Private Sub ComboBox1_Change()

  Dim ws          As Worksheet
    Dim lr          As Long, c As Long, i As Long
    Dim qty         As Double
    
    i = 1: c = 4
    If Me.ComboBox1.Value <> "" Then
        For Each ws In ThisWorkbook.Worksheets(Array("ORDERS", "STOCK"))
            lr = ws.Cells(ws.Rows.Count, c).End(xlUp).Row
            qty = WorksheetFunction.SumIf(ws.Cells(2, c).Resize(lr), Me.ComboBox1.Value, ws.Cells(2, c + 1).Resize(lr))
            Me.Controls("TextBox" & i).Text = qty
            i = 2: c = 2
            qty = 0
        Next ws
    End If
    TextBox3.Value = Val(TextBox2.Value) - Val(TextBox1.Value)
    
End Sub

example:
select item from combobox1 and will populate numbers textbox1:textbox3 , textbox3 =200 .pic1 : when I enter number in textbox4 =500
then messagebox " the only available QTY is 200 , so you should wait to arrive 300 qty in next orders "and should just fill 200 in textbox4 after massage box is gone .
300 is got by (500-200=300)

pic2: if textbox3=0 and textbox4 =200 then message box "there is no available QTY for this brand",so you should wait to arrive 200 qty in next orders" and textbox4=0 after massage box is gone
200is got by (200-0=200)

pic3: if textbox3 is minus =-100 and textbox4=200 then message box "there is no available QTY for this brand ,so you should wait to arrive 300 qty in next orders " and textbox4=0 after massage box is gone
300 is got by (200-(-100)=300)

I hope this clear
thanks

 

[dmt32]

Hi,
rather than adding all that complexity in error reporting, why not consider just adding a line at the bottom of the ComboBox1 change code to disable or hide your entry control (Textbox4) if the Net amount (Textbox3) is less than one? This way, users can only enter a value if stock is available?
You can then just write some code to catch the error when users enter a request for more than is available.

Try adding line below & see if of any help

Private Sub ComboBox1_Change()

    Dim ws          As Worksheet
    Dim lr          As Long, c As Long, i As Long
    Dim qty         As Double
    
    i = 1: c = 4
    If Me.ComboBox1.Value <> "" Then
        For Each ws In ThisWorkbook.Worksheets(Array("ORDERS", "STOCK"))
            lr = ws.Cells(ws.Rows.Count, c).End(xlUp).Row
            qty = WorksheetFunction.SumIf(ws.Cells(2, c).Resize(lr), Me.ComboBox1.Value, ws.Cells(2, c + 1).Resize(lr))
            Me.Controls("TextBox" & i).Text = qty
            i = 2: c = 2
            qty = 0
        Next ws
    End If

    TextBox3.Value = Val(TextBox2.Value) - Val(TextBox1.Value)
   
   Me.TextBox4.Visible = Val(Me.TextBox3.Value) > 0
End Sub

Also, I would suggest whilst developing your project to give the controls meaningful names that follow a Naming Convention

Dave

 

[Maklil]

ok

your way when textbox3 is minus value , but what if the textbox3 is positive value and smaller than textbox4?

 

[dmt32]

ok
your way when textbox3 is minus value , but what if the textbox3 is positive value and smaller than textbox4?

You can then just write some code to catch the error when users enter a request for more than is available.

As I suggested, you would just need to include some code that would catch that entry error

Dave

 

[Maklil]

ok I will try myself but where put it?
in the end of the code?

 

[dmt32]

You would use one of the available event codes to check user entry

Untested but something like this may do what you want

Place in your userforms code page

Private Sub TextBox4_AfterUpdate()
    Dim Net         As Long
    Net = Val(Me.TextBox3.Value)
    With Me.TextBox4
        If Val(.Value) > Net Then
            MsgBox "the only available QTY Is " & Net, 48, "Attention"
            .Value = Net
        End If
    End With
End Sub

Dave

 

[Maklil]

Awesome !
I can play with your code as I wanted .
thank you so much .

 

[dmt32]

welcome glad suggestion helps & appreciate your feedback

Dave