Collecting Feedback from a VBA InputBox

Quick reference

• 00:05 In this video, we're going to look at another way to grab user input.
• 00:09 But this time, we're not just gonna be taking the output of a button click.
• 00:14 What we're gonna do is, we're gonna give the user the option to actually provide
• 00:17 information by typing it in.
• 00:19 And to do that, we use the VBA input box.
• 00:22 You'll notice in this case here that we have a dimension, the variable for
• 00:26 sQuestion.
• 00:27 And sQuestion has been assign to capture the details of the input box for
• 00:31 what department are you in.
• 00:33 Just for reference, the options that we actually have to work with here,
• 00:37 if I highlight the last parenthesis, and just hit my space,
• 00:39 you'll see the Tool tip pops up.
• 00:42 And it shows me that I've got my prompt, which is what department are you in?
• 00:45 Optionally, I can provide a title.
• 00:47 So we could go back and we could say, all right, let's put in, hello.
• 00:51 We could provide a default value, so in this case here,
• 00:55 I could say something like Accounting.
• 00:58 And then optionally, I can keep on adding more items to this, I could
• 01:03 control the x position and the y position, so where it shows up on the screen.
• 01:07 I won't get into help files or context, but
• 01:09 key part here is that you can provide these, but they're optional.
• 01:13 They don't necessarily need to be there, but I'll show you just for
• 01:15 fun what these actually look like.
• 01:18 Once we take the actual, or take the input from the message box, or input box,
• 01:22 rather, at that point I'm gonna force it to lower case using LCase, and
• 01:27 that's because text comparisons in VBA are case sensitive.
• 01:31 And then we're gonna put it through our Select Case statement in order to actually
• 01:34 see what happens.
• 01:35 So at this point, I can run my routine.
• 01:38 You'll notice that it's prompted with Accounting.
• 01:40 I'm gonna say hey, you know what?
• 01:41 I'm actually in the packaging department, I'm gonna say OK.
• 01:43 And then it says, hey, I've never heard of that.
• 01:46 If I were to go back and say, what department are you in?
• 01:49 Accounting, we'll say OK, and it says, hey, that's really cool.
• 01:53 Again, if I were to go and knock these guys off, and run it again, you'll notice
• 01:58 that we don't actually get prompted with Accounting, which is important in this
• 02:03 case, because we don't really want to see our answers if it's not necessary.
• 02:08 So never heard of a department that's cancelled because there's no department.
• 02:12 Okay, now where's the practical use for some of this stuff?
• 02:16 You know what? I don't know if you
• 02:17 ever tried to set up something that logs information.
• 02:19 But one of the challenges in Excel is when you actually try and use a now function or
• 02:24 a today function.
• 02:25 In order to do that, it's volatile.
• 02:26 So every time you open the workbook up, it ends up changing it,
• 02:29 which isn't really useful.
• 02:31 So VBA is a perfect way to actually be able to give somebody the option to click
• 02:34 a button, and actually have it prompt for their name and
• 02:37 log their date and time that they actually filled something in.
• 02:40 So if we look at this particular routine,
• 02:42 you'll see that we've set up a variable for a range which could be a cell.
• 02:47 We have the sEmployee string,
• 02:48 which is gonna be assigned from an input box that says, what's your name?
• 02:52 And then we set the range.
• 02:54 And this one's a little bit tricky.
• 02:55 You'll notice we've got worksheet sheet one, and we have a range for A1048576.
• 03:02 So what is this?
• 03:04 So if I copy this, and I go to the Excel name box and paste it in, this will jump
• 03:08 me right to that cell, which you'll notice is the very last cell on the worksheet.
• 03:14 Okay, we have no more cells here.
• 03:16 It's the very last cell of the worksheet.
• 03:18 If you are recording a macro, the next piece that I did here was,
• 03:21 I pressed the End key and pressed my up arrow.
• 03:24 And it actually shifts all the way up to the very last used cell.
• 03:28 And then what Offset does is offsetting it by one row and no columns.
• 03:32 So it's actually dropping down one cell.
• 03:35 This is a lot better than grabbing the top of your range and saying End Down.
• 03:41 Because if you only have one item, it takes just the last row on the sheet.
• 03:45 We don't really wanna have our data here.
• 03:46 So we go to the last cell and up arrow and down one.
• 03:50 That'll give a nice place that we always know is a good place to record our data.
• 03:54 So once I've got that cell set into a variable, I can actually take the range,
• 03:59 and I can say, give me the value for that, and set it at the employee and
• 04:04 offset it by 01, which is offset by 0 rows and 1 column.
• 04:09 So with our log over here, and the value is gonna equal.
• 04:13 Now just like the Excel formula,
• 04:14 except that we don't actually use parentheses on this guy in VBA.
• 04:18 So let's go see what actually ends up happening here.
• 04:20 As we go in, it says, what's your name?
• 04:23 So I can say, hey, my name is Ken.
• 04:26 We'll say OK, and it says all right, I'm gonna set the range here.
• 04:31 And how do I know that that's actually working?
• 04:33 Well, you know what, I can always go back and say show me rng.address.
• 04:40 It's A4, perfect.
• 04:41 That's exactly what I want.
• 04:43 So we're gonna say all right, let's go and set the value to sEmployee.
• 04:46 And if I want to test the range offset, I could go back and say, you know what?
• 04:50 Let's do that.
• 04:51 Let's say, what is rng.Offset(0, see, there we go.
• 04:56 We got the row offset, the column offset.
• 05:01 And I didn't give it anything.
• 05:03 It just says, well, what do you want to know about that?
• 05:04 Well, if I type in address, if I try and type in Address.
• 05:08 Here we go.
• 05:11 We get B4.
• 05:12 So B4's value is going to become Now.
• 05:15 There we go.
• 05:16 This actually worked out nicely.
• 05:18 We can run again.
• 05:20 And I could go and run this again every time I actually need to do it.
• 05:23 So let's say that Fred comes in and logs some time, and
• 05:26 we get a ticker that keeps on tracking all of this information.
• 05:28 So this is something that you may wanna use, assign this to a button
• 05:31 in order to be able to log who did what or who reviewed what or whatever.
• 05:35 A really useful little routine that I've used before.

Lesson notes are only available for subscribers.