need to make this userform not accept any number more than stock number

[mazan2010]

Hello all expert
i create this excel sheet that have two button for make the following action
1- Click here to add Stock Quantity
which will open form that allow enter data from manufacture to stockdatabase sheet
2-Click here to add Withdraw Quantity that allow enter data withdrawed or sold to withdrawdatabase sheet

the problem now i need when i open withdraw form nd enter value excess from corresponding data in stock , give me message that
the number enter in quantity is more than stock

for example in my example
in sheet Withdrawdatabase in row 10
have product Settling TSA batch 1111
and this batch quantity produced in stock was 450 from Stockdatabase in row 2
and in withdraw its sold for two locate before (row 2,3 ) (150, 300) and again in row 10 location 1 have 200
i need to prevent this to accept number more than stock


best regards

[davesexcel]

I think it would be in your xtBatchNo_Exit event.

Something like....

Private Sub TxtBatchNo_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim ws As Worksheet, sh As Worksheet
    Dim Rws As Long, Rng As Range, c As Range, lookRng As String

    Set ws = Worksheets("StockDatabase")
    Set sh = Worksheets("WithdrawDatabase")

    With ws
        Rws = .Cells(Rows.Count, "E").End(xlUp).Row
        Set Rng = .Range(.Cells(1, "E"), .Cells(Rws, "E"))
    End With

    lookRng = Me.TxtBatchNo
    Set c = Rng.Find(what:=lookRng, lookat:=xlWhole)

    If Not c Is Nothing Then
        MsgBox c.Offset(0, -1) - Me.txtQty.Value    'edit this to do what you need.
    Else: MsgBox "Not Found"
        Exit Sub
    End If


End Sub

[mazan2010]

its not working well because its allow to add value
please see my attached file in withdrawdatabase sheet

[mazan2010]

Do you can help me to not allow enter value more than available

[mazan2010]

and please i add new items in withdraw userform nameed ( available quantity)
i think it will be good idea that will show every time want to withdraw any quantity
how can make this not allow any value more than available

[mazan2010]

even by add helper column in any sheet