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

 

[My Aswer Is This]

I was the one who wrote this code. But it has nothing to do with a UserForm

So are you saying if the search value is not found you want the Inputbox to Open back up so you can search for another value?

 

[Fluff]

Is this what you want

Private Sub CommandButton3_Click() 'Modified  10/14/2018  8:12:52 AM  EDT
   Dim ans As String
   Dim Fnd As Range
   Do Until Not Fnd Is Nothing
      ans = InputBox("Enter search for value")
      Set Fnd = Range("A:A").find(ans, , , xlPart, , , 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
         MsgBox "The Part Number  " & ans & "  Can Be Found In Row  " & Fnd.Row
      End If
   Loop
End Sub

 

[ipbr21054]

I was the one who wrote this code. But it has nothing to do with a UserForm

So are you saying if the search value is not found you want the Inputbox to Open back up so you can search for another value?

Hi,
I have a list of numbers to check.

So whether the code is there or not i would like the box to stay ready for the next number input.
I would also need to be able to close it myself at some stage.
@MAIT i understand it has nothing to do with a userform but that was my way of trying to explain what i require for the code to open itself again.


Fluff i will look at yours now for a test drive.

 

[ipbr21054]

Is this what you want

Hi,
No loop action works.
Its basically the same.
I click on OK and the box closes and stay closed unless i click the command button again

 

[Fluff]

How about

 

[dmt32]

Hi,
No loop action works.
Its basically the same.
I click on OK and the box closes and stay closed unless i click the command button again

hi,
see if this update to your code does what you want?

Private Sub CommandButton3_Click()
    Dim ans As Variant
    Dim msg As String
    Dim Response As VbMsgBoxResult
    Dim rr As Long
    Dim SearchRange As Range
    
top:
    Do
        ans = InputBox("Enter search for value", "Search")
'cancel pressed
        If StrPtr(ans) = 0 Then Exit Sub
    Loop Until Len(ans) > 0
    msg = "The Part Number " & ans
    Set SearchRange = Columns(1).Find(ans, LookIn:=xlValues, lookat:=xlWhole)
    If Not SearchRange Is Nothing Then
        rr = SearchRange.Row
        MsgBox msg & "  Can Be Found In Row  " & rr, vbExclamation, "Record Found"
    Else
'part not found retry
        Response = MsgBox(msg & " Is Not In The List", 21, "Not Found")
        If Response = vbRetry Then GoTo top
    End If
End Sub

Dave

 

[ipbr21054]

hi,
see if this update to your code does what you want?

Hi,
This works great but need some more code as its 50% done

It works like this.
I enter an unknown number
I see the msgbox retry or cancel
Cancel closes the msgbox & retry allows me to enter another number.

I now need the same for if the the number is present.
This would also allow me to close the msgbox but to also type another number to check.

 

[Fluff]

Seems I forgot to post the code in post#6

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, , , xlPart, , , 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, vbYesNo) = vbYes Then Set Fnd = Nothing Else Exit Sub
      End If
   Loop
End Sub

 

[ipbr21054]

This seems better.

I like the way if not found has the option of Do You Want To Try Again vbYesNo

So i need the same msgbox sort of for when a number is found,so like so.

The Part Number 12345 Can Be Found In Row 5
Do you wish to search for another ?
YES NO option.

These options will be the same as above,Yes shows box awaiting another number, No closes box.

Thanks very much.