Previous Topic Next Topic

Adjust code show message about brand

Closed
abdelfatah_0230 Posts 73 Registration date Thursday July 18, 2019 Status Member Last seen July 23, 2022 - Oct 2, 2019 at 02:56 PM
abdelfatah_0230 Posts 73 Registration date Thursday July 18, 2019 Status Member Last seen July 23, 2022 - Oct 3, 2019 at 02:45 AM
hi
i would helping to adjust my code i have data in my sheet a,b ,c columns a= item , b= brand, c= quantity and userform textbox1,2,3
when i fill the data in textbox2 the brand and textbox 3 the quantity and press enter in textbox3 should show message "available is 330 or 500...etc" if what i fill the value in texbox3 more than what contains in column c "

my code when i choose the brand an fill value it continues give me the same value in the first brand in column c 


Private Sub TextBox3_AfterUpdate()
Dim myCell As Range

    For Each myCell In Range("c2:c4")
        If TextBox3.Value > myCell Then
            MsgBox " the availble brand is " & myCell.Value, vbOKOnly
            Exit Sub
        End If
    Next myCell
End Sub

2 replies

Answer 1 / 2
Blocked Profile
Oct 2, 2019 at 04:02 PM
You are comparing a value to a range. You need to compare a value to a value, as in:

Textbox3.value < mycell.value.

Have fun!
0
abdelfatah_0230 Posts 73 Registration date Thursday July 18, 2019 Status Member Last seen July 23, 2022
Oct 2, 2019 at 04:15 PM
you seem don't understand me look this picture


it supposes showing the number 300 in message box not 200 it always appears number 200 in every time choose the brand
this is my file
https://ufile.io/8nox6jtt
0
Blocked Profile
Updated on Oct 2, 2019 at 07:01 PM
You are right, I don't understand!

Here is a screenshot of what is returned with my altered code!




By the way, which is which? Is textbox3 the Brand, or the Quantity? Your code is comparing to the Quantity. If you wanted to show the brand, you have to add in a cell for the Brand, because you have only linked to the Quantity (C column according to your example).

As in: 

Private Sub yup()
Dim a, BrandcellValue, therange
Dim myCell As Range
a = 2
For Each myCell In Range("c" & a & ":c4")
therange = "B" & a
BrandcellValue = ThisWorkbook.Worksheets("Sheet1").Range(therange).Value
a = a + 1
If ThisWorkbook.Worksheets("Sheet1").Range("e3").Value < myCell.Value Then
MsgBox " the availble brand is " & BrandcellValue, vbOKOnly
Exit Sub
End If
Next myCell
End Sub


Yea, I don't get it ! :-)

XD

Have FUN!
0
Answer 2 / 2
abdelfatah_0230 Posts 73 Registration date Thursday July 18, 2019 Status Member Last seen July 23, 2022
Oct 2, 2019 at 10:02 PM
i ment textbox3 the Quantity not brand with column c i change this line to :
therange = "B" & a it show the quantity but there is problem it shows the quantity of number 200 every time i choose the new brand!
0
abdelfatah_0230 Posts 73 Registration date Thursday July 18, 2019 Status Member Last seen July 23, 2022
Oct 3, 2019 at 02:45 AM
sorry for correction therange = "c" not b
0