Excel VBA - Invoice Generator - Microsoft Excel 2010 - Part 2

http://www.onlinepclearning.com
Excel VBA - Invoice Generator - Microsoft Excel 2010 - Part 2 This part 2 of the Microsoft Excel VBA project that creates an Invoice Generator that is free to use. The data is stored in 2 sheets (databases) and filter to your criteria. The purpose of this project is to help with your VBA and general Excel skills in basic application development. The template is for Excel 2010 however this application will run fine if created in Microsoft Excel 2003 /2007.
I will be adding the videos in stages. First look at the Overview video to see if this is something that you may be interested in. Please feel free to contact me if you have any suggestions or problem through the comments at the end of this blog or with the contact form in this website.VBA Excel -- Invoice Generator tutorial for Microsoft Excel 2010 / Excel 2007 / Excel 2003.
I will be showing you how the template can be adjusted to suit your needs.

---

Closed Caption:

greetings this is tripp from online pc
learning com
this is the second tutorial in creating
the EZ invoice generator now in this
tutorial I'm going to show you basically
what the template that you have
downloaded contains how you can alter it
to suit your needs and also i hope we'll
have time to be able to put in all the
dynamic named ranges for the application
so hang on natural will do
first of all let's have a look at the
template that you have downloaded first
of all just a little bit of housekeeping
with excel when you finish the
application and when you view the one
that I showed you in the overview
tutorial you noticed it was a little
different to this in the way it looked
now the reason for that is that I always
said my applications once they're done
to make sure that we remove the header
and remove the formula bar so you just
go to the View tab to do that click on
the View tab and here where it says head
I would remove and formula bar we room
we'd remove as well now we'll put them
back in for now but when you finished
your application when you're doing what
it's going to look like before you use
it always take those two antique those
two boxes
the other thing I always do is always
make sure that the actual ribbon itself
is hidden
that's the button up here right so we'll
leave them all that now but you just
need to be aware of that now looking at
this all of these are shapes the invoice
that takes you to invoice will go back
to interface all of these here are
shapes and each of the shape is assigned
a hyperlink now the hyperlinks are just
basically static named ranges so what's
a static named range here they are all
here is all the static named ranges now
forgetting hyperlinks if we just click
on the static named range it takes us to
that named ryan side summary here it is
i 1 is the named range and we go to Home
tab and we gotta formulas i should say
nine manager and then have a look at
where it says summary and just have a
look at the reference to a couple decide
you see it says that's a reference to
invoice summary I
one now static named range is a very
easy to create you just click in any
cell click into you nine range box type
type in the name you want and hit enter
and then it appears in here so we've
done with these static named ranges is
we've assigned them as a high poly so if
i go to invoice here for instance right
click and choose edit hyperlink you'll
notice that we have its hyperlink to a
place in this document and you notice
that the Ninth range is a9 range called
Mike invoice and if we checked that
named range which we can do a game from
the formula bar Mike invoice is a
reference to invoice sheet invoice
i won now why do we do that why did we
go to a . aesthetic diet named range
rather than reference the sheet
well the reason for that is that we want
these buttons to work regardless of what
people do with the sheet names of people
change the sheet names we still want the
program to work even though we're going
to hide the sheet names in the finished
application so hyper linking to a stat
to dine static named range is much safer
and safer than using a sheet nine now to
remove these tabs at the bottom here
which you were doing the finished
application or just do this while we're
doing some housekeeping or do that now
we're going to options so we went to
file options and then over here to the
Advanced tab is simply scroll down a
little bit how to find it wasn't 2003
and we want to make sure that we can
take this box here that says show sheet
tabs and when we do that and click OK a
sheet tabs an hour
moved where I will let's get started the
first thing i want to do with you is we
want to go and create ever static named
range so click on the invoice button go
to invoice now this range across here is
a static 19 so click on the cell diet
pull it across here we're going to
create a static and I'm drains click on
the name box at the top and I wanted to
type in accounts will be referencing
this lighter in kinds and once it
started in hit the enter key and now of
course no matter where we are in the
worksheet will just go to another book
and we're now if we click down and we
choose accounts you see it highlights
that night so that's how to create a
static named range now if you had
anything to do with my tutorials in the
past you know that I'm really mad keen
on dynamic named ranges i just think i
give so much power to your applications
and we're going to use them a lot of
dynamic named ranges in here there's one
other thing I need to show you before we
move on and that's I put a spin button
here
see here we this spin button i just want
to show you that in the code so that you
can understand what to do in ordering
that because i put in here I think we go
down to 49 I just click right to the
bottom and of available ranges so I've
taken down a 49er you may not want that
many or you want might want more
how are you going to adjust just spin
button suit hit the Alt key and then hit
ever live right when you get to ever
live and find the sheet that we're
working on the sheet that we are working
on here is the invoice sheets sheet2
double-click that sheet to open up the
BB i headed for that sheet and give you
some space here
now each of the sheets I've put in this
piece of kind of the start which is an
activate and it's basically in a scroll
to the top of the shape for you and it's
also going to sign the sheet to whatever
you want
so this doesn't fit on your screen and
you wanted to make the application fit
on your screen it change that from a
hundred zoom 290mm 216 whatever you want
it
ok that's in every sheet i put that in
there for you in the template now he's a
spin button now one is called a spin up
and what is called a spin down okay now
all it is is I followed which means it's
going to go 1 x equals 49 219 so this
one is going to spin up is going to go
to to row 49 and spin up to row 19 so
remember we said a minute ago that our
our invoices went down to Rio 49 now if
you want to do is to go to row ID change
that reference their 80 ok that's all
you need to do change their 280 if it's
started a drone far your changes 25 now
here we've got the spin down button over
here on the left
it's just the opposite so what you do if
it started at five you put five year for
went to idea put Heidi here and then you
change these little message boxes here
if range 49 equals active cell then
messagebox Bob up so you change that to
say that within go to ID and this one
would go five
ok so that's this bin button explained
just close or minimize the video editor
for a moment we'll close it down and
we'll scroll to the top of the sheet
this is something we want to do here
with this sheet before we go any further
and I'll set this up now and we're
probably this will be as far as will go
with this tutorial so first of all
united says a hidden group of columns in
here but you can't stay here and
highlight and scroll over so you
crossing over the hidden columns right
click and choose unhide now in here we
want to put some of the things that we
want to move over into a database
what do we want to put in here let's go
over to the purchases sheet because
we're taking this information and
dumping it over in the purchase sheet so
over in the purchase of shape you notice
we have date invoice customer before
quantity so here's the information on
your sheet right here this information
here and to the left of it we also want
to include the date invoice and customer
searches click on that and then
right-click so I lies three cells again
right-click and choose copy then we'll
go back to Erin your sheet and we go one
two three left right click and choose
paste and that puts in those three
categories you can displace them as text
if you want but we're going to need to
do now I just give us a bit more real
estate here we're going to need to do
now is to make sure that we pick up the
customer from here so we got equals into
their type in the equal sign and then
we'll click into customer
and then hit the f4 key to make it an
absolute reference so we got that and
then hit enter so now we're referencing
whatever the customer will be in here
will be referenced in here with that
sell highlighted grab the cell what I
want you to do is to copy it down
copy it right to the end of your range
right to the very bottom so we'll get
right down to their so every time we put
the customer in here going to show up in
here now with invoice which is an
invoice number will do the same equals
and in the invoice over here hit f4 to
make it an absolute reference it ended
to enter the formula click back on the
cell and now drag this to the bottom all
the way down to fill the hole invoice up
and again with date we will go equals
and then it's the data we need to make
sure that the f4 key again and then hit
enter we need to make sure that this is
formatted as a diet now whatever you
format one day as here on the invoice
sheet on your purchases sheet on your
own interface and invoice and purchases
and your summary sheet make sure that
use consistent date formatting write
your application it's very important if
you're transferring data across going to
make things a lot a lot neater so once
we have that setup again all the way
down to here
ok now once you have that highlight the
three of them right click and choose
hide you don't need to see them anymore
in this application what we can do a
course is moving shapes back over to
stop those shapes moving next time just
right click on the shape that format
shape and here where it says properties
click on the properties and choose don't
move or size with cells and click liars
and just do that with the create invoice
one as well say that that doesn't happen
to us again that's something that I love
diamond sighs myself
ok so now we hide and unhide that which
we will a little later on those shades .
move
alright what basically we're set up now
ready to start putting in a dynamic
named ranges and I think thing is we've
come this far
we'll leave that for another tutorial
what i would do is put into here you're
starting invoice number every time you
put in an invoice now we're going to
increment it by one so put in the
starting invoice number and I have
nobody wants to start with invoice
number one so we might miss time with a
hundred however they're all right this
is tripp from online pc learning com pop
along the website i grabbed the temp
light have to play around with it
this is basically being an overview of
what the template can do for you how
it's setup and you can modify it a
little bit
now we're ready to go with partying and
thanks once again
yeah
yeah