How to use the Message Box in Excel VBA (syntax you'll need for msgbox)

in this tutorial I'm going to show you a very useful and easy to use VBA function called msgbox function it basically looks like this it's a super easy way to get info dialog boxes to communicate with the user and also get simple responses like okay yes no or cancel now you can use it for example to inform them that the macro has just finished running or to double check with them before they run the macro especially if your code is deleting stuff now the best thing is that you don't have to design it it's an inbuilt function we just have to call it this video is part of my online VBA course if you're interested to find out more check out the link in the description or go to XEL pause comm slash courses our first task is to create a welcome message for the user because here we're just informing them our second task is to create a macro that's going to clear these values but before we clear them we want to double-check with the user if they're sure they want it clear so here we need to get a response from them let's bring a visual basic Altaf 11 first create a simple procedure let's call it simple msgbox for the welcome message I'm going to switch to single procedure view and collapse this to write the message box function you can directly type in in if you remember the syntax and if you don't you can get to it through intelligence by typing in VBA dot and it's directly in the global library or you can get through it through going through the correct compartment and that's in the interaction class dot message box okay so regardless of how you type this you get the same result the arguments for message box are the following so first is a mandatory argument called prompt the rest are optional now let's say our prompt here is hello okay that's all I need to type I'm gonna press Enter that's the message box in its simplest form so when I press play we get hello now let's take a look at the other arguments we can define a button style and we can also define what the default buttons are but in this case since we're just informing them we don't need more than one button we can skip this argument so notice that I'm going through this like a formula but you have the option of also referencing the arguments that you want next argument here is the title and I'm going to put in a welcome here the rest help file context I don't need and we can see here that the result of a message box is saved in something called VB message box results right that's something that we need if we are going to capture the answer so if we had more than one button here in this case we don't so it's enough just to do this then we can also personalize this a little bit and we can do hello and combine it with the username so I'm going to get to it through intellisense using application the username okay so let's run this you say hello irony and the title is something we change to welcome otherwise you just see Microsoft Excel there okay we can even define a button for this so I'm going to insert a form control assign it to the macro call this welcome and you can even add symbols to your buttons okay just make sure that the correct one is in front so I'm gonna right mouse click bring to front add this here assign a macro also to this symbol and then group them together okay so when we click this you see hello Nana granny now later we're also going to learn about events so if you ever wanted a welcome message to pop up every time the user opens your Excel workbook you need to work with excels events and that's something that we cover in a future section now let's just type this up another way by referencing the arguments separately so I'm gonna do prompt then you need the call in and the equal sign and then I want to take this one so I'm gonna copy this and also add a dot in the end so don't forget that whenever you're combining variables with text you do need that quotation mark now if you want to go to the next line we can use VB newline and let's add a second text called thanks for stopping by now I'm going to add space and underscore to go to the next line the title is welcome okay let's just comment this one out and let's run this hello later granny thanks for stopping by now let's move on to task two we won a clear values here but before we want to double check with the user for this I'm going to create a new sub called yes/no message box now this time we want to capture the results of the message box function so I'm gonna dim a variable that I'm just going to call answer as VB message box results so now we need to assign it we're going to assign answer to mr. box now because we're on the right side of the equal sign we need the brackets here let's put in are you sure about this we want to add special buttons so let's go with VB yes/no then if you want to add more styles to this just put in the plus sign we could do VB question to get the question mark in the form and we could assign a default button so yes would be default number one that means that that button is highlighted and if we want no to be highlighted we have to go with default button two so in this case I'm gonna go with default button two for the title I'm gonna add clear cells okay so now we need to see what the user clicked on and decide on the outcome that we want to take so if answer is VB yes then we want to clear that range else we want to leave this up let's now also create a button for this one and assign our macro to it let's click this are you sure about this notice that the default button is no so if I press ENTER it's going to exit this up now I can go with my keyboard to yes and press Enter or go with my mouse I'm going to press Enter the values are cleared so that's how you can use VBA's messagebox function to interact with the user if you liked this video I'd be happy if you gave it a thumbs up and if you'd like to learn more about excel consider subscribing this video was brought to you by unlock Excel VBA and macros course where you're going to learn how to simplify your work and have to save time by automating complex and routine tasks we're gonna go from beginner all the way to advanced for more information check out the description of this video or go to XEL plus.com slash courses [Music]

Channel: Leila Gharani

Views: 100,494

Rating: 4.9420934 out of 5

Keywords: excel vba, vba message box yes no, excel vba msgbox, msgbox snytax, excel vba message box continue or cancel, vba message box with multiple lines, excel vba message box, msg box, msgbox vba, how to use msgbox in vba, vba clear cell values, excel vba insert buttons with icons, Advanced Excel Tutorials, Leila Gharani, Excel 2016, Excel 2013, Excel 2010, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, XelplusVis

Id: EwbTFvXBfns

Length: 8min 51sec (531 seconds)

Published: Tue May 01 2018