Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveX Control Return Keypress MsgBox Loop | Excel Programming | |||
Return Address of Blank Cells in MsgBox | Excel Programming | |||
How to put a 'return' in Msgbox? | Excel Programming | |||
Msgbox text hard return | Excel Programming | |||
MsgBox carriage return | Excel Programming |