1
00:00:00,860 --> 00:00:06,070
So in the last couple of videos we've built this employee creation forum.

2
00:00:06,110 --> 00:00:12,740
We went ahead and tied all of these various objects on this forum to code and we built our little show

3
00:00:12,740 --> 00:00:19,100
form codes so now we know when we click on the ad employee button here we get this form that shows up

4
00:00:19,250 --> 00:00:20,290
we can fill this out.

5
00:00:20,300 --> 00:00:25,160
We can add new employees to our table here and we know all of this is working smoothly.

6
00:00:25,220 --> 00:00:30,840
So our next step is to go ahead and add a list to our roll combo box here.

7
00:00:30,980 --> 00:00:34,100
And this is something that you actually want to do when the form loads.

8
00:00:34,130 --> 00:00:39,560
So as the form is opening for the first time we have the chance to interact with the form before the

9
00:00:39,560 --> 00:00:41,120
user can even see it.

10
00:00:41,120 --> 00:00:47,060
So this is the kind of situation where you might want to if you wanted to lock down some of these objects

11
00:00:47,060 --> 00:00:52,400
that these are couldn't type in them or if you wanted to hide some of them and set the visibility property.

12
00:00:52,400 --> 00:00:56,710
This is all stuff you could do as your form is loading for our purposes.

13
00:00:56,720 --> 00:01:02,570
We can go ahead use form load to create our dropdown list but we could also use it to preset some of

14
00:01:02,570 --> 00:01:08,420
these values to be either true or false as as in the case of this checkbox or we can preset some of

15
00:01:08,420 --> 00:01:11,390
the named values or the start date or anything like that.

16
00:01:11,420 --> 00:01:17,090
So that's what we'll be covering in this video is on the form loading going ahead and pre filling or

17
00:01:17,090 --> 00:01:20,430
pre determining some of these values.

18
00:01:20,460 --> 00:01:24,870
And so the way that we do that is by creating a subroutine.

19
00:01:24,870 --> 00:01:33,030
So we'll say private sub and this time we're going to take user form underscore initialize open parentheses

20
00:01:33,060 --> 00:01:38,670
close parentheses enter and we can see up here these two dropdown tab automatically updated which is

21
00:01:38,670 --> 00:01:39,960
what we would expect.

22
00:01:39,960 --> 00:01:44,910
So this is telling us we are interacting with the object user form which is this form that we've built.

23
00:01:44,910 --> 00:01:46,700
That's what this is it's a user form.

24
00:01:47,220 --> 00:01:50,130
And on the initialization of this object.

25
00:01:50,160 --> 00:01:56,710
So as we load this form as we open this form we're going to do whatever code we put into here.

26
00:01:56,730 --> 00:02:03,420
So this is where we could preset some values or build our list for our combo box.

27
00:02:03,420 --> 00:02:06,670
So let's start by pretty filling our date.

28
00:02:06,780 --> 00:02:09,590
So maybe we want the start date to be today's date.

29
00:02:09,600 --> 00:02:14,260
Maybe this is a form that we fill out on the very first day that the employee starts work.

30
00:02:14,280 --> 00:02:18,990
So the way we do this is by referencing our start date text box.

31
00:02:18,990 --> 00:02:25,320
So we'll type start date text which is the name of this text box that we put onto our form and we'll

32
00:02:25,320 --> 00:02:30,890
say that the value of this text box is going to be date.

33
00:02:30,900 --> 00:02:38,320
Now we can see date automatically capitalizes that's because this word date has meaning in VBA already.

34
00:02:38,340 --> 00:02:40,980
That's why we can't create a variable that's called Date.

35
00:02:40,980 --> 00:02:46,200
It would confuse the code later on as to whether we're referencing our variable or referencing what

36
00:02:46,200 --> 00:02:49,800
VBA currently has tied to the word date.

37
00:02:49,800 --> 00:02:52,440
Now date in VBA means today's date.

38
00:02:52,440 --> 00:02:59,760
So when we actually run this code we should see the start date cell up here automatically default to

39
00:02:59,790 --> 00:03:01,260
today's date.

40
00:03:01,260 --> 00:03:07,350
The next thing we want to do is go ahead and build our roll combo box list that appears when we select

41
00:03:07,350 --> 00:03:08,430
that dropdown button.

42
00:03:09,360 --> 00:03:12,840
So we're actually going to do this with a with end with statement.

