Message box error when searing a value

anthonymedz

Board Regular
Joined
Jan 30, 2015
Messages
69
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
You cannot test a value against a range like that, try
Code:
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
 
Upvote 0
You cannot test a value against a range like that, try
Code:
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.. :)
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top