MsgBox Yes/No Excel VBA
Solution 1
The MsgBox
function returns a vbMsgBoxResult
value, which is an enum (and should be a Long
integer, not an Integer
).
You're calling it twice:
Dim question As Integer question = MsgBox("Unfortunately, the Database has no sources regarding " & Category & " in " & country & ". Would you like to broaden your search and see all sources regarding " & country & "?", vbYesNo + vbQuestion, "Empty Sheet") MsgBox question
Once to assign question
, and once to display question
- which at that point is going to contain either vbYes
(6) or vbNo
(7).
I would declare question As vbMsgBoxResult
to avoid ambiguities and get autocomplete/IntelliSense when you later use it. Actually, result
or answer
would be a better identifier - "question" sounds like the question itself, not the user's response.
Solution 2
just use
question = "Unfortunately, the Database has no sources regarding " & Category & " in " & country & ". Would you like to broaden your search and see all sources regarding " & country & "?."
would be enough.
In addition the if function can be
If Msgbox(Question) = vbYes then
...
End If
Don't call MsgBox twice
Solution 3
Remove MsgBox question
. This is unnecessarily creating a second message box populated with the value of question (6 or 7 depending on whether you chose yes or no, as eg vbYes has the return value 6).
franciscofcosta
Updated on July 09, 2022Comments
-
franciscofcosta almost 2 years
I have a
Yes/No MsgBox
in my VBA script that returns a question for the user to answer. Whenever the "Yes" or "No" buttons are pressed, besides the script running its respective code, anotherMsgBox
with the numbers "6" or "7" pops up. How do I disable this secondMsgBox
?Here's my code:
Dim question As Integer question = MsgBox("Unfortunately, the Database has no sources regarding " & Category & " in " & country & ". Would you like to broaden your search and see all sources regarding " & country & "?", vbYesNo + vbQuestion, "Empty Sheet") MsgBox question If question = vbYes Then Sheets("Results").Range("D6").ClearContents Sheets("Results").Range("D7").ClearContents Category = Sheets("Results").Range("D6").Value Else Sheets("Results").Range("D5").ClearContents Sheets("Results").Range("D6").ClearContents Sheets("Results").Range("D7").ClearContents Exit Sub End If
-
Mathieu Guindon over 7 yearsYup. There's actually no need to have a variable for the return value if it's only going to be used once.