Return Value from MsgBox
 

If the string from the input box is not found, I want to rerun the code if
the retry button is selected from the message box.

How do I fix the code below to make this work?

Sub FindStr_sub()
Dim s As String, t As String, q As String
Dim r As Range
Dim response As Byte
t = Chr(10) & Chr(10)
s = Application.InputBox("Enter the Word: ", "Locate 'Word'")
DoOver:
For Each r In ActiveSheet.UsedRange
With r
If InStr(1, .Value, s) 0 Then
q = q + .Address & " " & .Value & t
End If
End With
Next
If q < "" Then
MsgBox "Found the Word in Cell(s)" & t & q, , "Word Location"
Else
MsgBox "Did not find the Word " & t & Chr(34) & s & Chr(34),
vbRetryCancel, "Word Location"
If response = vbRetry Then
GoTo DoOver
Else
End If
End If
End Sub

Return Value from MsgBox
 

Dim response as Variable, then make it = to the msgbox:

Sub FindStr_sub()
Dim s As String, t As String, q As String
Dim r As Range
Dim response 'As Byte
t = Chr(10) & Chr(10)
s = Application.InputBox("Enter the Word: ", "Locate 'Word'")
DoOver:
For Each r In ActiveSheet.UsedRange
With r
If InStr(1, .Value, s) 0 Then
q = q + .Address & " " & .Value & t
End If
End With
Next
If q < "" Then
MsgBox "Found the Word in Cell(s)" & t & q, , "Word Location"
Else
response = MsgBox("Did not find the Word " & t & Chr(34) & s & Chr(34), _
vbRetryCancel, "Word Location")
If response = vbRetry Then
GoTo DoOver
Else
End If
End If
End Sub

Mike F
"Hal" wrote in message
...
If the string from the input box is not found, I want to rerun the code if
the retry button is selected from the message box.

How do I fix the code below to make this work?

Sub FindStr_sub()
Dim s As String, t As String, q As String
Dim r As Range
Dim response As Byte
t = Chr(10) & Chr(10)
s = Application.InputBox("Enter the Word: ", "Locate 'Word'")
DoOver:
For Each r In ActiveSheet.UsedRange
With r
If InStr(1, .Value, s) 0 Then
q = q + .Address & " " & .Value & t
End If
End With
Next
If q < "" Then
MsgBox "Found the Word in Cell(s)" & t & q, , "Word Location"
Else
MsgBox "Did not find the Word " & t & Chr(34) & s & Chr(34),
vbRetryCancel, "Word Location"
If response = vbRetry Then
GoTo DoOver
Else
End If
End If
End Sub

Return Value from MsgBox
 

Thanks Mike,

That and relocating the DoOver label has it working now.

Originally, I think I tried setting response = msgbox but it did not work
becase I left the ( ) out of the msgbox statment.

Regards,

"Mike Fogleman" wrote:

<snip

Return Value from MsgBox
 

download mztools. it has a msgbox builder and a lot of other things

http://www.mztools.com/v3/mztools3.aspx

--


Gary


"Hal" wrote in message
...
If the string from the input box is not found, I want to rerun the code if
the retry button is selected from the message box.

How do I fix the code below to make this work?

Sub FindStr_sub()
Dim s As String, t As String, q As String
Dim r As Range
Dim response As Byte
t = Chr(10) & Chr(10)
s = Application.InputBox("Enter the Word: ", "Locate 'Word'")
DoOver:
For Each r In ActiveSheet.UsedRange
With r
If InStr(1, .Value, s) 0 Then
q = q + .Address & " " & .Value & t
End If
End With
Next
If q < "" Then
MsgBox "Found the Word in Cell(s)" & t & q, , "Word Location"
Else
MsgBox "Did not find the Word " & t & Chr(34) & s & Chr(34),
vbRetryCancel, "Word Location"
If response = vbRetry Then
GoTo DoOver
Else
End If
End If
End Sub