1
00:00:00,660 --> 00:00:06,120
In this video we're going to cover the use of dialog boxes which are a good way of communicating to

2
00:00:06,120 --> 00:00:10,740
the user or allowing simple user interactions or user inputs.

3
00:00:10,740 --> 00:00:15,780
We're going to start with the basic message box which will be creating by opening up our VBA window

4
00:00:15,780 --> 00:00:17,960
and inserting a module.

5
00:00:18,060 --> 00:00:27,870
We'll go to the developer tab and click Visual Basic in the VBA window click insert module and now we'll

6
00:00:27,870 --> 00:00:39,540
create a public sub we'll call this example message put out parentheses and hit enter.

7
00:00:39,640 --> 00:00:47,260
Now the terminology associated with the basic message box is m s g b o x from message box.

8
00:00:47,380 --> 00:00:50,410
So we'll type that and hit space.

9
00:00:50,590 --> 00:00:53,800
You should see a yellow bar pop up below your mouse.

10
00:00:53,800 --> 00:00:59,680
These are all of the inputs that the function message box is set up to accept not all of these available

11
00:00:59,680 --> 00:01:03,760
inputs are required for every type of function you might use in VBA.

12
00:01:03,940 --> 00:01:09,460
You can look up all of the functions and terms in the object browser library or on Microsoft's help

13
00:01:09,460 --> 00:01:11,230
site as an example.

14
00:01:11,230 --> 00:01:16,450
We can search for message box in the object browser library by clicking on the object browser button

15
00:01:17,260 --> 00:01:24,090
and typing message box into the search bar hit enter to search in our search results.

16
00:01:24,100 --> 00:01:28,440
We see this interaction class member message box which is what we're using.

17
00:01:28,600 --> 00:01:34,090
Click on that and make sure message box is selected as your member of interaction.

18
00:01:34,180 --> 00:01:39,370
You can see the function inputs appear at the bottom of the screen and you can click the links to navigate

19
00:01:39,370 --> 00:01:44,200
to those particular inputs to see what valid options might be.

20
00:01:44,200 --> 00:01:49,930
Again this is all available through Microsoft's help site which actually provides further examples and

21
00:01:49,930 --> 00:01:51,310
more clarity.

22
00:01:51,310 --> 00:01:58,460
For now let's close out of the browser and we'll talk through the message box inputs one by one.

23
00:01:58,670 --> 00:02:03,810
The first input is prompt which is the text that will appear in the message box.

24
00:02:03,860 --> 00:02:09,490
We can type a little hello message here as an example and that message should be surrounded by quotes

25
00:02:09,490 --> 00:02:11,840
to signify that it is text.

26
00:02:11,860 --> 00:02:15,700
Anytime you use text in VBA it must have quotations around it.

27
00:02:15,730 --> 00:02:24,110
So the code recognizes it as a string of text and not as key VBA code words commas separate the function

28
00:02:24,110 --> 00:02:24,720
inputs.

29
00:02:24,740 --> 00:02:28,910
So we'll put a comma next to show that our prompt input is complete.

30
00:02:28,910 --> 00:02:32,420
Next is the type of buttons but we don't type anything here.

31
00:02:32,420 --> 00:02:38,180
The system will just use an OK button for the message box so we'll skip this as an example by not typing

32
00:02:38,180 --> 00:02:44,140
anything and just putting another Comma next is the title which is the text that will appear in the

33
00:02:44,140 --> 00:02:47,860
blue bar above the message as its title will type.

34
00:02:47,860 --> 00:02:56,180
Example again in quotations to denote text help file and context link a help file for the user and are

35
00:02:56,190 --> 00:02:59,840
generally unused so we'll leave those off entirely.

36
00:02:59,850 --> 00:03:05,940
Let's go run this macros see what happens and what the message box we've built looks like we'll minimize

37
00:03:05,940 --> 00:03:12,030
the VBA window in our excel file under macros.

38
00:03:12,350 --> 00:03:12,920
Well highlight.

39
00:03:12,920 --> 00:03:14,800
Example message and click Run.

40
00:03:16,650 --> 00:03:19,650
We see that we get a message box that says hello.

41
00:03:19,650 --> 00:03:21,260
It is titled example.

42
00:03:21,480 --> 00:03:26,520
And the only button we have is the OK button which will go ahead and click to close out a message box

43
00:03:28,880 --> 00:03:29,320
now.

44
00:03:29,330 --> 00:03:35,630
Sometimes you might use the message box as we just did to simply communicate something to the user.

45
00:03:35,630 --> 00:03:41,210
This could be a message saying the code was successfully executed a message containing a data point

46
00:03:41,210 --> 00:03:45,650
the user might need or an alert message to make them aware of an error.

47
00:03:45,680 --> 00:03:50,870
In these cases the user doesn't need to interact with the message other than to simply acknowledge that

48
00:03:50,870 --> 00:03:51,650
they read it.

