Excel VBA MsgBox Explained In Detail

the excel vba message box a great way to give feedback to your users and let them interact with your macro coming right up the spelling of the message box function is msgbox from now on i'm just going to say message box because it's too much of a tongue twister otherwise now there are two ways of using message box you can either use it as a statement or as a function you use it as a statement when you just want to give some information to the user such as an error has occurred or finished processing the other form is if you wish to get information back from the user and you want the user to make a decision so for that you use the function syntax so let's just do a little example here so if i do sub test and i'm going to just create a variable dml result as long so we're using the function form of message box equals message box network error eror keep trying so i'm going to just make this message box more useful with each iteration what i'm also going to do is i'm going to type debug.print l result value is and ampersand l result i'm just going to run that piece of code so there we go network error keep trying so it's asking me a question but i can't say yes or no because i'm just getting the standard okay but what we have done is we've got the value of the ok button because i pressed ok and it said l result value is one now let's make this message box a little bit more useful i'm going to put in a comma i'm going to choose from one of these built-in constants the options that you have available for buttons for example are vb yes no you've got vb okay cancel you've got vb okay only which is the default that we've just had vb retry cancel and yes no and yes no or cancel so let's just go yes no cancel just out of interest so now if i click run again you now have network error keep trying and we have yes no or cancel so if i press cancel a result value is 2. so the value of the cancel button is two let's run it again so the value of the yes button is six the value of the no button is seven and as i've previously just pointed out the value of the cancel button is two you can build in some interactivity into your program so for example i can type select case and l results i mean it's your choice whether you use if or case but case in this instance can be a little bit neater so to speak so if i just type in case 6 case 7 and case 2 i'll fix that typo in a second and select i can put in another message box now this is instead of code that you would for example put in that would enable you to fix your network error so message box we're going to try again we're not going to try to reconnect and user press cancel so let's try this we run it again network error keep trying no press no we're not going to try to reconnect and if i just run it again um cancel user press cancel but okay we can make we can still customize this message box a little bit more so in addition to yes no cancel you can chain buttons together because you will have noticed that the button looked pretty plain we're asking a question so why not try vb question there now let's run that and now you have the question mark appearing so now network keep trying yes we're going to try again the other thing we can also do with message boxes is you can customize the title so i'm going to use the title of businessprogrammer.com you for example would use the title of your application name or whatever the name you want to give the program that you're going to use and in that instance you could for example perhaps use a global variable the only time i actually agree that it's okay to use global variables so let's run this now so network error keep trying yes no cancel so i'm going to go no and now you can actually see the difference because this for example is a play in microsoft excel in the title and it just has okay as opposed to having something that looks like that now in addition to that we've mentioned the network error keep trying you can also instead put vb exclamation so for example if i do space control plus so if i do vb exclamation and i click ok you now get network error keep trying businessprogrammer.com yes no now let's also discuss default buttons so i've done everything with the mouse so far so if i click run you can see network error keep trying you you notice that yes is highlighted that means if i press the return key yes is what will be accepted so yes we're trying we're going to try again what if i want cancel to be the default well i just have to add in another argument here so if i go plus you've also got vb default button one two three and four this is covering up to four buttons that can appear on the message box so for example if i choose vb default button three you will remember cancel was in the third position so if i put that in and now if i run it network hour keep trying notice that the cancel button is highlighted so when i press enter now user press cancel in addition to that let's just try the other option that's giving it option two which is the middle button run that network error keep trying no user press no we're not going to reconnect so that was using the keyboard and so you can see you've got a lot of options available to you with this also i do have an article on businessprogrammer.com that explains all of this in ultimate detail where you can see the code and you can get to it easily via businessprogrammer.com forward slash message box thanks for watching

Channel: Sean Johnson

Views: 228

Rating: 5 out of 5

Keywords: excel vba, excel vba msgbox result, excel vba msgbox response, excel vba msgbox, MsgBox, msgbox vba excel, msgbox visual basic, msgbox vba, excel vba tutorial, vba message box, vba, excel vba message box, excel yes no message box, msg box, excel message box, excel vba message box custom buttons, message box vba, message box in excel vba

Id: ZtV5sYnDgvw

Length: 6min 11sec (371 seconds)

Published: Tue May 18 2021