Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:00
in this exercise,
00:02
I'm going to show you how to make your own quiz or test in Excel using my crows and how to make it completely looking like a professional piece of software asking the questions.
00:19
So, um, let me first guide you through the experience, and then I'll going to explain to you how
00:27
the whole thing is working.
00:31
So we have two buttons on the first sheet.
00:36
And actually, if a student would open this these two sheets down here, worksheets, answers and questions would be hidden
00:44
like I showed you before. So you know how to do it using the
00:51
the variable that puts, you know, the XL one that
00:56
or hides the sheet completely.
00:59
So we're not going to do that because we need these sheets to explain to you how this works. This is just one of the ways to do it, but it's the simplest one.
01:07
So first of all, you click on the intern, your name, and this is the instruction that students get. So each one of them has to sit in this file and they type their name. So ask said, okay, and this is it. So I'm just entering my name.
01:23
And now the step two is to start the test.
01:26
And this displays
01:30
question one.
01:32
You get the
01:34
standard form
01:36
and you get to choose one of these three
01:40
so it can you can choose just one.
01:44
And the space here is open so that the question can be longer. And also, these answers can be much longer if needed. So the full line. So something about 30 40 characters. You can also play with the size of the font and everything.
02:01
But this is it so
02:05
and then you click, submit, answer.
02:07
And then another one appears. So these air trivia questions not to really test,
02:14
so
02:15
Ah,
02:16
you just answered them.
02:20
You cycle through all of them.
02:23
What you see here is that this is appearing on my second display. So I'm
02:30
just changing. Moving gets around. And so this is it.
02:36
Emily is done. It's finished. So we could have placed the message
02:42
that's finished. Save the file. Go home. Whatever.
02:46
And we can do that later.
02:47
And if you see here in the sheet answers, there is my name. And there is a number off books I have selected to. Ah,
03:00
So it was like first box, second box, third book. So this is too. This is tree. This is one. Just do this history.
03:08
Now I have
03:09
in third sheet in questions all these questions
03:15
and I also have,
03:17
ah,
03:20
possible answers.
03:22
And here I have the correct ones.
03:24
So, uh,
03:28
these numbers here
03:30
they're used for models to communicate between themselves and some other stuff. So we can simply hide these rose so that the teacher don't accidentally
03:43
click on them and change something in the way they shouldn't
03:47
our delete them of which could generate the problem for microbes to work.
03:53
But in general, this is the function. So we also have some control boxes here, and we can hide them, for example. But I will leave them just when I while I'm running through codes so you can see that perfectly nice.
04:12
And you understand what I'm talking about. So, um
04:16
Okay, so let's ah, let's go to the visual basic
04:24
and let's move it here. Let's reduce the size so we can look at the code and
04:32
the file at the same time.
04:36
So, first of all, we have user form
04:40
and you could look and you can look at this year's of form. So an editor, this is how it's done.
04:46
And I'll show you how to do these things on another form.
04:51
So this is how it looks. So let's ah, let's make something that looks exactly or almost exactly this. We go insert,
05:00
we go to use their form,
05:02
and then we get another window. So we just first resize this
05:09
and you see relieve these open and now we have toolbox.
05:14
So first of all, we select label. So this where the question is going to be is called label
05:21
and we put their label here, and later we're going to change the text of discs this label through macro.
05:30
Then we put three
05:35
buttons
05:39
Option button, one
05:41
option button, too,
05:46
and option but three.
05:49
Anna, didn't
05:53
we click and put this submit? So command button
05:58
and this is it.
06:01
Now we can click on these three buttons here
06:06
and for example, look, att Ah, there with and let's make it the same. So it's put it like 380.
06:17
So they're all here,
06:20
and it's also very important thing. We group them together.
06:26
So we've put group, for example answers
06:30
here
06:31
they're now grouped, which means that
06:35
they are old, um,
06:40
linked together.
06:42
So when you have seen how it works, when I click on the one button, the other automatically gets be selected.
06:48
So when I click unjust if one of these buttons here have this left. Okay, so the height is not the same, but the way it is, So I can now adjust the height
07:01
more with a little bit around It took me some time to fit them together. To be
07:08
exactly precise is they should be. But this is it.
07:12
And this is our form.
07:15
And we're not going to save this one just not to mess with this file because you're
07:19
having that filing. Your resource is Deb.
07:23
Uh, but this is pretty much it and be just
07:29
close it.
07:30
Sorry,
07:31
Andi. Eso user form to I'm going to remove your user from Do they want to export? No, I'm not. So this is it. So I have ah, you see, in the form I if I selected, I have also changed the color off the background and I changed the color
07:51
off these buttons. So
07:56
this is background color so you can paint it with every want. The same thing is with this one. So, for example,
08:03
I can I can change this to take your palate and I can make it something like this. But I'm not. So
08:16
let me just
08:18
Yeah,
08:20
this
08:20
we won't save it. So this is it. So this is form
08:26
and this is the code behind it
08:30
And their two subs 1st 1
08:33
is this is the first and most important one.
08:37
This is ah,
08:39
user form initialized.
08:43
And you see that I don't have here. User form one initialized. Now this is user form initialize. This is macro that execute when you opened this form, when you call it from some other
08:56
17 or or function
08:58
and I'm basically the mentioning roll numbers, integer and row number is and look at this sheets Question cells 11
09:07
So this is it,
09:11
uh, at the beginning, the software that the other macro will reset this to two. And this is basically telling this form where to look for
09:24
the question, the possible answers. So this is it
09:30
and leads back to it, and then we have the whole initialization procedure. So we would say, use their form captured.
09:39
And this is important. You see here when you click it
09:43
to use a form look and we get captured its user form one. So it's not changed its ah, generic one.
09:54
But here
09:56
I will put in that caption. So, user for one, that caption this is evoking this cell here.
10:03
The caption still
10:05
and I say it is
10:07
questions with space and
10:11
row number minus one. Why? Because I'm starting with drone number two. And actually, that is the first question.
10:16
So you have seen in the form it was question one.
10:22
And then I have label
10:24
one,
10:26
which is this This is label one, do you see?
10:30
And it's kept Shin is
10:35
ah,
10:35
sheets questions
10:39
that sells row number, comma one and then value.
10:45
And this is Rome. Number one A room number two, because it's on in initially is going to be too
10:52
cold. One
10:54
same thing happens with
10:58
option bash in buttons captions. So she's question sells rule number, the same neural number and then 234 and they're closing this up. And we're going to continue with the explanation off all these crows in the next
11:13
video

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor