If..Then..loop not working for MsgBox

[WyvsEyeView]

I want to display a message box with Yes, No, Cancel buttons and execute different code depending on which button is clicked.

strMsg1 = "Click a button"
        
If MsgBox(strMsg1, vbYesNoCancel) = vbYes Then
  'code A here
ElseIf MsgBox(strMsg1, vbYesNoCancel) = vbNo Then
  'code B here
ElseIf MsgBox(strMsg1, vbYesNoCancel) = vbCancel Then
  'do nothing
End If

What happens is this...user clicks Yes and code A executes. User clicks No and message redisplays, user clicks No again and code B executes. User clicks Cancel, message redisplays, user clicks Cancel again, message redisplays, user clicks Cancel a third time and message finally disappears. It's as if the If...Then statement has to loop twice to get to the second option and three times to get to the third option. Why is this happening...I don't follow the logic.

[Stewart Ross]

Hi WyvsEyeView. It's happening because you are repeatedly calling the messagebox function in each test of your IF. Define a variable to hold the result of your message box function and then test that variable with your IF, like this:

Dim UserChoice as vbMsgBoxResult
UserChoice = MsgBox(strMsg1, vbYesNoCancel)
If UserChoice = vbYes Then
  'code A here
ElseIf UserChoice = vbNo Then
  'code B here
End If

Note that if the user's choice is neither Yes nor No it must be Cancel - testing for that value is redundant. As your code was to do nothing for Cancel I've removed the branch altogether.

By the way, an If..Then structure like this is not a loop, even though your post is titled as such! It can be contained in one, but that is not what you have posted...

-Stewart

[WyvsEyeView]

Perfect! Makes absolute sense once you understand what's happening. Thank you, Stewart. I'm going to look up "loops" to be sure I understand what is and isn't one :)

[NeoPa]

I would also recommend using the Select Case ... construct in place of If ... ElseIf ... End If.

This could also be another way to avoid your problem, although the fundamental reasoning is now understood so that's less important.

[ADezii]

Sorry, but I think NeoPa's point is one worth mentioning:

Dim strMsg1 As String
strMsg1 = "Click a button"

Select Case MsgBox(strMsg1, vbYesNoCancel)
  Case vbYes
    'code A here
  Case vbNo
    'code B here
End Select