Adjust code show message about brand

Posts
16
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 17, 2019
- - Latest reply: abdelfatah_0230
Posts
16
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 17, 2019
- 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
See more 

2 replies

Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1473
0
Thank you
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!
abdelfatah_0230
Posts
16
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 17, 2019
-
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
ac3mark
Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1473 -
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!
Respond to ac3mark
Posts
16
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 17, 2019
0
Thank you
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!
abdelfatah_0230
Posts
16
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 17, 2019
-
sorry for correction therange = "c" not b
Respond to abdelfatah_0230