MsgBox "Choose one ", vbYesNoCancel, " Three Options. "

[Steved]

Helo from Steved

What have I done wrong please as I would like the below to cancel when I
push the cancel button. What is happening is that it continues onto the next
one when I push the cancel button, which I do not want it to do. I Thankyou.

MsgBox "Choose one ", vbYesNoCancel, " Three Options. "

Sub Schoolfind()
Dim res As String, saddr As String
Dim RgToSearch As Range, RgFound As Range
Dim secondValue As String
Set RgToSearch = ActiveSheet.Range("C:C")

res = Application.InputBox("Type School Number as 001,8.00 to find the
school you are looking for", _
"Find School", , , , , , 2)
If res = "False" Then Exit Sub 'exit if Cancel is clicked
res = Trim(UCase(res))
If res = "" Then Exit Sub 'exit if no entry and OK is clicked
If InStr(1, res, ",", vbTextCompare) = 0 Then
MsgBox "Invalid entry"
Exit Sub
End If
v = Split(res, ",")
res = Trim(v(LBound(v)))
secondValue = Trim(v(UBound(v)))
Set RgFound = RgToSearch.Find(what:=res, _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If RgFound Is Nothing Then
MsgBox "School " & res & " not found."
Exit Sub
Else
saddr = RgFound.Address
Do
If RgFound.Offset(0, 1).Text = secondValue Then
Application.Goto Reference:= _
RgFound.Offset(0, -1).Address(True, True, xlR1C1)
' commenting out the next line should do it
Sub Schoolfind()
Dim res As String, saddr As String
Dim RgToSearch As Range, RgFound As Range
Dim secondValue As String
Set RgToSearch = ActiveSheet.Range("C:C")

res = Application.InputBox("Type School Number as 001,8.00 to find the
school you are looking for", _
"Find School", , , , , , 2)
If res = "False" Then Exit Sub 'exit if Cancel is clicked
res = Trim(UCase(res))
If res = "" Then Exit Sub 'exit if no entry and OK is clicked
If InStr(1, res, ",", vbTextCompare) = 0 Then
MsgBox "Invalid entry"
Exit Sub
End If
v = Split(res, ",")
res = Trim(v(LBound(v)))
secondValue = Trim(v(UBound(v)))
Set RgFound = RgToSearch.Find(what:=res, _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If RgFound Is Nothing Then
MsgBox "School " & res & " not found."
Exit Sub
Else
saddr = RgFound.Address
Do
If RgFound.Offset(0, 1).Text = secondValue Then
Application.Goto Reference:= _
RgFound.Offset(0, -1).Address(True, True, xlR1C1)
' commenting out the next line should do it
MsgBox "Choose one ", vbYesNoCancel, " Three Options. "
' Exit Do
End If
Set RgFound = RgToSearch.FindNext(RgFound)
Loop While RgFound.Address <> saddr

End If
If RgFound.Offset(0, 1).Text <> secondValue Then
MsgBox "School Not Found"
End If
End Sub

' Exit Do
End If
Set RgFound = RgToSearch.FindNext(RgFound)
Loop While RgFound.Address <> saddr

End If
If RgFound.Offset(0, 1).Text <> secondValue Then
MsgBox "School Not Found"
End If
End Sub

 

[OssieMac]

Remove double quotes around False

If res = "False" Then Exit Sub

should be
If res = False Then Exit Sub

 

[Rick Rothstein]

This line of your code...

If res = "False" Then Exit Sub 'exit if Cancel is clicked

says, "Was the TEXT string 'False' returned from the InputBox, not was the
Boolean False value returned". Remove the quote marks to test the res
variable against the Boolean value...

If res = False Then Exit Sub 'exit if Cancel is clicked

 

[OssieMac]

That was the input box error that I found. I should have read your question
more carefully. The following code is an example of how to use MsgBox with
vbYesNoCancel.

In lieu of GoTo you can also use Call WhateverSub

Sub test()
Dim Response

Response = MsgBox("Choose one ", vbYesNoCancel, " Three Options. ")

Select Case Response
Case vbYes
MsgBox "Answer is Yes"
GoTo codeForYes
Case vbNo
MsgBox "Answer is No"
GoTo codeForNo
Case vbCancel
MsgBox "Anbswer is Cancel"
Exit Sub
End Select


codeForYes:
'required code here
GoTo pastNocode

codeForNo:
'required code here

pastNocode:

End Sub