Excel VBA Introduction Part 10 - Message Boxes

welcome to this YSL tutorial in this video we're going to cover the use of message boxes in Excel VBA the things we'll cover in this video will tell you all you need to know to start displaying messages on the screen while your programs are running so we'll start with an explanation of the basic message box function and then show you how you can go into customize message boxes using the various parameters we'll show you a couple of useful techniques for working with messages including how to concatenate strings and how to add multiple lines to a message box we'll also show you a couple of ways to get values into a massive box so from things like functions and also cell values from your spreadsheet the final part of the video shows you how you can ask users simple questions using message boxes how to store the results using variables and then how to test the result of your message so let's get started a message box in VBA is a convenient way to display information on screen while your programs are running to make one appear is really straightforward you need to reference the message box function and you make that appear in the intellisense list if you press ctrl + space on the keyboard following that type in another space and then you'll see an argument list or a parameter list appear showing the 5 different parameters for the message box there's only one compulsory one though and that's the one called prompt the reason you can tell it's compulsory is because it's not stored in a set of square brackets like all the other ones are so the prompt is simply the text that appears on the message box itself there's an unwritten rule in programming where you're always supposed to make your first message say hello world and pluck them out really tedious so let's put something else in a set of square double quotes let's put I know I'll I Peter um how much more exciting than hello will release it but at least it's true if I hit enter then at the end of that instruction all I need to do now is execute the subroutine by clicking the play button or hitting the f5 key on my keyboard when I do that my message box appears on the screen with the prompt that I've specified clicking ok simply answer subroutine and takes me back to the VB editor now there are several things that we can get to customize our message for instance we can change the title that's displayed and we can even display different icons next to the prompt to make that work we need to head to the end of the line of code we've already written type in a comma to make the parameter list appear and then look at the other parameters where you have so you can see that we've moved on to the buttons parameter it's a bit of a misleading name actually we're not going to change the buttons at this point the buttons parameter also lets you change what symbol appears on the message so there are things like VB critical which displays a red circle with a white cross there's something called VB exclamation which is a yellow warning triangle with an exclamation mark in it there's something called VB question a little bit further down which shared displays a question mark symbol on the message the one that we're going to use is the one called VB information and simply displays an a blue circle with an eye in the middle so we'll have a VB information symbol then once you've got that we'll have another comma and that takes us to the title argument and here I'm just going to put in another string of text so I'm going to say food message that's particularly exciting but it'll just be enough to demonstrate how it works so those are two extra arguments provided if I then run the subroutine again try that again and the subroutine there we go you can see the customizations we've made it the message we have the food message title and a bit more obviously we have the information symbol displayed I just wanted to make a quick mention of a technique you'll often see used when you pass multiple arguments to a function like a message box sometimes it can be difficult to interpret which piece of information which argument is being passed to which parameter so rather than just listing out your arguments in a comma separated list it's possible to name the parameters as you give information to them so to do that all you would need to do is in front of the argument type in the name of the parameter so in this case it will be prompt followed by a colon and an equal sign and you would then subsequently do that to each of the other arguments so in front of the VB information we knew that that was the buttons argument or parameter and then again in front of the food message that was at parameter titled colon equals it obviously requires a little bit more effort to do and it makes your code I guess a bit longer but it can be worthwhile if you're not entirely sure which Valley gets passed to which parameter is a nice way to remind yourself have talking it in the real world I don't tend to do this myself but just it's just so you know that it's an option another technique that you're likely to need when you're using message boxes is a technical concatenation or simply joining bits of text together so I'm gonna write another subroutine that displays a different message on screen this going this is going to be called date message I'm also going to do is display to the user a phrase which says today's date is and then that will show today's date so to make that work again I need to start with the message box and all I'm going to do is specify the prompt the prompt was made of two different parts the phrase the date is and that's a string literal so it's going close inside of double quotes and then what I want to do is join to the end of that the actual date there's a function in VBA that will do that for me the function is called date but join it together with what I've already done and it's used the ampersand symbol which is shift in the number seven on your keyboards usually all going to do then is type in the function that I want to use which is called date hit enter and then if I run that subroutine we should see those two bits of information are joined together hopefully you'll notice the importance of using a space within my message if I click OK to go back to the code if I didn't have this space within the string then when I run the subroutine I don't get a space in the message either so it's important whatever you put into the double quotes it's important to understand that it appears exactly as you have typed it in including any spaces you can join together or concatenate as many different elements of a phrase that you like so let's say for instance that after I've displayed the message to do with the date I want to tell the user what the weather's like so after the date function I can type in another ampersand and then join another piece of information so in this case I'll open a set of double quotes there'll be a full stop after the date and then I'm going to start another sentence the weather is and I'm in near Manchester in the UK which were the weather's pretty much always rainy here so it's a fairly good bet I can say the weather is rainy and that will be true so if I then run that subroutine again I get my second Peter information or inset so technically the third piece of information isn't it the date is is the first bit then the actual date and then this literal phrase starting with the full stop there and ending with the full stop there when you have slightly longer messages to display it can be useful to separate out the message on two different lines so let's say in this example for instance I want to show the date information on the first line and the weather information on the second what I actually need to do to make that work is concatenate in a character code that inserts a new line within my phrase so let's just do a quick little tweak here where we end the first sentence with that full stop so the data is followed by today's date then a full stop then what I need to do is join in concatenate a carriage code that's a bit of choice here about which character code you actually use you'll find them all if you press the ctrl + space keyboard shortcut a common but slightly old-fashioned one but you'll still see this use quite often come on call vb c r LF which stands for visual basic carriage return line feed it's kind of harks back to their days of typewriters when you reach the end of the line you have to flip the carriage all the way back to the left hand side and then feed a line in the piece of paper there's a much more modern equivalent of that which is one that I tend to use myself it's called VB new line and I think that's a lot easier to understand as well so VB new line another ampersand and then I need to make sure that I begin the next sentence with a double quote as well so we can just quickly squeeze this across so you can see the full sentence so there's the first part of message the date is then the function called date and then a full stop then a newline character and finally the last part of my message so the end result of all this if I run the subroutine is that I get my message split into two separate lines which is exactly what we wanted as well as getting the information for a message from a combination of literal strings and functions and other keywords you can also get data from the spreadsheet to be displayed in a message but quickly switch back into Excel we're now a good old standard movies database again what I'd like to do maybe write a routine which selects a cell in column B here and then displays a message but it says what basically tells me what release date that film has so if I go back to the visual basic editor and then light another subroutine I'm gonna call this movie message what we can do is display a message box I'm sorry first of all we need to select a cell don't we so let's say range b7 arbitrarily dot select and then we can display a message which says active cell dot value so that will show me the value of whichever cell I have just selected then I would like to concatenate the literal phrase was really some and then another piece of information I want to refer to the value with the cell that is one to the right of the active cell so active cell again got offset zero comma one as you'd want call them across to the right dot value so that entire message should read if I run the subroutine because I've selected cell b7 it's only the results for the hobbit' and unexpected journey I click OK if I change the cell that I selected in the first place I say b12 for instance and run the subroutine again I get a different message picking up values from different cells again so far we've only used a message box to display information on screen but it is possible to use messages to get inputs from the user as well to show you how that works I'm going to extend our first subroutine to also ask these sort of questions after the message telling us that we like Peter I'm going to display another message box which asks a question do you like Peter I'm not going to bother naming my parameters this time and you're going to get jump straight into there the values that I'm going to pass to them so that's the prompt I'm also going to display a VB the question symbol on the message and I'm also going to change the title of the message as well so I'm going to make that safefood question okay so if I run that several it in will see two messages in succession the first bit of information I like Peter and subsequently the question do you like pizza hopefully you can see the obvious problem with a user being able to respond correctly to this though by default a message box only shows an OK button so we need to extend this so it shows yes and no buttons so I'm gonna click OK to get back to my code and then head back to the buttons parameter now we've already passed it in the VB question symbol what I also want to do is show VB yes no buttons and to do that you've type in a plus symbol after the first item and then choose another one from this list what I'm going to go for is VB yes no we also get get an idea of the other types of things you can see VBS no cancel retry and cancel ok only etc so the one we're going to go for is yes and no if we run that subroutine again now we can see that on the second message we have yes and no buttons so we're getting closer the last problem we got to overcome is how we actually get the result how do we store which button the user clicks because at this point it makes no difference which button gets clicked my program doesn't store it anywhere probably the best way to store the results of a message box is to use a variable now we've created a video on variables previously so we're not going to go into too much detail here what we're building is head to the top of the subroutine and declare a variable called button clicked which is going to hold the results of our message box what I then need to do is decide on which data type gets stored in the variable and actually got a couple of choices here one I'm going to go for is called VB message box result and this is an example of something called an enumeration and all an enumeration is is a list of numbers where each number has a textual description so if I choose VB message box result just to show you what I'm allowed to to store in that variable click on the word message box plus the f1 key on your keyboard to launch their help system that should take you directly to the master box function help page scroll down a bit on the help page to get to the return values sections these are the values that the measure box can give back to us you'll see there are two different things that get that get returned there's a list of numbers 1 through 7 but also corresponding to the numbers is a literal description and the ones we're going to get a VBS or VB know so I could have declared a variable that was a byte or an integer or some kind of number and got the numerical results what I've done is got the result of the value of a of an enumeration so VB message box result okay last stage to actually get the results stored is to extend the line which displays our message at the front of this I need to type in my variable name button clicked equals you'll see one of the great advantages of having an enumeration is that rather than having to remember or even guess what numbers correspond to which button you can actually see the intellisense list appears to show you exactly what options are valid we're not going to pick one without lists at this point all we need to do now is to enclose our parameter lists in a set of parentheses can I just show you that if I click away from this line I currently have a syntax error when you're storing the results of a message you need to enclose this argument list in a set of parentheses it's a syntax ruling V ei essentially when the message box appears at the right hand side of an assignment operator an equal sign the argument list needs to be enclosed in parentheses so nearly there all we've got to do now is test which button was clicked so we'll use a simple if statement to check which button was clicked so we can say if button clicked equals and again we get the the benefits of having used this particular data type the enumeration and say if the button clicked is VB yes then I'm going to say a different message we're gonna have another slightly longer video about if statements by the way if you're not that familiar with your statements yet we will explain that in a lot more detail in a future video but I'm going to say if the button click was yes then I'll display a message box which says yep pieces are great not particularly brilliant message to display but there we go I'm gonna splay let's see VB exclamation on the message as well otherwise there's only one of the choice if it wasn't yes it must have been no so if they're user to click no a different message box which says why not pieces are great and on this message I'm going to display a VB critical symbol just mainly so that you can see there the different range of symbols you can see on a message make sure to end the if statement and then run the subroutine one last time I like pizza great do you like pizza if I click yes I'll get a message saying yes pieces are great and that's the exclamation symbol if I run it one more time choosing no this time I got a slightly different message so that's a clearly an invalid choice pieces are great so that's the basics of using message boxes hopefully you found the video useful thanks for watching if you've enjoyed this training video you can find many more online training resources at ww-why Zeljko UK

Channel: WiseOwlTutorials

Views: 123,242

Rating: 4.9525504 out of 5

Keywords: Microsoft Excel (Software), Visual Basic For Applications, msgbox, message box, vba, wise owl

Id: JF1ZLOczSvw

Length: 16min 33sec (993 seconds)

Published: Thu Jan 23 2014