Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / September 2007

Tip: Looking for answers? Try searching our database.

Return Value from MsgBox

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Hal - 16 Sep 2007 15:34 GMT
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
Mike Fogleman - 16 Sep 2007 16:09 GMT
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
> 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.
[quoted text clipped - 26 lines]
>  End If
> End Sub
Hal - 16 Sep 2007 16:32 GMT
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,

<snip>
Gary Keramidas - 16 Sep 2007 16:37 GMT
download mztools. it has a msgbox builder and a lot of other things

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

Signature

Gary

> 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.
[quoted text clipped - 26 lines]
>  End If
> End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.