MsgBox with Two Questions

[Paul Black]

Hi everybody,

I have the code ...

Private Sub Test()

Dim Question1 As Variant
Dim Question2 As Variant

Question1 = MsgBox( _
"Text." & vbNewLine & _
Worksheets("W").Range("B3").Value & vbNewLine _
& vbNewLine & _
"Click YES if either of the following are TRUE :- " & vbNewLine & _
"(1) Text." & vbNewLine & _
"(2) Text." & vbNewLine & _
" (Text)" & vbNewLine _
& vbNewLine & _
"Click NO if either of the following are TRUE :-" & vbNewLine & _
"(1) Text." & vbNewLine & _
"(2) Text.", vbYesNo, "Title")
If Question1 <> vbYes Then
Application.Goto Reference:=Sheets("W").Range("B3")
Exit Sub
End If

Question2 = MsgBox( _
"Text." & vbNewLine _
& vbNewLine & _
"Text." & vbNewLine & _
"Text.", vbYesNo, "Title")
If Question2 <> vbYes Then
Application.Goto Reference:=Sheets("W").Range("B5")
Exit Sub
End If
End Sub

.... which unfortunately does NOT goto the correct cell if the answer
is NO, it exits the "Test" Sub but still runs the "Main" Sub instead
of exiting the "Main" Sub.
I call the "Test" Sub above from the beginning of the "Main" Sub.
It appears to exit the "Test" Sub but it does NOT exit the "Main"
Sub.
Any ideas will be greatly appreciated.

Thanks in Advance.
All the Best.
Paul

 

[Dave Peterson]

So you're calling Test from Main?

If yes, then maybe you can turn Test into a function so that it returns some
sort of indicator so that Main knows what happened there:

Option Explicit
Sub Main()

dim Resp as string 'matches what Test returns
resp = Test

if resp = "VbNo to question1" then
'do what you want
else
'do something else
end if
end sub

Private Function Test() As String

Dim Question1 As Variant
Dim Question2 As Variant
Dim myStr As String

myStr = ""
Question1 = MsgBox( _
"Text." & vbNewLine & _
Worksheets("W").Range("B3").Value & vbNewLine _
& vbNewLine & _
"Click YES if either of the following are TRUE :- " & vbNewLine
& _
"(1) Text." & vbNewLine & _
"(2) Text." & vbNewLine & _
" (Text)" & vbNewLine _
& vbNewLine & _
"Click NO if either of the following are TRUE :-" & vbNewLine &
_
"(1) Text." & vbNewLine & _
"(2) Text.", vbYesNo, "Title")
If Question1 <> vbYes Then
Application.Goto Reference:=Sheets("W").Range("B3")
myStr = "VbNo to question1"
Test = myStr
Exit Function
End If

Question2 = MsgBox( _
"Text." & vbNewLine _
& vbNewLine & _
"Text." & vbNewLine & _
"Text.", vbYesNo, "Title")
If Question2 <> vbYes Then
Application.Goto Reference:=Sheets("W").Range("B5")
Exit Function
End If
End Function

 

[Paul Black]

Thanks for the reply Dave,

If yes, then maybe you can turn "Test" into a Function so that it returns some
sort of indicator so that "Main" Sub knows what happened there.

You are quite right, the answer is "Yes".

The "Main" Sub works great. What I am trying to achieve is that two
questions need to be answered, and depending on those answers then
some action is taken.

If the answer to Question1 is "Yes" Then
Ask Question2
Else
If the answer to Question1 is "No" Then
Goto Sheet("W").Range("B3") and Exit the "Test" Sub or Function

If the answer to Question2 is "No" Then
Goto Sheet("W").Range("B5") and Exit the "Test" Sub or Function
Else
If the answer to Question2 is "Yes" Then
Continue to run the "test" Sub or Function

Thanks in Advance.
All the Best.
Paul

 

[Dave Peterson]

I don't understand.

If two questions must be answered, then if the user answers yes to Q1, then you
exit that function/sub. So two questions aren't asked/answered.

 

[Paul Black]

Thanks for the reply Dave,

If the answer to Question1 is "Yes" then it should then ask Question2
otherwise goto Worksheets("W").Range("B3"). It is only if the answer
to BOTH questions is "Yes" it needs to carry on and run the rest of
the "Main" program.

Thanks in Advance.
All the Best.
Paul

 

[Dave Peterson]

Function test() as string

dim Question1 as long
dim Question2 as long

'initialize the variables to no.
Question1 = vbno
question2 = vbno

Question1 = msgbox(..., buttons:=vbyesno)
if question1 = vbno then
application.goto....
else
question2 = msgbox(...,buttons=vbyesno)
if question2 = vbno then
application.goto ...
end if
end if

if question1 = vbyes _
and question2 = vbyes then
test = "BothAreYes"
else
test = "BothAreNotYes"
end if

end function

Then check that string in the calling procedure.

(untested, uncompiled)


Ask Question2
Else
If the answer to Question1 is "No" Then
Goto Sheet("W").Range("B3") and Exit the "Test" Sub or Function

If the answer to Question2 is "No" Then
Goto Sheet("W").Range("B5") and Exit the "Test" Sub or Function
Else
If the answer to Question2 is "Yes" Then
Continue to run the "test" Sub or Function