In this tutorial, we will explore how to utilize variables with the msgbox and inputbox functions to create dynamic dialog boxes. The code provided below will run in Microsoft 365 (or earlier) Excel, PowerPoint and Word applications.
Using variables with MsgBox and InputBox can make your VBA dialog interactions more dynamic and user-friendly. In this example we will use an Input Box to get a user input about the test score. Then, our program will evaluate the input and determine whether the user passed the test or failed.
Add a module to your Excel or Word
In Excel, Word or PowerPoint, hit the Developer tab.
Then, press the Visual Basic button – or hit the ALT +F11 combination.
Then right click the VBAProject entry, select Insert and then Module. This adds a VBA module, in which you will paste the code below.
Write your VBA code
In the macro code below, i used the studentScore variable alongside with an input box to capture the student score. We then use that variable to determine the pass or fail result.
Here’s is the code you can use – obviously, feel free to enhance it as needed:
Sub DisplayPassFailBasedOnScore()
Dim studentScore As Integer
studentScore = InputBox("Enter your test score:", "Score Input")
' Check if the studentScore is greater than 80
If IsNumeric(studentScore) Then
If studentScore >= 80 Then
MsgBox "You have passed the test.", vbInformation,"Your Grade Result"
Else
MsgBox "You should review the material and take another test.", vbExclamation,"Your Grade Result"
End If
Else
MsgBox "This program needs enter a numeric input. Please enter your test score.", vbCritical, "Invalid Input"
End If
End Sub
Run you VBA program
Save your module, then hit ALT + F8 and select your macro named DisplayPassFailBasedOnScore.
Once your program executes, enter the user score and see your results!
Here’s you input box:
I passed the test 🙂