Locked lesson.

Upgrade

About this lesson

Forcing users to enter data when requested.

Exercise files

Download this lesson’s exercise file.

Forcing User Input.xlsm
23.9 KB

Quick reference

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

Instructions

Method overview

  • 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

Sub Sample()
    Dim sAnswer As String
    Dim lTry As Long
    Dim lMaxTries As Long

    'Set max attempts user should have
    lMaxTries = 3

    Do
        '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
                Exit Sub
            Else
                'No. Let's let the user try again
            End If
        End If

        'Prompt for user’s input
        sAnswer = CStr(InputBox("Message prompt"))
    Loop Until sAnswer <> ""

    Debug.Print sAnswer
End Sub

Hints & tips

  • The CStr function can be used to convert inputs to strings for comparison
Login to download
  • 00:04 The next question we might be asking is, how do we force a user to enter something
  • 00:09 to actually stop them from just clicking the Cancel button and going away?
  • 00:14 And the secret to doing that is to actually use a Do loop in order to
  • 00:17 make this work.
  • 00:18 So what you'll see here is if I go over to click on the button to, say,
  • 00:21 Enter Department, it comes up and says, what department?
  • 00:25 If I go and throw in something and say OK, it says, thanks, we can now proceed.
  • 00:30 But if I go back in Enter Department and I cancel, it comes back and says,
  • 00:33 hey, you didn't enter a department.
  • 00:35 Please try again.
  • 00:36 And I'm gonna cancel.
  • 00:37 And I'm gonna cancel one more time, and it'll actually stop.
  • 00:41 The reason being is because I set it up to only try three times.
  • 00:44 I figure if a user is trying to get out the badly,
  • 00:46 they probably really don't want to be there.
  • 00:49 Now, in this case, what actually ends up happening?
  • 00:52 Well, we setup our MaxTrys, and then we have a Do loop.
  • 00:55 And let's take a look at the first iteration.
  • 00:57 We open it up, we increase our counter by 1.
  • 01:00 And then we'll check to see if the counter is greater than 1.
  • 01:03 So, obviously, the first time through, it's not.
  • 01:05 And it prompts for the question, please enter the department.
  • 01:09 You'll also notice something a little bit different here is that we have this
  • 01:13 little thing in here, C String.
  • 01:15 C String is a function that actually allows you to convert the value
  • 01:20 that's being returned into a text string.
  • 01:23 This can be useful if you end up getting back something that's a different kind of
  • 01:28 a variant.
  • 01:28 Something that maybe comes back as a false or
  • 01:32 something like that, that needs to be converted into text.
  • 01:35 So C String is something that's useful.
  • 01:36 It's not 100% necessary here, but
  • 01:38 I did wanna make sure that I got it covered at least once.
  • 01:41 Now, it then takes the answer and says,
  • 01:46 Loop Until the answer does not equal quote quote.
  • 01:51 And, of course, everything between the quote is what we're looking for,
  • 01:53 which is nothing.
  • 01:54 So if there was a value, like there was the first time, it goes to the MsgBox,
  • 01:57 it says, thanks, we can proceed.
  • 01:59 And then you would think that the rest of the macro would run.
  • 02:02 If it does not have a value, though, it says Loop because it doesn't equal.
  • 02:06 We need to loop until it doesn't equal blank.
  • 02:08 So it's gonna back to the loop counter, increase the loop count by 1.
  • 02:13 The first time into the loop is 1, now it's 2.
  • 02:15 And then it says, is it greater than 1?
  • 02:17 If it's greater than 1, it will check to see whether or
  • 02:20 not we've actually hit the MaxTrys threshold.
  • 02:22 If it does, we get out.
  • 02:23 Otherwise, it says, hey, you didn't enter your department.
  • 02:26 Please try again.
  • 02:28 It exits the If test, and then asks the question again.
  • 02:34 So let's go step through this and see how this actually goes.
  • 02:37 So it sets the MaxTrys.
  • 02:38 We'll go in, we'll increase the counter.
  • 02:40 So you can see lCount is at 1.
  • 02:44 So it skips over the If statement.
  • 02:46 Here we are, let's test the department.
  • 02:48 So we'll go in and we'll say let's cancel.
  • 02:50 And it says, I need to loop until the answer doesn't equal this.
  • 02:52 Because answer equals quote quote.
  • 02:55 So step into this, increase loop counter.
  • 02:59 We'll check, aha, is it greater than the MaxTrys?
  • 03:03 Max try counter is at 3.
  • 03:04 And currently, we've been through the loop twice, so no.
  • 03:08 Then it says, hey, in that case,
  • 03:10 we'll feed the user back some information to let them know that they can try again.
  • 03:15 And then we'll prompt them for their department.
  • 03:19 So this time, if I were to enter 10, you'll notice that it's supposed to
  • 03:24 loop until the answer doesn't equal quote quote.
  • 03:26 It no longer does.
  • 03:27 So at this point, it says, thanks, we can proceed.
  • 03:31 Had that not happened, if we were to run it again and we hit Cancel.
  • 03:36 Actually, I'll need a breakpoint in here.
  • 03:38 Cancel and Cancel.
  • 03:40 Let me just go and stop this right around this part here.
  • 03:44 So on this case here, had we gone and broken into it, and okay,
  • 03:47 the first time through, it asks for a department.
  • 03:49 We'll say Cancel.
  • 03:50 It's gonna repeat the loop and increase the counter.
  • 03:53 So MaxTrys is at 2.
  • 03:54 It's gonna tell us we didn't enter a department.
  • 03:59 Enter department, we'll cancel again.
  • 04:01 So it's coming through.
  • 04:02 So now, we've increased the count by 1 again.
  • 04:05 It's now at, is it greater than MaxTrys?
  • 04:08 No, so it will ask one more time.
  • 04:13 Didn't enter a department.
  • 04:15 Say, End If, it will ask for department.
  • 04:17 I'm gonna cancel again.
  • 04:19 At this point, we're gonna loop back up to the lCount.
  • 04:22 We're gonna increase the lCount.
  • 04:23 It's now at 4, the MaxTrys was 3.
  • 04:26 So at this point, is lCount greater than 1?
  • 04:29 Yes, it is.
  • 04:30 Has it exceeded the MaxTrys?
  • 04:32 Yes, it has.
  • 04:33 So let's end the routine completely.
  • 04:35 We'll hit Exit Sub.
  • 04:35 That's gonna take us to the End Sub line and out.
  • 04:38 So this is a way that we can actually loop someone in there.
  • 04:41 It is a handy thing to at least give somebody an escape route.
  • 04:44 Because if you do send them into a thing where they basically have no choice.
  • 04:47 And believe me, there definitely are scenarios where you need to do that,
  • 04:50 where you don't wanna give them any escape route.
  • 04:53 But for the most part in these kind of things, if a user tries canceling five or
  • 04:56 six times, they probably really do want to cancel.
  • 04:58 So it's a good idea to have something that'll actually allow you to kick them
  • 05:01 right out of the routine completely.

Lesson notes are only available for subscribers.

Gift this course