About this lesson
Forcing users to enter data when requested.
Download this lesson’s exercise file.Forcing User Input.xlsm
Forcing User Input
Forcing a user to input data into an InputBox or a MsgBox
When to use
When you need to force a user to make a choice or abandon the macro
- Create a variable to hold the user answer/input
- Create a variable to act as a counter
- Create a varaible to hold the maximum number of times to ask the user
Sample code for focing Inputbox entry
Dim sAnswer As String
Dim lTry As Long
Dim lMaxTries As Long
'Set max attempts user should have
lMaxTries = 3
'Log the attempt number
lTry = lTry + 1
'Check the attempt status
If lTry > 1 Then
'Has user tried max number of times?
If lTry > lMaxTries Then
'Yes, end the routine
'No. Let's let the user try again
'Prompt for user’s input
sAnswer = CStr(InputBox("Message prompt"))
Loop Until sAnswer <> ""
Hints & tips
- The CStr function can be used to convert inputs to strings for comparison
Lesson notes are only available for subscribers.