Message box error when searing a value

[anthonymedz]

Hi guys,

Can you help me on my project. I wanted to search the exact product and if does not match a message box error will appear. I have a script wherein when i search a product and when it was empty a message box will appear saying "Insert Product" then when i search a product with a wrong product or wrong spelling a message will appear "Wrong product" else if the Product is correct it will proceed on that Product sheet. I have a list of a Product in Range B7:B27. Thank you.

Private Sub CommandButton3_Click()


If TextBox1 = "" Then
MsgBox "Insert Product", vbCritical, "Product is blank"
'TextBox1.SetFocus
Exit Sub


ElseIf TextBox1 <> Range("B7:B27").Value Then
MsgBox "Wrong Product Description", vbCritical, "Product Description is wrong"
'TextBox1.SetFocus
Exit Sub
End If

Sheets(Me.TextBox1.Value).Visible = True
Sheets(Me.TextBox1.Value).Select
End sub

 

[silentwolf]

HI,
why don't you do something like this

rngMyRange as Range

set rngMyRange = range("B7:B27")

then reference to the rngMyRange

TextBox1<>rngMyRange.value then

not testet .

HTH

 

[Fluff]

You cannot test a value against a range like that, try

Private Sub CommandButton3_Click()
   Dim Fnd As Range

   If TextBox1 = "" Then
      MsgBox "Insert Product", vbCritical, "Product is blank"
      'TextBox1.SetFocus
      Exit Sub
   End If
   
   Set Fnd = Range("B7:B27").Find(TextBox1, , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then
      MsgBox "Wrong Product Description", vbCritical, "Product Description is wrong"
      'TextBox1.SetFocus
      Exit Sub
   End If
   
   Sheets(Me.TextBox1.Value).Visible = True
   Sheets(Me.TextBox1.Value).Select
End Sub

 

[anthonymedz]

You cannot test a value against a range like that, try

Private Sub CommandButton3_Click()
   Dim Fnd As Range

   If TextBox1 = "" Then
      MsgBox "Insert Product", vbCritical, "Product is blank"
      'TextBox1.SetFocus
      Exit Sub
   End If
   
   Set Fnd = Range("B7:B27").Find(TextBox1, , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then
      MsgBox "Wrong Product Description", vbCritical, "Product Description is wrong"
      'TextBox1.SetFocus
      Exit Sub
   End If
   
   Sheets(Me.TextBox1.Value).Visible = True
   Sheets(Me.TextBox1.Value).Select
End Sub

Hi!

This is great! It works.. thanks for your help. And also silentwolf. You are all amazing..

 

[Fluff]

You're welcome & thanks for the feedback