Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Adjust code show message about brand [Closed]

Report
Posts
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021
 -
Posts
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021
 -
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

Answer 1 / 2

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!
Report
Posts
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021
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
Report
Blocked Profile
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!
Answer 2 / 2
Posts
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021
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!
Report
Posts
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021
sorry for correction therange = "c" not b
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!