Collecting Feedback from a VBA MsgBox

Quick reference

• 00:05 So now let's look at how to use a message box in a real world practical solution.
• 00:11 If you recall our continuity schedule,
• 00:12 we had built a macro that allows us to roll forward the file and then save it.
• 00:17 But one of the challenges we had when we're actually working on the save
• 00:19 file macro is that we needed to look and
• 00:22 see what would happen if the file was already found.
• 00:25 As it is we take a pretty hard line stance on this.
• 00:27 If the file is found we nuke it and that's it.
• 00:30 Wouldn't it be nicer if we could actually prompt the user to say,
• 00:33 would you like us to save it?
• 00:34 And if you don't want us to save it,
• 00:36 shall we just stop running the routine all together?
• 00:38 I think that would be a much, much nicer way of dealing with it.
• 00:41 So in order to do this, I need to make a couple of modifications to the code
• 00:45 block that I actually have here right now.
• 00:48 Right now, we're checking to see if the file path exists, if it does,
• 00:52 we kill the file name, and then we step out of our if statement.
• 00:55 If the file didn't exist, we just step right out of the if statement.
• 00:59 In both cases, we end up running the this workbook.save as.
• 01:03 I'm gonna move this code.
• 01:05 I'm gonna put it in here and I'm also going to put it in here just to make
• 01:10 absolutely certain that this still works.
• 01:13 I just wanna run through real quick and just talk about this one again to make
• 01:17 sure that we are aware that I haven't compromised the structure here.
• 01:20 So we check and see if the file exists.
• 01:24 If it exists, we delete it and then we save the new workbook under its new name.
• 01:32 If the file did not exist, we save it under a new name, and
• 01:37 then we exit the loop, okay?
• 01:38 So it's the same thing.
• 01:39 It's just that we've used the same code line twice.
• 01:41 Why did I do that?
• 01:42 Well because,
• 01:43 I actually wanna get a little bit more granular with what's going on in here.
• 01:47 At this point I'd like to give a user an option to say,
• 01:50 would you like me to do this?
• 01:52 And if you don't, I'd like to get it out.
• 01:54 So the challenge is is that when we're actually reading from a VBM message box,
• 01:59 I don't know what the data type is that's actually captured here.
• 02:02 So what I'm gonna do first is I'm gonna set up a variable here, and
• 02:05 I'm gonna call it message result, and
• 02:09 I'm gonna dim that as a variant because I'm not 100% sure what it is.
• 02:13 It's where I'll start.
• 02:15 So now, what I'm gonna do is I'm gonna go right underneath here I
• 02:18 found out that the file exists.
• 02:20 So what we're gonna do is we're gonna say msgresult equals.
• 02:25 Now when I do this I can type in that it equals a message box but because I'm
• 02:29 setting this equal to something, I now actually need to put in parenthesis.
• 02:33 I didn't need to do that when I'm just calling a message box without actually
• 02:36 trying to capture the result.
• 02:37 In this case here we're gonna say, would you like, oops, let me try and
• 02:42 spell that a little nicer.
• 02:44 Would you like to replace the file?
• 02:49 Close my quotes.
• 02:51 I'm now gonna hit comma to go into the different message box styles, but
• 02:54 because I'm getting close to the end of the screen,
• 02:56 I'm gonna hit space, underscore, Enter.
• 02:58 I'm still, as you've noticed from the tool tip text, now working on my buttons, but
• 03:02 that gives me a line break to make my code a little bit easier to work with.
• 03:05 So the style that I'm gonna go with, I'm gonna say VB, let me see,
• 03:10 how about VB question plus and we'll say VB yes no.
• 03:16 There we go.
• 03:18 Comma and what's the title that I would like to call it,
• 03:20 let's call it File exists.
• 03:25 Now, and it comes back and it tells me that I've got a compiler that is
• 03:30 missing a list separator or a parenthesis.
• 03:32 And the reason being is because I have an open parenthesis up
• 03:34 here I forgot to put a closing one on there.
• 03:36 So VBA helpfully tells me, turns everything red,
• 03:38 and let's me know that I shouldn't go forward.
• 03:40 So I fix that up, that looks better.
• 03:43 Now at this point in time, what I want to do
• 03:46 is I actually want to test if the message result equals something.
• 03:50 But unfortunately, I don't know what that is yet.
• 03:53 So what I'm gonna do is I'm gonna put a break point
• 03:55 on this Kill FullFilePath here.
• 03:58 And remember that this file already exists.
• 04:00 I'm not gonna run the roll forward macro because I'm still looking at
• 04:03 the January one.
• 04:04 So I know this file exists right now.
• 04:06 So we won't run roll forward.
• 04:08 We're not gonna save this.
• 04:09 But what we are gonna do is we're gonna run this and
• 04:11 get into the point where, does the file exist?
• 04:13 Of course this file does exist.
• 04:16 So we're gonna end up triggering our message.
• 04:19 And it says, would you like to replace the file?
• 04:21 So I'm gonna say yes.
• 04:23 And at this point,
• 04:24 I can use the immediate window I can see that my message result is six.
• 04:29 Well what in the heck does that mean?
• 04:30 Well here, let me just check this out here.
• 04:32 If I go in and say, let's go in and go to question mark, what is vbYes?
• 04:40 You'll notice that vbYes is equal to six.
• 04:43 These are numeric constants, that's actually why we can add them together.
• 04:46 So we can actually work with that.
• 04:47 We'll say stop this, and we'll try this.
• 04:50 We'll say if message result equals vb yes,
• 04:55 then we will kill the full file path.
• 05:01 If it doesn't else that must mean that it's no.
• 05:04 So what we're gonna do is we're gonna say exit sub and
• 05:07 exit the sub routine completely.
• 05:11 This way if the user says no I don't want you to actually replace it,
• 05:14 we can actually jump right out.
• 05:16 So that's gonna be really helpful.
• 05:18 Now It doesn't make sense for me to go and try and
• 05:21 save this routine at this point and run it because,
• 05:24 at least not directly from here, because I know that the January file works here.
• 05:29 And if I try and actually delete the January file,
• 05:31 that's the workbook I'm in that's not going to do very well.
• 05:34 So let's try and run this from our master macro.
• 05:41 Okay, so what we're gonna do is we're gonna step in, and
• 05:45 I don't really need to step through all these guys here, so
• 05:48 what I'm gonna do is I'm gonna use this step over command.
• 05:50 And that will run all of the pieces that are in the RollFoward macro.
• 05:54 And now we can step into the save file which it'll go through and
• 05:56 set up the individual components that we're looking for.
• 05:59 It'll check the directory, it says hey, it looks like it exists,
• 06:02 would you like to replace the file?
• 06:04 That means that it found the February file, so I'm gonna say yes.
• 06:08 And it says, all right the message equals yes so let's go and
• 06:13 kill the file path and then we'll step back and say, save and we're good to go.
• 06:19 Okay, so it works quite nicely overall.
• 06:22 The big key on this thing is being able to build your message box,
• 06:26 capture the message box result and
• 06:28 then actually test it to see which direction you wanna go.
• 06:31 If we'd said no then it would have actually gone straight into exit sub,
• 06:34 it would have skipped the remaining lines in this routine and jump straight out.

Lesson notes are only available for subscribers.