Excel In Your Office

We saw about MsgBox in our previous post and now we know how to display this box to user with various option buttons and text formats. Now let’s see few other scenarios and continue with how we can capture user response to proceed with next steps.

Your prompt in the box can be approximately 1024 characters (more or less depending on characters you use). Your message box size will automatically adjust to the prompt length and text will also be wrapped automatically to go to next line. But what if you want to enter more than one line, breaking at where you want instead of waiting VBA to wrap it?

Simple! You can use a carriage return i.e. Chr(13) or line feed i.e. Chr(10) or both together. You can simply use a VBA built-in constant vbCrLf (my choice) too to get this done.

Observe the below code and screenshots. First one is using vbCrLf and second one without it. See that I am using this constant with two concatenation operator (&) to join two lines. Also code should be in one line, it may appear split into 2 lines according to your browser settings)

Sub Test()
MyMsg = MsgBox("Macro finished execution!!!!" & vbCrLf & "Do you want to proceed???", vbYesNo)
End Sub

Sub Test()
MyMsg = MsgBox("Macro finished execution!!!! Do you want to proceed???", vbYesNo)
End Sub

Note: You cannot easily change the font and button placement in these message boxes. Best alternative is to create your own message box using UserForms rather than using standard. Eventually, we will see about that.

Another Example Using Variables

We saw about variables in one of the previous posts, so I thought it will be ideal to show one example of message box using them. Consider this one as bonus.

Sub Test()
Dim PromptMsg, ButtonStyle, MyTitle, MyString
PromptMsg = "Do you want to continue ?"    ' Define your prompt
ButtonStyle = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
MyTitle = "Provide a response"    ' Define title.
Response = MsgBox(PromptMsg, ButtonStyle, MyTitle) 'Show the box
End Sub

Post User Response

Ok. We displayed a nice and cozy message box to the user and he/she clicked on a button. What to do next? Macro pauses till the time this message box is displayed to user. When the user clicks on a button in the box, next line of the code will be executed. We saw that MsgBox returns a value based on the options selected by the user. We will use that to perform next actions. For your ready reference, I am again producing the possible values returned by MsgBox here.

We will extend the above example further to do something. Add the below code just before End Sub in earlier code. I made vbYes bold in below code to catch your eyes; however VBA wouldn't show it in bold. Instead of vbYes, you can also use a number 6 (above table) and so on.

If Response = vbYes Then 'Checking if user clicked Yes
MsgBox ("You clicked yes") 'Write what you want to do if user clicked yes
Else
MsgBox ("You clicked no") 'If Yes is not clicked, that means No is clicked, write what you want to do here
End If

We are using If-Then-Else-End If statements to see what the user's response is and displaying another message box for each option (Yes or No) as in below pictures.