In this video we're going to cover the use of dialog boxes which are a good way of communicating to
the user or allowing simple user interactions or user inputs.
We're going to start with the basic message box which will be creating by opening up our VBA window
and inserting a module.
We'll go to the developer tab and click Visual Basic in the VBA window click insert module and now we'll
create a public sub we'll call this example message put out parentheses and hit enter.
Now the terminology associated with the basic message box is m s g b o x from message box.
So we'll type that and hit space.
You should see a yellow bar pop up below your mouse.
These are all of the inputs that the function message box is set up to accept not all of these available
inputs are required for every type of function you might use in VBA.
You can look up all of the functions and terms in the object browser library or on Microsoft's help
site as an example.
We can search for message box in the object browser library by clicking on the object browser button
and typing message box into the search bar hit enter to search in our search results.
We see this interaction class member message box which is what we're using.
Click on that and make sure message box is selected as your member of interaction.
You can see the function inputs appear at the bottom of the screen and you can click the links to navigate
to those particular inputs to see what valid options might be.
Again this is all available through Microsoft's help site which actually provides further examples and
more clarity.
For now let's close out of the browser and we'll talk through the message box inputs one by one.
The first input is prompt which is the text that will appear in the message box.
We can type a little hello message here as an example and that message should be surrounded by quotes
to signify that it is text.
Anytime you use text in VBA it must have quotations around it.
So the code recognizes it as a string of text and not as key VBA code words commas separate the function
inputs.
So we'll put a comma next to show that our prompt input is complete.
Next is the type of buttons but we don't type anything here.
The system will just use an OK button for the message box so we'll skip this as an example by not typing
anything and just putting another Comma next is the title which is the text that will appear in the
blue bar above the message as its title will type.
Example again in quotations to denote text help file and context link a help file for the user and are
generally unused so we'll leave those off entirely.
Let's go run this macros see what happens and what the message box we've built looks like we'll minimize
the VBA window in our excel file under macros.
Well highlight.
Example message and click Run.
We see that we get a message box that says hello.
It is titled example.
And the only button we have is the OK button which will go ahead and click to close out a message box
now.
Sometimes you might use the message box as we just did to simply communicate something to the user.
This could be a message saying the code was successfully executed a message containing a data point
the user might need or an alert message to make them aware of an error.
In these cases the user doesn't need to interact with the message other than to simply acknowledge that
they read it.
So the OK button will suffice.
But let's say you have a question you want the user to respond to you have a few options.
The first is to change the button type of the message box.
We can change the buttons to display as something like Yes and no.
For example and retrieve a binary data point from the user for example let's go back to our simple message
box and we'll adjust the button type which is this middle function and put here instead of leaving this
blank will type VB yes no VB yes no means our message box will now display with a yes button and a no
button instead of simply an okay button.
Let's go back to our code and run this to get an example their macros.
Make sure you highlight example message and click Run we can see we have the same message prompt and
we have the same message box title but instead of a single OK button we now have a yes button and a
no button you can click on either those to close the message box and later videos we'll talk about if
statement which we can then use to assign a certain set of actions to whether the user presses yes or
no.
This is a really simple way to allow your user to interact with the database but it only provides a
binary data point yes or no let's say you want the user to actually input a piece of information.
This is what you would use an input box for which is the next thing we're going to talk about an input
box takes a piece of information that the user types and provides and stores it in a variable that you
predetermine as an example let's create a variable by declaring a variable we'll call it X and we'll
say this variable is a string which means text now to assign a value to this variable we'll type E X
equals and instead of typing our usual piece of text surrounded by quotes we're instead going to type
input box and then an open parentheses.
Now we can see that this has very similar inputs to the message box it's going to display very similarly
to the message box.
So as our prompt will say in quotes what is your favorite
for the title we'll say color and for now we can ignore the rest of these options.
They mainly have to do with the positioning of the box on the screen so we'll allow the box pop up in
the center of the screen or wherever it likes so we'll hit close parentheses to close our input box
now after the input box runs we'll have a value stored in our e x variable.
So let's return that X variable in our regular message box that we have down here.
So we'll add to this prompt and say your favorite color is
now.
We probably don't want to put the favorite color on the same line.
Maybe you will want to add an empty line in between this statement and the resultant favorite color
that has been stored in our variable in order to do so.
We're going to type in and sign the and sign tells the system we have a piece of text on the left of
this and sign.
And I'm about to give you another piece of text on the right side of the ad sign.
And I want you to combine them into one really long piece of text.
The reason that we put the answer on there is because what usually follows the AM sign or precedes the
sign is a variable or a VBA interaction.
So in this case we're trying to add a new line which is going to be VB C are 11th and then hit space
this particular code VB C are L F tells VBA that we're trying to insert a new line.
So now as a whole in our prompt right now we have a piece of text that says hello your favorite color
is then we've told VBA we're about to combine this with more text in this case I'm going to enter a
new line I'll use the and sign again let's enter one more new line just so we can really see the effect
and sign again.
So VBA still knows I'm still stringing together text because I keep using these and signs and then we're
going to type E X which is the variable that we've created up here that should have the value of the
favorite color.
Go ahead and jump out of that message box and we can see everything is capitalized.
Nothing is turned red so this seems to be the correct code that we need.
Let's go ahead and run this and we'll get an example of how this works.
Minimize your VBA window go to macros and run the example macros one more time we see we have this input
box.
It's asking What is your favorite color.
And it's titled color.
In this case I'll say my favorite color is blue and click ok.
Now I get my example message box which says hello your favorite color is new line once new line twice
and then the favorite color that I just input it blue again.
My title is so example my buttons are still yes and no because that's what I've preset the message box
to be.
But now we have a better understanding of how to create message boxes how to string together text and
how to use input boxes for simple user interaction.
The last thing we're going to talk about are alert messages in Excel and how we can turn them off.
Now we've seen that it can be really helpful to build display boxes and message boxes to help communicate
with the user.
Excel has some of its own internal message boxes and display alerts that will give the user when it's
trying to do something like save a file when you try to save a file in the background in the VBA code.
Excel might prompt the user with the message box asking whether or not the user really wants to save
it can be frustrating when you're trying to automate a process and you continue to get pop up messages
from Excel itself.
There is a way to turn off these alert messages and that's what we're going to talk about if we open
up our VBA window and go up to the top of our code here.
We're going to type application dot display alerts equals false.
Now let's break this down for a second application tells VBA that we're dealing with the Excel application
specifically this file that we have open the dot moves one level further into the options that we can
apply to that application.
In this case we're looking at the display alerts property of the excel file and we've set it equal to
False which means Excel will no longer display alerts that are intrinsic to excel.
It will still display your message boxes your input boxes your user forms.
It just won't display messages such as a save message or an overwrite message.
Generally speaking it's best practice to make sure we always turn these back on at the end of our code.
So right before we end the sub we'll type application dot display alerts equals true now in this example.
Anything that falls between this display alerts equals false and the display alerts equals true would
not be subject to excels internal messages and alerts and pop up windows.
So I recommend doing this when you're doing a lot of save options or things that might be creating pop
up windows that the user would have to continue to interact with.
They'll just slow down your code.
So it's a good idea to use display alerts equals false as long as you turn it back on.
At the end of your code.
So in this video we covered message boxes and put boxes and how to control excels pop up windows.