Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:01
at the end of this module in this. Listen, I'm going to show you some examples off complex Macron's with
00:09
idea to solve really life problems or something that looks like, really like problems. So we are not going to beat around the bush, and we're going to go directly.
00:21
Two
00:22
the example. So here
00:26
in this,
00:27
um, worksheet, we have some kind of
00:32
student answer.
00:34
Ah, sheet where they can answer to ABC questions on a quiz.
00:41
And at the end, when they click on submit results,
00:46
they get just information if they have passed or not.
00:49
So they don't get information about the percentage.
00:53
Ah, they had They will get that later. Why? Because if if they get this percentage, they can play with it until they pass. Because this is a B. C. They don't need to put any car, really answer.
01:07
So we're going to close this
01:10
and look at the answers. So here are the answers to that quiz. So they're 20 questions,
01:18
and we want first to make
01:21
this.
01:23
Aah! Answers calculated.
01:26
And then we want this answer sheet hidden
01:33
so that
01:34
Aah students cannot look at the answers.
01:38
Also, you notice here that there is ah,
01:42
word test in a cell. And one This is actually a password that I have typed in
01:49
That will be ah, needed to unhygienic sheet for, ah, teacher or professor if they want to,
01:57
uh, change the answers to the quiz for
02:01
next group or next semester or whatever.
02:06
So Ah, I'm keeping this Ah ah
02:09
password in this sheet because it's will be hidden so students won't be able to find it in no way because they wouldn't be ableto excessive
02:17
Now we can, of course, hide this sheet.
02:22
But then anybody can unhygienic it,
02:24
just clicking right. Click on any tab and clicking the answer sheet.
02:30
So this is not good. We want to hide it
02:35
properly.
02:36
So let's first look att. The submit results
02:39
My crew
02:42
and ah, this macro
02:45
is here. It's called Submit
02:49
no small under
02:51
and it's very simple.
02:53
So we are defining I as a counter is integer
02:59
and we are the defining. Some off answers total some off correct answers as interview as well.
03:08
So we're seeing some of some of correct answers. Zero and then we go from
03:15
2 to 21. Why?
03:17
Because in the
03:21
answer started row two
03:25
and then
03:28
we ask if
03:30
range, eh?
03:32
And I
03:35
So it can be a two. A three. A four up to a 21
03:39
is equal.
03:42
Two sheets answers that range.
03:46
And then
03:47
again, A and I.
03:50
And why? Because if we look at the answers,
03:53
I have also
03:57
but hear answers the quiz. So to correspond. Row to Crispin's in Answers correspond to roto in,
04:03
um but student gave us an answer and so on and so on.
04:10
And, um,
04:14
if that is correct, then we're adding one,
04:18
two,
04:20
some off correct answers,
04:24
or else we do nothing and then re close. And if technically else could have been omitted, because then we would have, if then
04:33
structure. And that's completely legal.
04:36
So though
04:39
80% is needed to pass this test,
04:42
and that's 15 out of 20
04:46
um, and
04:47
hello,
04:48
Sorry, 75% is needed to pass this test so it's 15 out of 20.
04:55
And so if some off answers is equal
04:59
or greater than 15
05:00
then the message books is past
05:04
else. Message books. It's failed and if ends up. So this is it. Very simple
05:11
macro that will simply count the correct answers. So if we look in the current sheet
05:16
and
05:17
ah, look, it's for the correct answers in
05:21
answers sheet
05:24
and that's it. So we have calculated that
05:29
now we want to
05:31
look a TTE
05:33
how to hide this answers
05:38
sheet.
05:39
And if you look here,
05:43
there is no
05:48
micro for hiding it. I have written it, but it's not visible.
05:53
I know it's name and it's called Hide
05:57
Sheet
05:59
and I want to edit it
06:00
and it has just one line.
06:02
But look at this. This is very important. It has private
06:06
before sub, and then it's called high Cheat.
06:11
So what it does
06:13
is
06:15
looks at sheets.
06:16
Pyramids were visible
06:18
and the sheets we are looking to set to the pyramid, therefore, is answers, of course.
06:27
And we put the value off Excel very hidden.
06:31
So this value
06:34
well, tell us
06:36
that, uh,
06:40
it will be hidden in a way that it cannot be hidden
06:44
by clicking right clicking here
06:47
and
06:49
doing on Hyde. We don't have this because we don't have any human sheets at this moment.
06:55
So if you go back to visual basic,
06:58
it's very simple
07:00
micro.
07:02
And if we execute it,
07:05
sorry.
07:13
So if we execute
07:15
this mackerel? Let me just close this.
07:18
So if we go to this one,
07:21
go macro and we type hide
07:28
sheet
07:30
and we see run.
07:31
This one is human
07:33
and great Click on height is not possible
07:39
So
07:40
we want to unhygienic it.
07:43
Go here
07:45
and we click on this module here. Now this one has a slightly different name because I didn't enter it from this dialogue where I typed the new name and clicked on create I went to directly to visual basic and open the new module.
08:03
So let's first look Att This,
08:07
uh, my crew
08:13
So
08:20
it goes to this user form
08:22
and show
08:24
and it's it's it.
08:28
And the user form
08:30
is this.
08:33
And if you click here and say show, coat
08:37
your coat
08:39
So basically this one starts this user form
08:43
and let me just execute it first so you can see what it does.
08:48
So if I click on submit results
08:50
get past
08:54
and if I execute the macro here,
08:58
they say ran.
09:00
I get this user form So again I have a dialogue box butts now interactive that log books
09:07
and I typed test
09:09
and I think okay,
09:11
it doesn't work. I have to click physically here and it gets on him.
09:16
I can now change this and then I can use the other
09:20
passwords. If I found out that somebody has found that the password this So let's go and look. Att The code
09:30
and it's very simple. So if text box one value So this is textbooks. One
09:37
equals this world book that Sheets answers the strange and one
09:43
well, you. So we're looking at the value off cell and one in sheet answers, although it's over the hidden so it's not visible.
09:52
Then
09:54
we go to sheets
09:56
answers visible equals true. So remember, we
10:01
plays their excel very human, which is a special code to hide it completely.
10:07
Otherwise, we could just get the message books wrong
10:11
to end this,
10:13
and we do unload user form, so we remove it from screen so that dialogue box with the password will be gone as you have seen.
10:22
So this is very simple. I won't go at this point into how to create this user form. We'll address that in module eight.
10:31
So this is all
10:33
about how this
10:35
ah
10:37
macro is work and how to shut hide the sheet, so it's not that complicated
10:45
and it can be done with couple lines of coat,
10:48
and, uh,
10:50
that concludes this lesson.

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