49
00:03:51,680 --> 00:03:53,760
So the OK button will suffice.

50
00:03:53,900 --> 00:03:59,000
But let's say you have a question you want the user to respond to you have a few options.

51
00:03:59,000 --> 00:04:02,610
The first is to change the button type of the message box.

52
00:04:02,810 --> 00:04:06,260
We can change the buttons to display as something like Yes and no.

53
00:04:06,260 --> 00:04:12,560
For example and retrieve a binary data point from the user for example let's go back to our simple message

54
00:04:12,560 --> 00:04:21,050
box and we'll adjust the button type which is this middle function and put here instead of leaving this

55
00:04:21,050 --> 00:04:31,860
blank will type VB yes no VB yes no means our message box will now display with a yes button and a no

56
00:04:31,860 --> 00:04:34,620
button instead of simply an okay button.

57
00:04:35,130 --> 00:04:39,810
Let's go back to our code and run this to get an example their macros.

58
00:04:39,870 --> 00:04:46,780
Make sure you highlight example message and click Run we can see we have the same message prompt and

59
00:04:46,780 --> 00:04:52,900
we have the same message box title but instead of a single OK button we now have a yes button and a

60
00:04:52,900 --> 00:05:01,680
no button you can click on either those to close the message box and later videos we'll talk about if

61
00:05:01,680 --> 00:05:07,530
statement which we can then use to assign a certain set of actions to whether the user presses yes or

62
00:05:07,530 --> 00:05:08,250
no.

63
00:05:08,400 --> 00:05:14,040
This is a really simple way to allow your user to interact with the database but it only provides a

64
00:05:14,040 --> 00:05:21,510
binary data point yes or no let's say you want the user to actually input a piece of information.

65
00:05:21,510 --> 00:05:27,970
This is what you would use an input box for which is the next thing we're going to talk about an input

66
00:05:27,970 --> 00:05:34,750
box takes a piece of information that the user types and provides and stores it in a variable that you

67
00:05:34,750 --> 00:05:44,860
predetermine as an example let's create a variable by declaring a variable we'll call it X and we'll

68
00:05:44,860 --> 00:05:53,540
say this variable is a string which means text now to assign a value to this variable we'll type E X

69
00:05:53,660 --> 00:06:00,740
equals and instead of typing our usual piece of text surrounded by quotes we're instead going to type

70
00:06:00,890 --> 00:06:06,530
input box and then an open parentheses.

71
00:06:06,680 --> 00:06:12,540
Now we can see that this has very similar inputs to the message box it's going to display very similarly

72
00:06:12,540 --> 00:06:13,890
to the message box.

73
00:06:14,040 --> 00:06:20,100
So as our prompt will say in quotes what is your favorite

74
00:06:23,850 --> 00:06:30,760
for the title we'll say color and for now we can ignore the rest of these options.

75
00:06:30,890 --> 00:06:36,860
They mainly have to do with the positioning of the box on the screen so we'll allow the box pop up in

76
00:06:36,860 --> 00:06:41,840
the center of the screen or wherever it likes so we'll hit close parentheses to close our input box

77
00:06:43,770 --> 00:06:48,420
now after the input box runs we'll have a value stored in our e x variable.

78
00:06:49,020 --> 00:06:54,610
So let's return that X variable in our regular message box that we have down here.

79
00:06:54,990 --> 00:07:00,480
So we'll add to this prompt and say your favorite color is

80
00:07:04,250 --> 00:07:04,860
now.

81
00:07:04,970 --> 00:07:09,160
We probably don't want to put the favorite color on the same line.

82
00:07:09,170 --> 00:07:15,560
Maybe you will want to add an empty line in between this statement and the resultant favorite color

83
00:07:15,590 --> 00:07:19,280
that has been stored in our variable in order to do so.

84
00:07:19,280 --> 00:07:26,300
We're going to type in and sign the and sign tells the system we have a piece of text on the left of

85
00:07:26,300 --> 00:07:27,680
this and sign.

86
00:07:27,680 --> 00:07:31,780
And I'm about to give you another piece of text on the right side of the ad sign.

87
00:07:31,790 --> 00:07:36,240
And I want you to combine them into one really long piece of text.

88
00:07:36,320 --> 00:07:41,330
The reason that we put the answer on there is because what usually follows the AM sign or precedes the

89
00:07:41,510 --> 00:07:45,650
sign is a variable or a VBA interaction.

90
00:07:45,650 --> 00:07:53,840
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

91
00:07:54,990 --> 00:08:01,860
this particular code VB C are L F tells VBA that we're trying to insert a new line.

92
00:08:01,860 --> 00:08:08,460
So now as a whole in our prompt right now we have a piece of text that says hello your favorite color

93
00:08:08,460 --> 00:08:15,330
is then we've told VBA we're about to combine this with more text in this case I'm going to enter a

