Command button code like userform.show

[ipbr21054]

Evening.
I am using the code below.
After i have been told that the item exists or does not exist i click on OK and the message box closes.
I then need to click again on the command button3 to show me the box & type in another number.

What i would like is to be able to apply something like userform.show code so it opens again without me doing it each time.
The also have the option to close it manually myself once finished.

Private Sub CommandButton3_Click()'Modified  10/14/2018  8:12:52 AM  EDT
Application.ScreenUpdating = False
Dim ans As String
Dim rr As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
ans = InputBox("Enter search for value")
Set SearchRange = Range("A3:A" & LastRow).Find(ans)
If SearchRange Is Nothing Then MsgBox "The Part Number " & ans & " Is Not In The List", vbCritical: Exit Sub
rr = SearchRange.Row
MsgBox "The Part Number  " & ans & "  Can Be Found In Row  " & rr, vbExclamation
Application.ScreenUpdating = False
End Sub

 

[ipbr21054]

I think what we are missing from the code below is the second line which is the question.

Ive tried a few times but keep getting a syntax error

[COLOR=#333333][I]If MsgBox("The Part Number  " & ans & "  Can Be Found In Row  " & Fnd.Row, vbYesNo) = vbYes Then Set Fnd = Nothing Else Exit Sub[/I][/COLOR]

 

[Fluff]

All you need to do is add it in, in the sameway as it is on the previous msgbox.

 

[My Aswer Is This]

Glad to see Fluff is helping you.
I will move on to another post.
Take care

 

[ipbr21054]

I tried a few time but keep[ getting a syntax error.

I have it like this at present

If MsgBox("The Part Number  " & ans & "  Can Be Found In Row  " & Fnd.Row, vbLf & vbLf & "Search For Another", vbYesNo) = vbYes Then Set Fnd = Nothing Else Exit Sub

 

[ipbr21054]

Glad to see Fluff is helping you.
I will move on to another post.
Take care

@MAIT
You could take a look at a code you advised but this time i need to be able to print it.
The post is called Insert Photo Code

 

[ipbr21054]

Fluff,
I also see an issue with its search as its looking for partial numbers also.

If in cell 55 i have the number 123456 and i the search box i type 12 or 56 i am told it can be found in row 55
I think we need to look at a 100% match

 

[Fluff]

In that case change xlPart to xlWhole

 

[ipbr21054]

Fluff,
That now works great.

Looking at post #6 do you see why when i use it i get a syntax error ?
Im sure its just a character , & ) etc but i dont see where the issue is.

 

[Fluff]

a) As there is no code in post#6 I'm surprised you get a syntax error
b) If you are getting a syntax error why did you say "That now works great."

 

[ipbr21054]

Where did 6 come from,i meant 14 same thing ha ha.
Run time error 13 type mismatch


Anyway,here is what i have so far.

Private Sub CommandButton3_Click()   Dim ans As String
   Dim Fnd As Range
   Do Until Not Fnd Is Nothing
      ans = InputBox("Enter search for value")
      If ans = "" Then Exit Sub
      Set Fnd = Range("A:A").Find(ans, , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         If MsgBox("The Part Number " & ans & " Is Not In The List" & vbLf & vbLf & "Do you want to try again", vbYesNo) = vbNo Then Exit Sub
      Else
         If MsgBox("The Part Number  " & ans & "  Can Be Found In Row  " & Fnd.Row, vbLf & vbLf & "Search For Another", vbYesNo) = vbYes Then Set Fnd = Nothing Else Exit Sub
      End If
   Loop
End Sub

That works great was in respect to the search which is now looking for a 100% match where as before it was partial.