Excel VBA: How to capture MsgBox response

Question

I would like to perform the following tasks:

• write the message "Are you awake?" and display a question mark

• capture the response in the integer variable intR, put intR value in cell A2

• If the response is Yes, then write in cell A1 "Hurray"

• If the response is No, then write a message box with the text "ZZZZZZZZ"

• If the response is Cancel, then exit the sub

  Sub EX3_1_6MsgBoxFunction()
  
  Dim intR As Integer
  Dim TxtRng As Range
  Dim stra As String
  Dim stra2 As String
  
  'Have the message box display the buttons Yes, No and Cancel
  
  
  intR = MsgBox("Are you awake ? ", vbQuestion + vbYesNoCancel)
  intR = Range("a2")
  
  If intR = vbYes Then
  Range("a1") = "Hurray"
  
  'that means yes
  
  ElseIf intR = vbNo Then
  stra2 = MsgBox("ZZZZZZZZZZ")
  
  Else
  
  Range("a2") = intR
  
  
  End If
  
  End Sub
  

Answer 1

First of all welcome to StackOverflow. When you ask a question, please remember to specify where you have problems in your code.

• write the message "Are you awake?" and display a question mark

If I understand correctly, you don't just want a question mark to be displayed, but a choice of 3 possibilities, which are given by the following MsgBox's mode:

    intR = MsgBox("Are you awake ? ", vbQuestion + vbYesNoCancel)

This does display a MsgBox which requires an answer: "Yes" or "No" or "Cancel". It cannot be anything else which is why I removed the last bullet point in your question. The answer code is then mapped on an integer intR, that is correct.

• capture the response in the integer variable intR, put intR value in cell A2

As YowE3K pointed out, it should be Range("a2").Value = intR, not intR = Range("a2")

• If the response is Yes, then write in cell A1 "Hurray"
• If the response is No, then write a message box with the text "ZZZZZZZZ"
• If the response is Cancel, then exit the sub

You can use a Select Case here: think about it as an If with more than 2 possibilities:

Select Case intR
    Case 6 '<- According to the link I provided vbYes = 6
        Range("a1") = "Hurray"
    Case 7 '<- According to the link I provided vbNo = 7
        MsgBox "ZZZZZZZZ"
    Case 2 '<- According to the link I provided vbCancel = 2
        Exit Sub
End Select

You can write vbYes, vbNo and vbCancel instead of the integer. As pointed out by another user, the code is probably more readable that way.

Answer 2

You may try something like this.....

Sub EX3_1_6MsgBoxFunction()
Dim Ans As VbMsgBoxResult

'Have the message box display the buttons Yes, No and Cancel
Ans = MsgBox("Are you awake ? ", vbQuestion + vbYesNoCancel)

Select Case Ans
    Case vbYes
        Range("A1").Value = "Hurray"
        Range("A2").Value = "Yes"
    Case vbNo
        Range("A1").Value = "ZZZZZZZZZZ"
        Range("A2").Value = "No"
    Case Else
        Range("A1").Value = ""
        Range("A2").Value = "Calcel"
End Select

End Sub

Answer 3

Further to Noldor's answer, as a general rule, you shouldn't use Hungarian notation for your variable names, intR doesn't really tell you anything about what the variable is apart from it's type.

You should be able to infer the type from the context and the variable name should give you something more useful. I'd recommend rewriting your code like

Sub EX3_1_6MsgBoxFunction()
    Dim retVal As Integer

    'Ask the user whether they're awake
    retVal = MsgBox("Are you awake ? ", vbQuestion + vbYesNoCancel)

    'Switch on their answer
    Select Case retVal
        Case vbYes
            Range("A2") = "Hurray"
        Case vbNo
            MsgBox "ZZZZZZZ"
    End Select
End Sub

Note that you don't need to assign MsgBox to a variable, you can just call it with an argument (in this case "ZZZZZ") to prompt the user, this way you can eliminate your needless stra2 and stra. You also don't need to explicitly Exit Sub because the code will naturally exit after the Select Case or If statements.