Build a Pop up Calendar for Excel #2

Part 2 of 3. I build the pop-up calendar and start to write the VBA code. A good introduction to building VBA UserForms in Excel.

---

Closed Caption:

I'm not in green and this is the second
of my video demos showing you how to
build a pop-up calendar for Microsoft
Excel these demos are designed to
accompany the tutorial at my website at
www.soleyn.com where you can find a
complete tutorial which is fully
illustrated with screenshots and gives
you step-by-step instructions for
building the calendar in part 1i showed
you what the counter look like and what
it could do and in this part i'm going
to start building the counter and write
some of the code that powers it i'm
going to be doing that in the visual
basic editor and to get to that i'm
going to go to the Developer tab in
excel and choose visual basic I could
also use the keyboard shortcut out + f11
to get here now if you haven't been here
before
i'll give you a quick tour around the
main part of the screen is divided to
the area where we actually write our
code and design our user forms at the
left-hand side there are two windows the
upper one is the project Explorer and
that is where you're showing what's
currently going on in Excel for example
it's showing us that the personal macro
workbook is open and also an unsaved
work book called but one and that's the
world book in which i'm going to create
the kinder underneath is the properties
window which shows you the properties of
whatever happens to be currently
selected in the visual basic editor now
the first thing I've got to do here is
insert a userform which will be the
basis of our kinder into the book that
I'm going to be working in which is
booked one and to do that I'm going to
right-click on its name choose insert
and userform now when these upon opens
the toolbox also opens if you don't see
the toolbox
it could be that it was left switched
off last time there's the button for
switching it on or it may be that if the
toolbox disappears once you're working
it's because the userform has become
deselected I'll kicking this window just
to be selected as a form and all you
need to do is just click on the userform
again to get the toolbox back the first
thing i want to do here with the
userform is change some of its
properties it has a name user for one
because the visual basic editor
automatically names everything and I
want to give this a more friendly name
I'm going to call it frm perform
calendar also want to change the caption
which also says use for one something a
bit more relevant so i'm going to change
that to pick a date now at any time you
can check what your user form looks like
by actually running it like a macro so
that it appears in excel and to do that
you can go to the run menu and choose
run the userform or you can click this
Run button here or you can just press
the f5 key on your keyboard
i'm going to click the Run button and
here's the userform in Excel just as the
user would sit it has a close button and
of course there is the caption that i
gave it now when i close the userform
because I came here from the visual
basic editor that's where it'll take me
back
everything that I want to do right now
is to create a macro that will open the
userform from excel so i can open it as
the user would open it and to do that I
need a module to put my code in and i'll
do that in the same ways i created the
calendar userform i'm going to right
click here but this time
insert module and so here i am in this
new module which is called module 1 i'm
going to write a very simple macro that
just will open the calendar userform i'm
going to call it sub open calendar when
I do that it creates the end sub line
for me and I just need to put my coat in
between and i use the name of my
userform which is frm kinder followed by
. it gives me a list of all things that
i can type in this context right now I
need to touch just the word show and
that is sufficient to open the Conda now
whenever you write code and test it
it's always a good idea first to check
your code i know this is really simple
and doesn't really need checking but
it's good idea to get into this habit
you go to the debug menu and choose
compile
now when you do that the visual basic
editor checks as best it can become that
you've written as long as you don't see
any other messages you know that your
code is pretty well gonna be okay
I didn't see any other message is if you
need to check just go back to the debug
menu and you can see that compile is now
great out which means there's nothing
new to compile and the visual basic
editor is happy with your code the first
time you do this then what you want to
do is save your workbook and we get into
the habit of debug compile save this is
the first time this workbook is being
saved is taking us to the save as dialog
and we need to create a name for our
workbook and what's really important is
that if you written any code in your
workbook you must save it not as an xls
x which is the default but as an xls m
which is a macro enabled world book if
you don't save it as an xlsm then exhale
will discard your code it will warn you
but you might not read the error message
which is what a lot of people fail to do
and I've got also to give it a sensible
name so I'm just going to call it
calendar kinda xlsm and i'm going to
save it seems I've already got one so
i'm going to replace it
ok so I've created my basic user forum
and I've also written a macro which I
can run from Excel now so I don't have
to run it from the visual basic editor
and the reason I want to do that is
because I want to be testing it with the
same experience as the use of my heart
so go on the development up to the
macros button there's also macros button
on your view tab so it doesn't matter
where you get to it from there's my open
counter micro i run it and the userform
peers no calendar on the ear of course
the difference now is that when I close
it i don't get immediately returned to
the visual basic editor i'm going to use
my keyboard shortcut
I'll ever learn to take me there now the
first thing that I want to put on my
userform is a button so i'm going to
choose a command button tool here and
just click on my form now the purpose of
this command button is going to be to
close the form you might be wondering
why is he going to put a commandbutton
i'm going to close the form if he's
already got a button up here that does
it but there's a special reason which
you'll see in just a moment but first of
all i'm going to give this command
button a sensible name CMD close i'm
going to change its caption to close
although the users actually going to see
this button as you'll see in a minute
and i'm also going to change the council
property to true and this is the key to
why I'm actually creating this command
button and that's because i want to use
it to be able to close the form right
pressing their escape key now the plan
is that when the user picks a date the
date will get rid of written into a cell
or cells and the form will close
automatically but if the user opens the
calendar and then changes their mind
decided I didn't want it i want to make
it easy for them to close it and most
people are used to the fact that they
can just hit their escape key and a
dialog box will go away
that's what's going to happen here if
you said the council property to true
that has the effect of pressing that
button then any code that is attached to
the button will run because that code is
going to tell the user forum to close
that little close the form that code
doesn't exist yet so you need to write
it which means we need to get into the
code module for the userform the quick
way to do that is just to double click
on the item that you want to write code
for so i'm going to double click the
close button
it's taken me into the code module for
the form and that doesn't show over here
is a separate module you can get to it
by double-clicking on an item or double
clicking on the form as I just did or
you can right-click on the name of your
user forum and choose view code the
visual basic editor has created an empty
event procedure for me it's called an
event procedure because and then invent
procedure its code is run automatically
when that event happens and this is the
click event of the CMD close button in
other words when the user clicks the
button the click event happens and
whatever code i put here will run and
the code is going to be very simple
tap-in for tightness there and my code
is unload which says closed me now the
word me in this context refers to the
user forum in which this code is located
i could equally instead of writing me
could have written frm calendar but I've
chosen to write me because that always
refers to the current object and if i
decide at some stage maybe the change
the name of my car I don't have to find
all instances of it that I've used and
correct because I
always have referred to it as me again
now we can debug and compile and save
now this time we don't get shown the
same dialogue because remember we're
already in a saved workbook and it's
just doing a sequential save let's check
this out we'll go to excel quick way to
do it was cooking excel button here I'll
go to my macros dialogue and run my open
calendar macro now i know that if i
close by pressing the close button it
should close the form
yes my code works what about having it
closed when i press the Escape key
let's get the counter open again so
what's my master . is over here i'm not
going to click that button i'm going to
press the Escape key now and the dialog
box goes away which is exactly what I
wanted it today I'm going back to the
visual basic answer and the next thing
I'm going to do is to put the actual
calendar on the form now I don't want to
see this close button there's no need
for the easy to see it if they want to
close it using the mouse they can use
this button up here so i just want to
move this little bit over to the left so
that i can ensure that it will be hidden
by the kinder itself which when I put it
on the forum will go on top now I've got
the month view control which will be my
calendar itself here on my toolbox but
you probably not yet gone now on your
toolbox if you haven't got it there you
need to ask for it simply right click on
the tool box go to additional controls
and scroll down the list until you find
Microsoft month view control will see
mine is already selected because i've
used it before the number that you'll
see him
might be different depending upon which
version of Excel you're using it but
simply put across in the box and click
OK and the month view controller appear
on your toolbox in order to use it as
before click on the button and click
somewhere on the form so there's my
calendar i'm going to move it over here
for a moment now a calendar control has
got lots of different properties for
example you can show on multiple months
arranged in rows or columns you can
format it in different ways choose a
different font and color scheme anything
that I want to change is this sort of 3d
effect that you can see I prefer mine to
be flat and i'm going to change your
here on the appearance property change
it from 3d to flat it's already got a
name month you 1 i'm not going to change
that but i'm going to move it up into
the upper left corner of the userform
and now i'm going to resize the userform
so that it fits the kind of control i'm
going to click on the user form so that
the user forum itself is selected and
use its resizing handles these white
rectangles on the side just to as best i
can match the size of the userform
itself answer in a bit to the actual
counter because there's nothing else
going to go on the form
ok I'm going to check it out by running
it from here and there it is and i can
press my escape key and it'll go away
now the way that the user actually gets
a date on to their work sheet is by
choosing one of these day numbers here
they can choose to change the month of
the year but it's only when they choose
the day number that the userform will
close
but that again that won't happen
automatically I need to write the code
to tell it to do that so i'm going to
double-click on the calendar and again
the visual basic editor has created an
event procedure for me it's the date
click event in other words when the user
clicks on a date of the month you one
control
I've got some choices here the first way
that I'm going to do it is to have the
calendar just insert the date into a
single cell in other words the active
cell of the current selection the first
line of code that i'm going to write is
an error handler on error resume next
and also notice that i tend to type in
lowercase when i'm writing code and
that's because when i move out of the
line that i just typed as necessary the
code will jump into rubber case and
that's a quick visual cue to me that I
have made a typo
now the purpose of an error handler is
to tell Excel to ignore any code
statement that it can't run if I didn't
do that the macro would actually going
to error
I don't want that to happen now I don't
need to write a more complex
errorhandler because it's quite safe for
me to tell it just to ignore any errors
it's not always safe to do that and you
should always use air handlers like this
with caution and probably write a more
specific her handler but this will do
for what we're doing in the moment more
error could happen well suppose a
particular cell that we want to write
the date in has been protected you can't
tell that sounds protected until you
actually try to type in it and then in
Excel you get a world warning message
telling you that it's protected cell but
what if you try to write into a cell
in code excel doesn't bother to display
the same error message as it would show
the user instead the code crashes and
that's not what you want to happen we
want to avoid that happening at all
costs
so this simple error handler will deal
with that it will ignore the message to
right into the cell if that cell is
protected so how do we tell it to right
into the cell we refer to the active
cell so active cell . value equals me
again I'm using the term me to refer to
the current user forum . month you one .
value so when the user chooses date the
month view control is given the value
that is that date and we just
transferring that value into the active
cell and finally we want the userform
too close so again is unload me debug
compile and save I can take cell now
I've got a single cell selected so let's
call out that macro and we'll choose a
date and it's written the date into the
cell
what if we choose more than one cell
what happens we choose a date which is
different day
it's only written into the active cell
when you have more than one cell
selected one cell is always the active
cell and because we only told it to
write the date into the active cell
that's what it's done i'm going to
assume that when more than one son is
selected the user once the date written
in
to all the selected cells so i need to
modify the code a little bit so I'm
going to take myself back into the
visual basic editor i'm going to go back
into the code and i'm going to change
this so it writes into all the selected
cells
no just do that I don't need this
particular line of code because i'm
going to change that so I'm just going
to select and delete it
making a space heater on my code now
what I'm going to say to do is to visit
each selling the selection and what it's
doing that the cell that it happens to
be on a time i'm going to represent
using a thing called a variable which is
just a little bit like a label for
something and I'm going to declare that
variable in other words Warren excel
that i'm using this particular word to
represent the cell not I'm actually
going to use the word cell as object so
that was just telling itself that when I
just use the word cell in my code is
just referring to something which is an
object i'm going to create a loop for
each cell in selection . cells in other
words within this the cells that are in
the selection do this to each one so not
active cell because active cell would
still refer to that one active cell in
the selection sell refers to the cell
that we happen to be visiting at the
moment . value it cause type
value equals may . 131 . value i hadn't
created that that typo excel would have
shown me by highlighting the lion read
as soon as I tried to come out of him
now that's all it's got to do as it
visit each cell so the last part is next
cell and all that says is go to each
cell in turn right the value of the
calendar into the cell then go to the
next cell in the selection when he gets
to the end and has written it's right
into the last tell the loop finishes and
at that point i want the kind of cut too
close so I'm just getting rid of these
entry lines here so that should do
everything that I want to compile and
save and let's test it from excel so
here we are I've got a comma
self-selected let's go to let's find out
macros that we are and run to open our
kind of let's choose a different year
for example and it's written the date
into all of those cells even if I have
what we call a non-contiguous selection
in other words a selection of cells that
are not necessarily sitting next to each
other and I'm doing that by holding my
control key down as I select the cells
called my kind up and if i asked to put
a specific date n it's written that day
into each of those cells and the loop
has achieved that
so we're now at a point where the Conda
is doing everything that we wanted to do
I've got a couple more jobs that i
wanted to do for example I want to have
a right-click menu because it's a little
bit inconvenient for the user to have to
run macros to open the calendar i want
them to have an item on the right click
menu of a cell so they can choose
something here and the counter will open
i also want to have a keyboard shortcut
so that when they make the keyboard
shortcut the counter will appear
I want the calendar to show the current
date so if the seller is currently the
active cell has already got a date in it
i wanted to show that date and finally
I'll be saving this workbook as an excel
add-in which will make it easy for users
to distribute the code that will come in
the third and last part of this tutorial
remember that you can follow the
tutorial from start to finish on my
website at homestead.com