Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

SHOW WARNING MESSAGE IN USERFORM

0

HI ,GUY 

HERE I HAVE WORKBOOK CONTAIN SHEET 1  WITH DATA  I WOULD WHEN I WRITE IN USER FORM  IN TEXTBOX3 THE QUNTITY MORE  WHAT  IS EXISTED THEN SHOW MESSAGE  FOR INSTANCE: IN SHEET1  THE QUNTITY MOUSE  300Q  WHEN I WRITE IN USERFORM IN TEXTBO3 400Q SHOW WARNING MESSAGE " THIS BRAND ONLY CONTAINS 300Q" 

I HOPE GIVE ME THE CODE 

ATTACHED FILE

Answer
Discuss

Discussion

Hello leopard, please don't use AllCaps in your post. AllCaps are understood as shouting and I, for one, don't like to be shouted at. I suggest you edit your post and give it a less aggressive tone.
Variatus (rep: 4889) Jun 4, '19 at 1:17 am
Seriously man.... ALL CAPS?!?!?!???!?!? Not cool!
don (rep: 1989) Jun 4, '19 at 11:57 am
Add to Discussion

Answers

0

Add the code below to your user form's code sheet.

Private Sub CommandButton1_Click()
    ' 05 Jun 2019

    Dim TbxNames() As String
    Dim Max As Long
    Dim Qty As Long
    Dim R As Long
    Dim i As Integer

    TbxNames = Split("TbxItem TbxBrand TbxQty")
    For i = 0 To UBound(TbxNames)
        With Controls(TbxNames(i))
            If Len(.Value) = 0 Then
                MsgBox "Please assign a value to textbox " & .Name, _
                       vbExclamation, "Missing entry"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i


    Qty = Val(TbxQty.Value)
    Max = BrandMaximum(TbxBrand.Text)
    If Max And Max < Qty Then
        MsgBox "Quantity exceeds maximum for " & TbxBrand.Value, _
               vbExclamation, "Excessive quantity"
        TbxQty.SetFocus
    Else
        R = Cells(Rows.Count, "A").End(xlUp).Row + 1
        For i = 0 To UBound(TbxNames)
            With Controls(TbxNames(i))
                Cells(R, i + 1).Value = .Value
                .Value = ""
            End With
        Next i
    End If
End Sub

Private Function BrandMaximum(Brand As String) As Long
    ' 05 Jun 2019

    ' This function must refer to a list of brands and related
    ' maximum quantities yet to be created,
    ' and return the quantity found for the brand in TbxBrand

    ' I suggest to let the function return zero if there is no maximum

    BrandMaximum = 300              ' provisional, unqualified function return
End Function

It includes a function that would determine the maximum quantity permissible for each brand according to a list you might have in a separate worksheet of the same workbook. This function currently just returns "300".

The attached workbook demonstrates the code.

Discuss

Discussion

thanks  for your answering  but what i would when write textbox 3  show me the quntity availabe when is exsecced  evrey  time choose the brand 
leopard (rep: 88) Jun 5, '19 at 5:33 pm
That's another question. Please accept this answer if it helps you. Then start a new thread with your next question.
Variatus (rep: 4889) Jun 5, '19 at 9:03 pm
but, i explain this point the end of discussing you should check the comment
leopard (rep: 88) Jun 6, '19 at 7:47 am
I see.
Variatus (rep: 4889) Jun 6, '19 at 10:32 pm
what you sugget
leopard (rep: 88) Jun 7, '19 at 5:45 am
Add to Discussion


Answer the Question

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