MsgBox Yes/No Excel VBA

13
votes

I have a Yes/No MsgBoxin 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, another MsgBox with the numbers "6" or "7" pops up. How do I disable this second MsgBox?

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
3
vbaexcelmsgbox
Well, you call MsgBox twice, don't you? – GSerg
Sorry, what do you mean? – franciscofcosta
Delete MsgBox question – Limak
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 suffice – Rosetta
One way to find errors like these is to run your code line by line. This is a powerful technical that is worth mastering. It will help you understand what the individual parts of your VBA are up to. – David Rushton

3 Answers

15
votes

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).

enter image description here

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.

8
votes

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

1
votes

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).