Build a Pop up Calendar for Excel #3

Part 3 of 3. Here I write the code that creates a keyboard shortcut and adds Insert Date to the context menu of a cell. Then I convert the project to an Excel Add-In. A good introduction to building VBA UserForms in Excel and building an Excel Add-In.

---

Closed Caption:

i'm watching green and this is the third
of my video demos that accompanied the
tutorial on my website at www.soleyn.com
and the tutorial shows you how to build
a pop-up kinda excel if you've been
following these tutorials will know that
I've already built the calendar and done
some of the coding and I've written a
macro to open the kinda on the counter
appears the user can choose a year and a
month and a date and when they choose
the date that gets written into the
active cell or into all the selected
cells if more than one is selected and
then closes I want to make some
enhancements to the calendar so for
example if as is the case here the
active cell already contains a date when
the kind of opens I wanted to
synchronize with the date that's in the
active cell which he doesn't the moment
the default is for the calendar to open
showing today's date so i'm recording
this on the 26 of sep tember and as you
can see that's what showing in the
calendar
despite the fact that the eleventh of
$MONTH november 2014 is showing in the
active cell so i need to write some code
so i'm going to close the counter and go
to the visual basic editor i need to get
into the code module that's attached to
the userform the counters on quickest
way to do that is to right-click on the
userform over here and choose view code
so now I'm in the
code module for the userform and I need
to create an event procedure for the
initialize event of the userform the
initialize event happens when the
userform opens and we use it to prepare
the form for use for example we might be
putting default values into text boxes
or building lists for drop-down list and
this box is that sort of thing so the
way that I get to that procedure is by
picking from the left-hand list here
use the form now what this is i do that
it creates an empty of any procedure for
the click event of the use of all I
don't need that I want a different one
so I'm dropping the right-hand list now
that right hand drop-down shows all the
relevant event handlers for the item
that I've chosen the left-hand list and
one I want is the initialize events or
choose that now I can get rid of the one
that gave me which was used form click
and I've got the initialize event and
i'm going to write my code here what I
want the code to do is to decide whether
or not the active cell contains of dates
and if it does to synchronize that date
with the calendar so i need an if
statement to do that and i'm going to
use the his date function and that
function is going to look at the value
of the active cell now you might expect
me to write equals true here but if you
don't write equals true then that is the
default value so you can leave it as it
is so I'll just continue the if
statement then
me the main keyword remember refers to
the actual user forum itself i use it up
there earlier only closed event me month
you one which is the name of the actual
calendar itself . value equals active
cell . player and I finish off the
statement with and if so that checks the
active cell if it's got a date it then
writes that date into the calendar so
that the counter amount of cell but show
the same date remember deeper compile
and save before we actually test any
code i'll run it from Excel this time
I'll open the macros dialogue and run
the counter and you'll see that the
counter has now opened for november 2014
so not the current date which is the
same as the date in there to sell
unfortunately I haven't found a way to
actually highlight the day number you're
supposed to be able to do it according
to microsoft and try as I might have not
been able to do that I think it's a bug
in the calendar
although there is an easy answer reason
it if you know a way to do that or find
a way to do it I can do it in other
circumstances but not using the
initialize event so I'm going to be
satisfied with that as it is ok that's
the first thing that I wanted to change
the next thing that I want to do is make
it so that there will be a keyboard
shortcut built into the calendar so that
the user can give a keyboard shortcut
have a counter appear
and also an item on the right click menu
of a cell so if the user right clicks on
a cell they'll be an item on this menu
that says insert date and when they
click that the country will open so
again I need to go to the visual basic
editor now this time that code to do
those jobs is not going to reside
actually in the userform itself it needs
to reside in the workbook because the
code will run when the world book opens
so when the work but opens and
instruction will begin to excel that a
particular keyboard shortcut will open
the calendar and when the workbook
closes that keyboard shortcut is
cancelled at the same time when the
workbook opens a new item will be
created on the right click menu of a
cell and work but closes that item will
be removed the place to put your code is
in special module called this workbook
module so you just have to double click
to open that particular module and there
are specific event procedures for the
workbook opening and the world book
closing will start off with the workbook
open event so again . down the list
choose workbook and the default event
procedure is workbook open which is the
one that I want so let's start writing
the code now as before
i'm going to put in a simple error
handler here on error resume next
I'll explain the reason for putting that
in just a moment
now i do need to declare a variable here
and the variable is going to represent
the new menu item not among menu school
to control Tim and i'm going to call it
new control as come on bar control so i
should be referring to that variable
later and first of all a bit of code to
set a particular people shortcut in
order to do that you need to talk to
excel so I'm going to start off with one
application and there is an event called
on key on key means when a particular
combination of keys oppressed and you
have to supply it with a code it says as
string there so it means i have to put
it in quotes my code is a plus sign a
carrot and in curly brackets which are
practical braces let us see and what
that code stands for the plus sign
sounds for shift the carrot stands for
ctrl + C and curly black brackets
represents the keypress of the letter C
so in other words shift ctrl c will be
my people choke that we now have
deterred which procedure you want to run
a my procedure will be the macro that I
created that owns the calendar i'm going
to be specific and same module one here
because that's where it lives . open
kinda which is the full name and address
off the macro that I wanted to run
when that particular people shortcut is
gone now the next thing I'm going to do
might seem a bit strange to you but i'm
actually going to give command to delete
the item that I'm going to put on the
right click menu let me just write the
code for that and then i'll explain why
again we're talking to the application
so we start off with application and
we're talking about command bars a
command Bart is the term given to menus
and we is quite rare now a toolbar but
there's a specific one that's the right
click menu of the cell and it's called
cell . controls so we want to talk about
a specific control and that control is
going to be called i made it yet but
it's going to be called insert date
that's gonna be the text of the menu
item and i'm going to say here delete so
why am I deleting something that I
haven't made yet
well this is a bit of a safety net so
that if excel didn't close properly last
time and the code that will delete the
item off the menu when the workbook
contains kind of closes because there's
no point having it now if the count is
not available if that code didn't have
the chance to run maybe because Excel
crashed or something happened to it
of course the next time excel open this
particular workbook the manual would
already be there and your code want to
add another one and you might end up
with multiple instances of that menu
item we don't
on that so the first command we're
giving is telling it to remove that menu
item if it's already there if it's not
there you would normally get error that
point which is why I've got on error
resume next so we tried to remove it if
we can't doesn't matter now comes the
code actually to create that particular
control and this time I'm going to be
referring to my variable up their set
new control
so in other words i'm telling you what
new control is it cause application .
command bars open bracket cell .
controls . add so I've told it to add a
new control to the cell command bar to
the right click menu of the cell have
not yet told it what that control is
already does
so I'm going to use a with statement
because i want to use several different
commands to refer to the same item with
new control its caption is going to be
insert date now be very careful about
your spelling and make sure you spell it
and you write it the same way every time
you referred to it on action in other
words when the item is chosen what you
want to happen i'm going to tell it
which macro to run which as you remember
is module one . open calendar
and finally i'm asking to begin a group
that's an optional one was just says put
it at the end of the menu and put a line
about it just to begin a new group and
separated finally to finish off this
with statement end with so all that is
going to happen when the workbook
happens as the world book opens a new
keyboard shortcut is set and a new item
gets put on the right click menu in the
cell now we also want to have some of
these commands reversed as the world
book closes so I'm going to ask for
another event procedure and it's
actually called the before closed event
because it happens just before World
closes and this is a fairly simple 1 i'm
going to have on error resume next again
I wanted to cancel the keyboard shortcut
command and the way we do that is take
just the first part of the command that
we use to create a keyboard shortcut
can't so I'm actually going to copy that
and paste it into there so that cancels
that keyboard shortcut instruction
next we want the line of code that
deletes the item off the menu
copy and paste and that's done so i'm
going to test this code in a moment so
we want to debug compile and save but
what I also want to do at this point is
a convenient . to do it is to password
protect my code that's quite an
important thing to do for various
reasons first of all it protect your
intellectual property if you've
created something yourself and you don't
want other people to steal your code
then you can password protect the code
and users can't see it
another reason to do that is to stop
people interfering with the code
somebody might think they can improve
your code and actually they break it or
worse
somebody might write some malicious code
so if you password-protect your code it
prevents all of those things happening
is very easy thing to do we go to the
Tools menu and go to vba project
properties on the protection tab we need
to write and confirm a password and then
lock the project for viewing even though
you might have entered the password here
it doesn't become effective until you
lock the project for viewing and none of
that works until the workbook has been
closed and saved and then reopened so
I'm going to ok that again i'm going to
save the workbook and then going to
excel and close the workbook i'm just
going to remove that and now I'll close
the workbook going to save the changes
yes I do and now I'm going to reopen the
workbook it'll be on my recent files
list there it is now depending upon
where not whether or not you from where
you saved your room
well but you might get the warning about
security here but i'm not getting it
because I'm putting it in a safe place
let's have a look and see first of all
if the keyboard shortcut works i'm going
to ctrl shift see and there's the
calendar and
i can write a date into a cell as fine
and about right clicking on a selection
and there's my insert date item when I
choose that the counter appears i can
choose a date what if i want to go and
check out the code here if I get to my
visual basic editor you'll see it looks
slightly different and if i try to open
the counters workbooks colored I'm asked
for a password so i can't get into the
code unless I give the password and now
it's allowing me into the code if I want
to modify my kinda at all so at this
point I'm actually finished building the
calendar and if you want to just keep
your counter in one specific workbook
they are you can stop at this point but
if you want to distribute this counter
to other users what would be a very
convenient thing to do and also a safe
thing to do would be to save it as an
add-in so they instead of distributing a
workbook to people they can install the
add-in on their computer and that will
always be available to them in Excel
it's a very easy process
all you have to do is save this workbook
as an added which is a specific type of
Excel workbook but before you do that is
one of two things that are not vital but
it's quite useful to do first of all
with this workbook open and in this
world but i'm going to the file tab now
if you're working in office 2007 you
just click the office button up in the
corner to get here and I'm going to the
file properties
and choosing advanced properties another
way to do this is actually to close the
file go find it in wherever you put it
on your computer right click and choose
properties will get to the same window
and then on the summary term give some
information about the file so i'm going
to call this the title is an important
one pop up kinda because that will
actually become the name of your ad in
as opposed to its finite the author up
my full name in here and also put my web
address again what is quite useful is in
the comments section down here put some
information which will describe what
you're adding does so for example I
might give an instruction that says
right click on a cell or use the
keyboard shortcut shift already quite
surely ctrl-c to open the kinda and the
chosen date
well the
Britain and to the selected cell
ourselves okay
right-click on the cell use the keyboard
shortcut shift ctrl c to open the
calendar the chosen day will be written
into the selected cells ok so I'm happy
with that the name of my atom is up here
and the description here a little bit
about me I'm not okay that take myself
back into XL and save in other words
i've saved that description and now to
actually make the ad in itself we do is
save as here again in $MONTH 2007 you
press the office button i'm going to
click the file tab choose save as and
the save as dialog comes up at the
moment it's kinda . xlsm and it's a
macro enabled workbook I want to have an
excel add-in and I've got two choices
here 97-2003 add-in aura and add in for
the current version which is 2007-2010
now if saves the 2003 I'd in this code
will actually work in Excel 2003 but you
must use the new kind of a demon and
Excel a.m. if you're working in
2007-2010 so I'm choosing that and it's
taken me to the add-ins folder which is
a particular place on your c drive and
you can actually save your adding any
way you like but I'm saving it in the
default folder and all i have to do now
is click save now it's savers so my
actual counter workbook here the xlsm
file remains unchanged which is fine
the one thing that you shouldn't really
do is open both at the same time so i'm
going to save that in case I need to
make any changes to my dad in so i'm
going to close my kind of work book now
now once you've created an add-in you
can distribute that to various use and
they need to go through a short-term
installation procedure and i'll show you
how that works i'm just going to ask for
a new entity workbook here which is not
important for the installation but just
so that you can say that if I control
shift see nothing happens because there
isn't a workbook here at the moment
which contains the kinda if I right
click on a cell there's no insert date
option there now in order to install the
kinder again I go to my file tab or you
choose the office button in 2007 and you
go to your Excel options
I'm of the window down here so you can
sit now on the Excel options there is an
add ins manager and here you can see
there is my popup kinda actually caused
it
pop-up calendar although it's fine name
is calendar XL am because that is the
title that I gave it its properties and
it's here under inactive application
add-ins which means it's not available
to me yet
now exhale has found it because it was
put in the default Atoms folder i'll
show you if you do put it somewhere else
maybe on a network location it's quite
easy to find it in order to activate it
you come down to the drop-down here make
sure that Excel add-ins is selected from
this list there are things on the list
and click go
and you see the add-ins manager here and
again there is the public calendar these
other atoms are ones that are installed
along with excel when you setup makes it
on your computer now in order to
activate the calendar i just need to put
a tick in the box that's the description
that i wrote if you remember in the
comments section and if i click OK
cooking ok actually opens the add-in you
don't get to see it but it's there in
excel so if i give my keyboard shortcut
ctrl shift see the counter appears and
similarly if I right-click on the cell
there is insert date and I can choose a
date which gets written into all cells
they are job done if any point you want
to deactivate the calendar in exhale
2007 you have to go again to the atoms
manager via the Excel options but if you
open the Developer tab in version 2010
we've got a button here for add-ins and
the open settings manager so to switch
it off you just take the ticket away i'm
going to leave open was useful
you'll notice that if you visit the
visual basic editor when you've got
Adams installed that you will see them
appear here now with third-party items
like these other ones from Microsoft if
they were activated you see them here
and most people who build add-ins
password protect them so you can't get
in but because you
the password if you want to modify your
reading you can do it from here or you
can work on the safe workbook file so
there we are that's the end of this
particular tutorial i hope you enjoyed
it and remember that you can go and read
about the whole thing in the tutorial on
my website at www.sedar.com you can go
step-by-step through the entire tutorial
its copiously illustrated with
screenshots ms even a sample ready-made
add-in file for you to download