43
00:03:12,840 --> 00:03:18,870
And that's because we don't want to have to continue to reference this roll combo box over and over

44
00:03:18,870 --> 00:03:19,640
again.

45
00:03:19,680 --> 00:03:25,050
So I'm going to type with roll combo hit enter.

46
00:03:25,050 --> 00:03:27,450
We'll go ahead and give myself a line of room to work with.

47
00:03:27,450 --> 00:03:28,130
And I'm going to type.

48
00:03:28,140 --> 00:03:28,890
End With.

49
00:03:28,890 --> 00:03:34,500
Before I forget that we're sure we close out this with statement and now roll combo again is just the

50
00:03:34,500 --> 00:03:41,150
name of this combo box if I click on this we can see it's called roll combo so I'm saying with my roll

51
00:03:41,150 --> 00:03:48,860
combo box I want to add an item in this case I want to add the item we'll say design is the first option

52
00:03:50,460 --> 00:03:59,630
maybe we also have sales maybe we also have production and maybe we also have manager.

53
00:03:59,630 --> 00:04:04,600
So now each of these add to each of these items should have been added to the list.

54
00:04:04,610 --> 00:04:07,980
That appears when we click on this role drop down menu.

55
00:04:08,000 --> 00:04:13,580
So as we load this form all of these items are going to get added to the potential list for our roll

56
00:04:13,580 --> 00:04:14,980
combo box.

57
00:04:14,990 --> 00:04:20,750
Now if we wanted to clear off our roll combo box and empty it out at the beginning of our form we could

58
00:04:20,750 --> 00:04:29,800
do that by typing roll combo dot clear and that would MDL anything that's currently stored in our combo

59
00:04:29,800 --> 00:04:32,610
box or roll box that we have there.

60
00:04:32,620 --> 00:04:39,130
So now we can go ahead and go back to our main excel file here and this time when I click on the add

61
00:04:39,130 --> 00:04:44,110
employees button we have something that we expect to happen as the form opens so we'll click on this

62
00:04:44,110 --> 00:04:48,730
to see if our code works and we'll see as this pops up.

63
00:04:48,850 --> 00:04:52,650
We have a pre entered start date which happens to be today's date.

64
00:04:52,840 --> 00:04:58,690
And again that's what we would expect seeing as we preset this value to be today's date and in our roll

65
00:04:58,690 --> 00:05:04,450
if we click on this dropdown we can see our various options that we wanted to pre build into our role

66
00:05:04,450 --> 00:05:10,960
have been created in this list and I can either select this or I can type in my my own new role and

67
00:05:10,960 --> 00:05:13,480
that's fine since we use that combo box.

68
00:05:13,480 --> 00:05:18,790
So I'll go ahead and close back out of this and you can see how it'd be pretty easy to build a simple

69
00:05:18,790 --> 00:05:22,930
loop to go ahead and add these items if you wanted to make this dynamic.

70
00:05:22,960 --> 00:05:31,180
So if we wanted to have a Rolls list somewhere else on our table and say we have roles here design production

71
00:05:31,780 --> 00:05:38,530
sales manager it would be pretty easy to build a quick loop that would run through this and add each

72
00:05:38,530 --> 00:05:46,630
one of these cell values into our combo box list by adding an item and instead of typing design typing

73
00:05:46,960 --> 00:05:51,030
cells whatever cell we're in value something like this.

74
00:05:51,600 --> 00:05:56,830
And that would be pretty simple to do as a loop if you wanted this to be a dynamic list that you could

75
00:05:56,830 --> 00:06:02,590
control from the Excel file rather than having to come into the code itself to control it.

76
00:06:02,590 --> 00:06:09,820
So in this video we talked a little bit about opening up your form and how we would drive and preset

77
00:06:09,820 --> 00:06:12,130
some of the values on opening the form.

78
00:06:12,190 --> 00:06:17,920
We also talked about how to create the lists that will populate your combo box in your list box which

79
00:06:17,920 --> 00:06:22,840
you would do the same way you would just change whatever the name of this combo box is to be the name

80
00:06:22,840 --> 00:06:24,160
of your list box.

81
00:06:24,160 --> 00:06:29,230
And so now that we know how to do this we can go ahead predetermined values on our form and build our

82
00:06:29,230 --> 00:06:31,180
lists on our form as we open them.

83
00:06:31,180 --> 00:06:34,360
So it's super helpful tool to use user form initialize.