94
00:08:15,330 --> 00:08:22,720
new line I'll use the and sign again let's enter one more new line just so we can really see the effect

95
00:08:23,860 --> 00:08:25,280
and sign again.

96
00:08:25,360 --> 00:08:31,720
So VBA still knows I'm still stringing together text because I keep using these and signs and then we're

97
00:08:31,720 --> 00:08:37,480
going to type E X which is the variable that we've created up here that should have the value of the

98
00:08:37,480 --> 00:08:39,060
favorite color.

99
00:08:39,100 --> 00:08:44,290
Go ahead and jump out of that message box and we can see everything is capitalized.

100
00:08:44,290 --> 00:08:49,920
Nothing is turned red so this seems to be the correct code that we need.

101
00:08:49,930 --> 00:08:53,700
Let's go ahead and run this and we'll get an example of how this works.

102
00:08:54,030 --> 00:09:02,610
Minimize your VBA window go to macros and run the example macros one more time we see we have this input

103
00:09:02,610 --> 00:09:03,210
box.

104
00:09:03,270 --> 00:09:05,580
It's asking What is your favorite color.

105
00:09:05,610 --> 00:09:07,580
And it's titled color.

106
00:09:07,710 --> 00:09:13,280
In this case I'll say my favorite color is blue and click ok.

107
00:09:13,580 --> 00:09:21,470
Now I get my example message box which says hello your favorite color is new line once new line twice

108
00:09:21,860 --> 00:09:26,160
and then the favorite color that I just input it blue again.

109
00:09:26,360 --> 00:09:31,460
My title is so example my buttons are still yes and no because that's what I've preset the message box

110
00:09:31,460 --> 00:09:32,200
to be.

111
00:09:32,300 --> 00:09:37,580
But now we have a better understanding of how to create message boxes how to string together text and

112
00:09:37,580 --> 00:09:41,970
how to use input boxes for simple user interaction.

113
00:09:42,020 --> 00:09:48,240
The last thing we're going to talk about are alert messages in Excel and how we can turn them off.

114
00:09:48,290 --> 00:09:54,170
Now we've seen that it can be really helpful to build display boxes and message boxes to help communicate

115
00:09:54,230 --> 00:09:55,690
with the user.

116
00:09:55,700 --> 00:10:01,490
Excel has some of its own internal message boxes and display alerts that will give the user when it's

117
00:10:01,490 --> 00:10:08,120
trying to do something like save a file when you try to save a file in the background in the VBA code.

118
00:10:08,150 --> 00:10:13,430
Excel might prompt the user with the message box asking whether or not the user really wants to save

119
00:10:14,270 --> 00:10:19,370
it can be frustrating when you're trying to automate a process and you continue to get pop up messages

120
00:10:19,370 --> 00:10:21,200
from Excel itself.

121
00:10:21,200 --> 00:10:26,000
There is a way to turn off these alert messages and that's what we're going to talk about if we open

122
00:10:26,000 --> 00:10:31,760
up our VBA window and go up to the top of our code here.

123
00:10:31,760 --> 00:10:41,200
We're going to type application dot display alerts equals false.

124
00:10:41,410 --> 00:10:47,810
Now let's break this down for a second application tells VBA that we're dealing with the Excel application

125
00:10:47,930 --> 00:10:54,320
specifically this file that we have open the dot moves one level further into the options that we can

126
00:10:54,320 --> 00:10:56,900
apply to that application.

127
00:10:56,900 --> 00:11:02,840
In this case we're looking at the display alerts property of the excel file and we've set it equal to

128
00:11:02,840 --> 00:11:08,300
False which means Excel will no longer display alerts that are intrinsic to excel.

129
00:11:08,540 --> 00:11:13,600
It will still display your message boxes your input boxes your user forms.

130
00:11:13,640 --> 00:11:19,750
It just won't display messages such as a save message or an overwrite message.

131
00:11:19,760 --> 00:11:25,430
Generally speaking it's best practice to make sure we always turn these back on at the end of our code.

132
00:11:25,610 --> 00:11:36,050
So right before we end the sub we'll type application dot display alerts equals true now in this example.

133
00:11:36,050 --> 00:11:42,020
Anything that falls between this display alerts equals false and the display alerts equals true would

134
00:11:42,020 --> 00:11:47,030
not be subject to excels internal messages and alerts and pop up windows.

135
00:11:47,030 --> 00:11:52,430
So I recommend doing this when you're doing a lot of save options or things that might be creating pop

136
00:11:52,430 --> 00:11:55,590
up windows that the user would have to continue to interact with.

137
00:11:55,760 --> 00:11:57,260
They'll just slow down your code.

138
00:11:57,350 --> 00:12:01,610
So it's a good idea to use display alerts equals false as long as you turn it back on.

139
00:12:01,610 --> 00:12:02,660
At the end of your code.

140
00:12:03,460 --> 00:12:10,400
So in this video we covered message boxes and put boxes and how to control excels pop up windows.