Excel VBA - MsgBox Function

hi and welcome to another breweries comm XL screencast today's screencast is going to be on the message box function in VBA if you're watching this video on YouTube and you'd like this workbook to download and follow along the macro code head over to Bradley's comm /xl - screencast there you'll find a message box function VBA video click the watch video button and underneath the screen you'll find the link to the workbook today we're gonna go over two common uses of the message box function the first is to alert whoever's using the spreadsheet of an important piece of information and the second is to use the user's answer to a message blocks question to direct the workflow of the macro so to start off here's the message box syntax there are five arguments only the first prompt is a required argument for all the details in the message box syntax you can head to this MSDN message box help page on Microsoft's website I'll bring it up here it's got everything you need to know about the syntax and the arguments of the message box function so I encourage you to have a look at it now let's get started with our first mackerel our first macro illustrates the most basic use of the message box function and that's to and that's to tell the user something be it an important piece of information or just alerting them that something has happened so our first macro is very simple it's just a cell selection macro that's going to let us know if we've selected one cell or multiple cells so if I click on a cell anywhere in the worksheet and click cell selection you see a message box pops up that says one cell selected very simple if I sell if I select multiple cells click the same button you'll see our message box now says multiple cell selected what's displayed here is the first argument in the message box syntax it's the prompt we'll get into a couple of other of the arguments used in the syntax and a little bit so we'll head over to alt 11 and just check out that first macros code it's extremely simple it's just selection to ourselves dot count counts the number of cells if it's greater than one we have this message box displayed if it's equal to one we have this message displayed very simple hit back to the worksheet the next use of the message box function that I find most useful when I'm designing a spreadsheet for others to use is to use the message box to spot errors alert the user to the air and allow the user to make the decision of whether to continue or not to continue I'll illustrate where this is useful in the following macro it's called transferred daily sales data what this macro does is say we have the current days sales downloads from an external source and we want to transfer that download into the historical sales worksheet now you can imagine how easy errors can occur say we forget to update these cells with the current day's totals and then we paste over here anyways we're gonna paste duplicate data however sometimes we may miss a day of importing the sales data and we may need to import add a sales data that is different than today's date so what we're gonna do is first identify or trap an abnormal situation where we're trying to import data that isn't today's date and then let the user decide if they want to continue with the macro or abort it so first we're gonna look at the code and then we're gonna go through it and test it in different situations on the worksheet so the macro is called sub worn user all it's going to do is determine if the data we're trying to transfer is different from today's date the user is going to is then going to be given three options yes/no or cancel depending on the option they choose the macro is going to go in three different directions if they choose yes they want to continue which is case VBS right here no code is going to happen within the select case statement or the if statement and we're just going to go straight back down to make the data transfer if the user selects case VB no we're gonna alert them that alert them of what to do and how to fix the problem so find the current data and run again if they click cancel we're just going to exit the sub so let's see how this works in practice first time through we go to our occurrence day sales this is the correct this is today's date September 15 2011 that's the data we want to transfer a head over to historical say hit the transfer Sayle daily sales data and we have a message box saying our data transfer was successful we look down and sure enough it was successful but say today's date is September 15 2011 and for whatever reason yesterday September 14th somebody forgot to update the file so say this data is missing and we go to our external source and we grab the 14th of September sales data and now we try to transfer in the data well this is the air we want to avoid in normal situations however now that this is an abnormal situation we want the user to be able to override our air trapping and proceed with the data transfer if necessary so if we head back to the historical sales and now we tried to transfer the daily sales data you'll notice that we have a new message box with a warning saying the date you were trying to attempt to transfer is not today's date do you still wish to transfer if we choose no brings up another message box saying find current data and run again however if we do want to transfer the data the user can click yes the data transfer will be successful what this allows us to do is maintain flexibility in our macro in case of abnormal situations while still trapping the air under normal circumstances I encourage you to explore the message box syntax on the help page in either excel or read on Microsoft's website to really get a feel of the different options that you can that you can use to help out your user and clarify any problems that may occur with the data I want to thank you for watching this excel screencast on rut waste com have a great day

Channel: ExcelScreencasts

Views: 10,788

Rating: 4.5555553 out of 5

Keywords: Microsoft Excel, MsgBox, VBA

Id: vhfnfqyOZv8

Length: 6min 2sec (362 seconds)

Published: Thu Sep 15 2011