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
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
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
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!
- Posts
- 16
- Registration date
- Thursday July 18, 2019
- Status
- Member
- Last seen
- October 17, 2019
-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
- Posts
- 13036
- Registration date
- Monday June 3, 2013
- Status
- Moderator
- Last seen
- October 11, 2019
1473 -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:
Yea, I don't get it ! :-)
XD
Have FUN!