hello this is randy with excel for
freelancers and welcome to the mini accounting application in this week's incredible
training i'm going to show you how to create this amazing mini accounting application
complete with purchase orders estimates work orders and invoices along
with an incredible transactions and we have full inventory tying that up with an incredible
dashboard and an amazing comprehensive admin screen it's going to be an incredible training
you won't want to miss it so let's get started all right thank you so much for joining me i'm
really happy you can be here i've got probably hands down the best application i have ever
brought to you on a youtube video and i'm going to share all that with you today so what is this
this is an incredible mini accounting application and i've got complete with work orders we have
full work order management we also have purchase we're going to be able to purchase inventory tie
that into inventory we also have are going to have credible invoicing course work orders we have and
invoicing all tied up right we're going to be able to add new copy to save update we're also going
to have inventory complete adding new inventory showing quantity on hand we've got that for you
transactions be able to create any transactions loading transactions searching and filtering
for transactions based on multiple search events and of course we're going to have transactions
and a dashboard inside the dashboard i'm going to show you profit and loss outstanding invoices
and bill payments top 10 products to order monthly sales graph of course based on a date
range we're going to have a date range so we can show you how to create a really cool date range
changing that up and of course we've got a menu here and a comprehensive admin screen that's going
to help you set defaults and fully customize this application including your own names your own
ids and of course your own accounts right so much to share with you on this training you're
going to learn so much what are you going to learn in this training well of course you're going
to learn basic and intermediate accounting skills we're going to go over all of that you can
also learn how to handle database and record management how to create records how to update
them delete them we'll learn about that how to create invoices purchase orders and of course
their impact on the inventory so we'll be going over that all that inside the order management
here we're going to show you how to do that and also we're going to show you data mapping how
data mapping can reduce the number of lines of code substantially so we'll be going oh complete
over data mapping how to use that inside your code also robust conditional formatting we've got
a lot of conditional formatting we're going to show you how to use that to make beautiful and
useful applications of course how to duplicate these orders we can simply copy this to a purchase
order if we need to order those items i'm going to show you how to do that so we can quickly create
brand new purchase orders work orders estimates or invoices on a single click and also dynamic named
range we're going to be using named ranges for throughout this application you're going to become
an expert on named ranges and also of course creating a comprehensive dashboard and how to use
advanced filters combined with pivot charts to create beautiful charts and graphs so we're going
to do all that and a whole lot more i hope you'll stick with us it's going to be a long training
so make sure you grab your beverage of choice take time to out and this is going to be
incredible training even if you can't complete watching this training it'll be always available
on youtube or your platform of choice i'll make sure it's always available for you you can return
and watch it as many times as you want if i do move quickly on this you can also slow down the
video if you need to i did want to let you know of an incredible launch that we're having i've
got 250 of my best templates available for you in a single zip file now available that's 2x
nearly every single application and just today now i decided to offer a brand new free bonus now
this 250 was already built this pack all of my last templates have already been in there but
what i'm going to do is i'm going to include this mini accounting application free as a
free bonus to those people who purchased the with pdf downloads i'm going to make sure that you
get this accounting application absolutely free template in there of course that's going to also
be included in our template library that way the template library is amazing because it's going to
allow you a single click to open the application and another click to view the youtube video
training so you'll always have those resources at your availability all right great we're going
to get started right away on this training because i've got so much to share with you on this
i just wanted to make sure a few things that you already have subscribed to the channel and
don't forget to click that notification icon bell that'll ensure that you get these trainings each
and every week i make them available for you please don't forget to share this video like it
comment below i respond to each and every comment so don't worry about that i want to hear from you
this video training is for you as it is each and every week all right so let's go over a quick
overview now that we know exactly and then what we do is we're going to get into the details so we
have this really cool home screen this is going to let us quickly and easily go to each and every
aspect we have an admin screen that we're going to be going over in detail on this we also have
a dashboard as you saw here and then what we have is the inventory you're going to be able to add
edit update inventory add pictures and do all that kind of stuff on the inventory now our order
screen we can create multiple different orders as you saw we can create estimates work orders we
can create invoices we can create purchase orders and all of that each one of them look different
each one of them are fully customizable we'll be getting into all of that and of course we also
have transactions right we're going to be able to create multiple transactions we're going to be
able to search for those transactions either by a specific account or we can search by a particular
amount if we want to or we can search by a name right we have that available as well so there's
a lot to choose from here and there's a lot to show you on this really incredible accounting
application has everything inside that you need to cover your basic accounting and i'm going to
show you every step so that way not only will you have a template but you'll able to customize this
and create it on your own and be very successful at it so we've got these transactions inventory
and orders all right so that's the basically it'll tie it up with an admin screen and then of course
the dashboard which you have seen along with the date range all right so what we're going to do is
let's start out with the admin screen we're going to go over what's inside the admin screen and why
that's important to you now any good application right every business everybody runs their business
slightly different they have different names they call work orders differently for example
estimates maybe we want to call estimates maybe we want to call them proposals right so what
we want to have is create a dynamic application in which they can change their own words and then
have those that vocabulary used throughout the application so if i call instead of an estimate
i call a proposal because that's what we're used to in the company that i'm working for then it is
that new vocabulary that gets used throughout the application so now we have proposal up here we
have proposal status we have add new proposal we have copy to proposal so it's automatically added
throughout the application so the application is fully dynamic whatever you change inside
the application right here in the admin and settings screen that's going to be changed
throughout the application so we have a lot of dynamic vocabulary here including invoices and
purchase orders so whatever you call them and here's just some ideas of what you might want to
call it work orders could be called work requests invoices receipts bills or orders purchase orders
or purchase or pos whatever you want to call them and the same thing with our customers if we want
to call them clients or customers or vendors or guests or whatever we want to call them we can
simply change it here and they're going to be changed throughout the application automatically
and it's also going to update the database as well and we're going to show you how to do that
so that when we make changes it gets updated throughout the application very very helpful
naming especially when we use other languages too also moving down we have uh accounting here
when we create transactions we want to create both income and expense types transactions
in expense type can be cost of goods sold cost of goods solds are those type of expenses
and which are directly related to the work that you perform so if we are creating if we're sending
technicians out and they're doing some repairs and they bring some products with you the hours that
they perform along with the products that they use are all cost of goods sold those expenses are
considered cost of goods sold while other expenses such as lease utilities or meals entertainment
something that cannot be related directly to the work or products being created those are called
other expenses and that's going to really help us when we create that profit and loss inside
the dashboard we want to show income cost of goods sold of course we've got a net loss in this
one gross profit and other expenses we want them showed sep those expenses cost of goods sold and
other expenses shown separately so having those two different types of expense accounts are really
going to help us detail down into the types of expense a company can have and of course we can
create as many expenses as we want okay also now we take a look up here we have a status now each
individual orders can have status in other words this is fully customizable an estimate can be
pending completed sent while a work order can have its own statuses invoices can have their own
statuses and purchase orders can have their own status and let me take a look and show you what
we mean by that i've got to add a home button onto this i think that's kind of important so
let's go ahead and do that now so let's take a look at this i've got a home button that's going
to allow us to do that admin screen needs one too i thought we put it there okay so we got one
for the admin let's put a home button and you see these going to automatically go to i'm going
to show you how to create that okay so insider order management if i have a status here if we say
proposal status so let's go ahead and add a new estimate or work order or proposal or anything
like that what we want to do then here there we go an estimate the way we had it before estimates
can have certain statuses pending completed sent pending approval approval or denied right
those are fully customizable statuses while work orders here can also have their own
statuses assigned on the sides right so you can see how the status the drop down list the dynamic
are completely customizable pending unpaid and of course lastly the purchase orders can have their
own status right so here ordered pending shipped receivers right so you see the same cell can
have dynamic dropdowns i'll be showing you how to create that and that's all going to be based
on what we set up in here and also what we want to do is we want to have a setup right we want to
have a default so if we want to know an estimate if we want to set brand new estimates we can have
the sent as a default and maybe we want our work orders to automatically be assigned that is the
default status when we create new estimates or new work orders so when we go back into the order
management and i decided i want to create a brand new estimate we see that the estimate status has
automatically been sent the set is the default and we could a brand new work order we also see
that we have a work order status as a side those are completely customizable from our admin screen
right here right so we have our default settings and that means for brand new orders so invoice
estimates work orders so everything is fully customizable and setting dynamic i'm going to show
you how to do that creating this dynamic not only do we have a dynamic data validation based on the
type of order that was created but we also have the dynamic default so we can set the defaults
with a single check so we've got a lot going on there i also want to count defaults right when we
create an invoice what income account notice we have a list of income accounts what income account
is going to get affected i want to know the income account so that we can automatically mark those
invoices when we create an invoice likewise when i create a purchase order and i purchase materials
i want to know what is the default expense account and why is that important when we go into let's
say we go into invoices i want to set a default invoice account that default income account now we
can change it on individual invoices but i want to know the default income account associated with
this invoice so when i create those invoices very easily it is that particular income account that
is going to get created credited likewise when we create a brand new purchase order i want
to set a specific uh actually this purchase account this should be invoice account income
this should be expense account i'll update that shouldn't be invoice account it can be expense
account but we want to make sure that this is dynamic so i'm going to make that update in other
words i want to show expense accounts if it is on purchase order or if it is so let's go ahead
and show you how we're going to do that so we're going to do equals if right and i want
to know if our order type i'll show you what the order type is the order type is very specific
to whatever order type or we offer the name range equals and i want to know if it's going to be the
invoice type invoice order so this is going to be if it's an invoice order i want to show this as
an income account otherwise if it's a purchase the only two particular orders that we're going to use
this for purchase orders and invoice and then it's going to be expense account expense account we're
going to show you what those types are and the name ranges will go over it and that's it that's
all we need to do so that way automatically this is showing expense account for purchase orders
and then anytime we load a brand new invoice it's automatically going to show income account okay
great now just keep in mind we'll be showing over that order type is right here called invoice as we
add a new one right it's going to change so keep in mind that this purchase order here if we take a
look at this this is order type so this particular cell is the named range that we're using okay now
back inside the admin let's continue going over we we looked over here but we've got some named
ranges here this is called the estimate order we're going to be using named ranges throughout
the application we'll be going over them every little bit but this is called the estimate order
this is called the work order this is called the invoice order that we just used this one inside
our formula invoice order and lastly we have purchase order right so those are the named ranges
that we're going to be using also i have customer name i have the vendor name i have the employee
name yes we'll be adding employees items and customers and vendors kind of didn't even show you
that my goodness right when we add a brand new one we can add a customer vendor or employee so we can
do that whoops forgot to show you that well now you know see there's a little secrets keep in mind
that purchases are going to have a list of vendors while invoices are going to have a list of
customers right so this is going to change the customer we have a dynamic list of customers
right we have a dynamic list of employees however all of them are either any one of them can
be added here if i select a specific customer here we want to may want to edit that customer we can
do that through here we're going to be getting into detail on that let's continue with our uh
just because i forgot to show you that so we've got customers vendors employees and items we each
one of them have individual names this will help refer to them inside our of course formulas and
help us also inside the code so we went over a pending status now account defaults we understand
the transfer account i'll probably set this up this is going to be a default so when we create
a brand new transfer i just didn't get that but i probably should if i create a brand new transfer
here's a transaction it should be transactions and i create a new transaction do we want to set a
default account what account do we want to set default is if i were to copy this here and then
go into the admin screen and i paste it should say transaction accounts and i paste that entirely
there and i call this transaction account let's use to do this abbreviated right i want to set a
default one animal transaction account so i want to set maybe we're gonna maybe we want to leave
it blank but maybe we have a continuing maybe we purchased a lot of meals and entertainment
so we want this is our default account we have other spaces here we can add to that so maybe we
want to do that too the transaction account so that way when we create new ones and i'll program
this in with you of course when we create brand new transactions it is that account that i also
want to set here in the add new in jail so we're going to have transactions so that's very cool
so we've got some default accounts set up this is used for invoices this is used for purchases
and keep in mind that if i invoice income purchase expense and transfer okay we also have
tax details i don't want to know the sales tax both when we create invoices we're going to create
a sales tax but maybe there's a chance that when our customers when we purchase on a purchase
order our tax rate could be different so let's say we have a sales tax of 10 percent
i'm not sure why they would be different but at a purchase tax rate of five percent we
may want to call them something differently therefore when we go back to the order screen
if i go to a let's go to purchase order here let's say three is a three is a purchase order
right so if i've got a purchase order here i know our purchase tax is five percent however
if i create an invoice here it is an invoice so in invoice i want a sales tax at ten percent so
notice that the tax can also be dynamic based on whether it's an invoice or based on whether
it is a purchase order so those are the two transactional types of orders estimates and work
orders do not have any type of transactional therefore when you see an estimate right there
is no account when you see a work order there is no account assigned because only invoices and
purchase orders have account related so that only they can have payments on them and only they
can have expense accounts or income accounts okay so moving on so we've got the accounts now
we also want to set some terms right if i want to if i know that we create invoices or pay bills
based on net 30 or net 15 when i create a new invoice or a new purchase order i want that
default set up so that we can automatically set the payment due date right so that means right
if i create a brand new order here and i want a new new invoice and i want to set that default at
net 30 i know that our as soon as i decided let's say i decide to change this to six five i want to
make sure that our due date is automatically based on net 30 if i decide to change these terms to net
60 that due date's automatically going to update right and it's going to be the same for purchase
orders if i create a brand new purchase order i also want that automatically due date probably
set it automatically when i create a new one based on assuming that there is a purchase
and assuming that there is a payment or date the due date should be set automatically but of
course it can change right and that means you have 15 days to pay that bill and i want that due
date automatically set based on the purchase date so that's when of course we can set a default
right that default is going to be for when i create a brand new invoice what payment term do
i set as default here if i were to go back into the admin screen and change that to net 15 any new
purchases or any new invoices would automatically get that net 15 here so we're going to set that
as a default that makes things a lot easier for us a date range this is very helpful this will be
used in our dashboard when we create a date range whatever date ranges we have this is what we
want to be reflected inside our dashboard so we go inside our dashboard and i change this to
this month right i want our data to automatically be reflected and i want our from and two days to
automatically show that if i change it back to this year again i want that from and to date
automatically updated based on the date range now that date range is basically set up directly
inside here so we can set up those dates here so we're going to be setting up and we just basically
set them up our basic formulas from the year of today if we want to set up for example from the
beginning of this month we would just use the date formula from today the year of the current
year of course the current month and of course one setting that first and then we use the same
formula except we wrap it around eo month and that's going to be the end of the month meaning
the current month so we've got some formulas here you can take a look at them we won't be going over
to too much detail on the formula they're pretty uh standard okay this is really really important
we've got an accounting basis now some businesses consider income created when they create an
invoice or when they create a bill however other companies use accrual based account excuse me use
cash basis and they assume that income is only when they get paid or when they actually pay when
the cash leaves their pocket right or when the cash comes into their pocket that is a cash base
and we should be able to do both of these right so we just simply select this option and that's going
to affect our profit and loss i'm going to show you how that does it so we go into the dashboard
right we're going to see that that's going to be affecting so if we refresh this you see our income
automatically changed right look at that the income completely changed based on the accounting
basis now this is really important cash basis counting means that it is income only when you
get the money is expense only when you actually pay the money whereas a cruel based accounting it
is income as soon as you create the invoice or as soon as you create the bill now personally i kind
of like cash because we know that when we create bills when we create invoices customers don't
always pay 100 and they always don't pay on time so i personally like cash basis but of course
it's whatever your company is used to whatever you're used to and or whatever your your rules
are sometimes government has specific rules on on which you can use but there but both are
available inside this application which is kind of really helpful also we have item picture folder
as you saw here inside the dashboard our items our inventory items have those pictures right those
are located inside our items database here we've got a database of items basic information item
id inventory type sales purchase description work or service order description that means
uh when we make a sale or an estimate there's a specific description for items when we make
a purchase there's a different description or when we create a worker you want to send our
staff on a specific work order we have another description we have a minimum quantity for a
product a sales price and a purchase cost and a picture associated another picture associated are
based on a folder that folder locations right here so i've mapped that specific folder location right
to here when you get this file of course you can map your own and if you want all of my pictures
and icons and every all the other resources our patreon platform will have all
of those resources make sure you join our patreon i've got that link down below
we've got brand new updated videos updated templates each and every week along with a ton of
other benefits so i hope you'll do join us so we can browse for a picture folder whatever picture
folder we want and we could just place that in there okay also again we also have dynamic footer
messages now this is really cool now what is a footer message a footer message is a message that
shows down here thank you for your business right we can set different ones if we want to we can
set a brand we have a list a footer message maybe 30 days right so estimate is good
for 30 days so what we can do is create a multiple footer message as we change our footer
message here it's going to change down below however we want to set up some default footer
messages so that means when i create a brand new estimate i want to set that footer message
as 30 day message and it means this estimate is good for 30 days on the estimated date or perhaps
when i create a brand new work order i want to set a footer message of cleanup letting our employees
know please make sure to clean up the entire work area before leaving and of course likewise when
i create an invoice i want to thank the customer and when i create a purchase order i want to know
about letting the driver know deliver all packages back to her so we can create any number of footer
messages and we want to have those footer messages and set those defaults here so that includes a
message name and along with the message text but what i want to do is i want to set the default
on a per order basis that means our estimates are going to show this if i want to change our
estimate so that our estimates could show delivery times i don't know why we'd want to do that we
can set the default here so then if i create a brand new estimate and it's going to show that
default so let's go ahead and here estimate here and create a brand new estimate here it's going
to show delivery times and then delivery time so the default is automatically set on this as i said
this is a massive application so we'll go ahead and reset that default so i want to be able to set
that default i want to set the default work order footer message the invoice and the purchase
order all should have default estimates that's really good i believe we've covered everything
in the admin screen it's the most detailed i've got a few named ranges that i just want
to bring to your attention that are coming from the admin screen so we're going to go into
the formulas and we're going to go in the name manager and we're going to see accounts expenses
right so we've got a dynamic named range based on the account expenses it's going to be based on
all of those expense accounts we also have one let's bring this over here we also have one called
income right based on our income accounts and i also have accounts accounts expense type right
i have another name range for expense types so this way when i look up a specific expense i can
easily use index and find out what type of it is is it a cost of goods sold or is it an expense so
that's going to be helpful in a formula so we have expense types and i also have all accounts sorted
now this is going to be a sorted account that we can have all the accounts sorted and that's going
to be located on our transactions let's go ahead and go to that so i can show you that and that's
going to be coming directly from the transactions here so if i move over here let's unhide
columns a and b so you can see a list of those our transactions uses all accounts right we can
have both income and expense accounts inside this account so what i want is a unique list of all
the accounts so here's a unique list of all the accounts called unique accounts so we can create
those automatically and i'll show you how to do that so that's going to be all accounts all right
great so we've gone over in some of the named ranges inside here we also have some dynamic named
ranges for our order status that's going to really help us out inside our vba code so we can quickly
get to the right automated status for each based on a form along with the default data so insider
formula and name manager and we see status here we see we have one called status estimate here
and we have a dynamic range called invoice here so we move over here and we have one called
status let's take a look at the work order here right which is over here and we have one more
of course called status purchase orders and we also have one for the default so you see status
purchase order right here and i also have one for the default i want to create one for the each one
of the defaults so that's going to also help us so if we take a look inside here we see moving
over here we have one for the default i've got a named range for the default it's called status
default estimate and another one for the invoice and another one for purchase order and merge
so each one of these has its own named range so we have one for the status and one for the
default status okay so that's going to easily allow us to locate which one is set to fault when
we create those brand new estimates work orders invoices or purchase orders keep that in mind that
also i've estimated this is c5 right of course this is also i've also created a named range so we
can do estimate order that works as well because we've created a named range for those same thing
with work order we i like to use these in formulas so instead of using a specific cell we see it and
that way when we make a change to work right if i was going to change this to work request you would
see that that would automatically change here up and there so that's kind of nice i like that
to have that kind of dynamic ability there inside there so using those forms and the same thing
for invoice here i've done invoice orders we can just quickly find it invoice order and lastly
purchase rows so we can see how these name right support store oops i didn't do that that should be
purchase order okay so we can see how using these uh named ranges here fully customizable vocabulary
throughout the application and then we can just use that so we see inside the formulas using the
name range we know exactly what it is great so that's kind of a rundown of our admin screen we
can kind of see just about everything else and we've got this i've also got a named range called
item picture folder here for that so in case we have a named range for footer messages let's just
quickly go over that because that's going to come up so we'll take a look inside footer message i
have one for the name and i have another one for the text so we've got two footer messages and i'll
remind you these as we go because there's a lot of named ranges but one for the footer message name
and one for the text so we've got two of those okay very very good let's go ahead and take
a look inside some of the database so i can just familiarize yourself with all the data the
first thing we have is our orders database right this tracks all of our main order we have order
id the type of order it is as you know we have four different types of work orders the order
date assigned what is the status of that remember we have dynamic statuses the name associated
generally it's going to be a customer or vendor name right vendors for purchases customers for
estimates and invoices and probably work orders and then we have an employee list now the
employee of course comes directly from here right here so we can assign an employee to any
type we also are going to have the terms right the due date of course work orders wouldn't have
a term estimates generally wouldn't have a term but invoices and purchase orders would have terms
in the along with the due date our footer message named the name of the footer message so we
recreate it if there's been a payment invoices and purchase orders would have payments whereas
workloads and estimates would not again invoices and purchasers would have accounts while estimates
and workloads would not have an account associated with that what is the total amount of that we
can have that total amount work orders generally wouldn't have total amounts but estimates invoices
and purchase orders would have and then we have a balance here what is the balance based on the
number of payments along with the total right so we have that and that's going to come from a
formula here i'd like to create these dynamically so that we create a formula and i'm going to
create simply basically the total amount minus any payments that's our formula which is l then when i
run some reports i'll just bring down that formula okay now we have order items database if we take
a look inside an order any one order we have items associated with an order right so i want so for
example i've got three items associated with this inside this purchase order we've got
items description quantity price and total i've got to store all that information
somewhere and so where are we going to be storing that well that's going to come in the
order items database and the order items database is going to store we certainly need an order id
notice order number one has three of these items it's a work order we got the order date the
assigned if there's an account that can help us work orders wouldn't have accounts the item name
the description of it along with the quantity the amount this wouldn't necessarily come for a
work orders but it could be helpful in the future and in a row that's associated this is the order
right it's on row number 10 row number 11 or row number 12.
So i want to know row so that way when
i bring it back in we know exactly what row was on before and we also want to know the existing
row the database row this is very important the database row 4 5 and 6. so we're going to use a
formula for that formulas are important because if i delete one row the formulas will automatically
update so we want to use formulas there also the item database right we saw that we have item
information well that's all got to be stored somewhere so we have that inside the item
database and it's going to start with the item id the item name the inventory right we can
have both inventory and non-inventory parts hourly service work would be a non-inventory part
meaning we don't want a stock inventory it's just a service item so we don't stock inventory for
those types but other types of inventory we do our sales or estimate description along
with a purchase description and the work or service description we saw that before we
have a minimum quantity the sales price the purchase cost and the picture we went over that
already the transaction database is very simple right that those are all going to come from these
transactions right and that's going to come right here we have a transaction id the date of the
transaction the account type what name was on it a vendor name customer name or employee name the
account that's associated the amount and a memo and the names database now this is really cool
because we can store customers vendors employees all within a single database as long as we know
the type of it so we have the name id the name the address city states it phone email and a status
right that's all going to be very helpful and we're going over that okay great of course names
as mentioned before we can quickly edit or update names using these right if we want to add a new
one right we can just clear this out delete this and we can add a brand new name here if we want to
add a new name we can create a customer a vendor or employee and we can create a status by adding
all of that we'll be getting into that okay so that's it the database really is just made up of
five different databases again orders order items items database transactions and named database so
it's very very simple on these four i've kept this very clean and while it is robust it is very very
simple to keep it nice and clean so we've covered the admin we know it's we've covered the database
now let's go ahead and take a look at some of the simplest screens let's go ahead and go inside
the item screen the item screen is basically this we've created a brand new items screen and what i
want to do is be able to simply select an item and then have that item load i may also want to load
it up automatically or i may just want to start typing in that name and have that name come up so
if i want to type in don right i want that to come up automatically now how do we do that that's
called autocomplete now autocomplete's a great feature and it's very very helpful so if i type
in let's say jacob's coffee here jac it's going to automatically come up however for something that
has like coffee beans let's take a look at this coffee beans la vaza or coffee beans crema it's
going to take a little bit more because those names are very similar so i can type in cof coffee
beans and nothing's going to come up as soon as i type in something that's unique it's going to come
up how do we do this well to use autocomplete what we want to do is we want to have the list of items
in the same column now if you take a look at this you don't see it here right it's here though but
what we're doing is we're hiding it it's actually right here let's take a look at this you see
the formula right here that is actually hidden so i do have a list linked to items let's take
a look at this items db and three what is that well inside our items database what i do is i have
a list of sorted items and inside here is called sorted item names when i create a brand new item
we're going to automatically update this list and make sure that they're sorted now to get this
list what i want to do is i want to take this list and i want to create links so that means
any updates to this list is automatically going to link so what i've done here if i go down all
the way let's say to about right here holding shift control and then i set a format i'm going
to go into the let's say home here and notice that there's a custom format if i change this
to a general format you're going to see them all here so we see we have the names here that's
great but it doesn't necessarily work let's uh i'm going to clear this something out i'm going
to delete what's here because it's getting called so now if i type in and nothing's going to work
let's type in d-o-n notice it didn't work right why didn't it work now because i deleted something
very very important okay what we have here is called i went over to specific training called
don't break the chain right this actually has to be a link a chain link between these cells that
link with data now that data can be any type of data even spaces so if i added let's just i had a
lot an x here and if i want to add an x or you can use a new space but i'll use x so you can actually
see it right and i'm going to paste those values in here and then i'm going to paste the value
here we've now created a chain take a look at this linking of full text these cells now it doesn't
have to be the same so now when i type in d o and notice it comes up automatically right so again
e s let's do something espresso coffee so notice but if there's a break in the chain right if
there's a break in this chain if i delete that and that chain gets broken it will not come up right
so notice it didn't come up as soon as we fill that chain in with values any value at all it will
come up so that's called don't break the chain now those of course we don't want to see all these
x's we can also use spaces so let's say i'll use two spaces right and then what i'm gonna do is i'm
gonna copy this i'm going to paste this down here paste in those values here and i'm going to paste
the values here so now we've replaced it with spaces and now let's check to see if it works it
still works great right so that's perfect that's exactly what i want and also you can do this
you can do let's say es or let's just do e okay and then i'm going to hold the alt down arrow and
that's going to help us also quickly get to where we want to go using what we want so using alt down
arrow also helps too if you want to quickly find out where you are so that's really cool so we
have that available to us so i really like that and it's called don't break the change now we see
but we don't really want this ugly uh text here we really want it hidden right we don't want to
see those so i'm gonna hold the shift and go all the way down to the bottom and i've got about
a thousand that we're using right so a thousand and what i'm gonna do is i'm gonna format this i'm
gonna go to the home i'm gonna give it a custom format i'm gonna go into more number formats
and then i'm gonna go to custom here and i'm gonna use three semicolons three one two three
three semicolons that will automatically hide it everything so now we have auto complete that's
working just great however we don't see the spaces we don't see that chain and we don't see the
links but it's all there so that's how we use autocomplete very very powerful especially when
we need to load we have a large list and we want to be able to automatically set those and create
quickly created without having to scroll down a large list and keep in mind if you have a row of
thousands right it's going to take a little bit longer to load that list so keep that in
mind that the lower down on the list you go a little bit longer it's going to take keep that
in mind so if you click on a cell it doesn't come up automatically list with thousands take a little
bit time depending upon the speed of your computer okay great so we learned how to do auto complete
now we're going to now you see as i change it's going to load these up how do we quickly load this
information up of course that's going to be in vba we'll be able to add a new item add a new
picture if we want to browse for a picture clearing the picture we can do a lot of that so
how are we going to do that well i don't need to say this let's go ahead and okay so it's going
to come up saving an item deleting an item that's going to of course all be inside so we've got
a few macros to go to inside vba if you wanted to learn how to get into vba we can go in to of
course developer if you don't have this developer open we can display the actually go into the
options here and then just go into the customize ribbon and make sure that you've selected a
developer if you are new to vba i have a great course available to you actually not one of my one
of my friends daniel strong he's got the ultimate vba course an incredible course from beginning
to end i don't teach basic vba too much but if you do want it that is a great course and
that'll get you up to speed so i'll include the link down below excel for freelancers followers
get at least 20 discounts so make sure you jump on that great so we have this let's go into the item
macros now what i've got is several modules here we're going to focus on the items that's the
first one i want to do i want to dimension some variables item row the item column last row
last results row the result row those are all log variables we're going to be viewing pictures so we
need a picture folder and we need a picture path and we need the item picture as a file that way
because we're going to have to browse for that item picture okay the first macro that we're going
to go over is the item new that is the macro that runs every time we click this button all we're
going to do is clear out the associated cells and of course we have a few hidden columns here
and that's going to help us for the admin so i'm going to unhide these it's going to display column
a and b now the item id right here this is the selected item so if i type in an item here i want
to show the item id associated with that i also want to know what row it's on i've got a named
range called item id if we go under the formulas and the name manager we take a look at item id we
have a dynamic named range based on all those item ids so you can see the last one's 30.
Okay so
that's item i also have another one called item name here and i have another one called item
sorted right remember we had that sorted list i've got a named range based on that sorted
list so that we want to use because it is that sorted list that i want users to select from so i
want both lists i want item name which is unsorted which is part of our original data here and i want
this sorted list which is vba is going to take care of and sort it for us automatically and show
it up here so we got two named ranges for that okay great so inside that what i want to do is
i want to associate the item row i want to know that item number 21 is on row 24. now keep in
mind that inside our items database our first one starts on row four right our first one starts
on row four so i want to know that item number 21 is located on row 24.
So how are we going to
do that well what we're going to do is we're going to use the match formula and we're going
to match whatever's in b3 that's the item id and i want to know an exact match and i want to
add 3 because our first one starts on row four if there's an error we're just going to show empty
an error would be perhaps if we click a new item then we want to make sure show empty so we know
that there's no item associated i also want to know the next item id if you've seen my videos
before we use it almost in every single video our next item id is going to be based on all
the item ids we're going to use the max formula it's going to give us the largest number and then
we're going to add 1 so in that case would be 31 because we have 30 all together i want to know the
selected item id when i select an item here i want to know what id is here notice it flashed very
very quickly here take a look at then again here located in b6 we see that 21 flash real quick as
soon as i know that we have a id associated with this item i'm going to place that id directly in
b3 and i also want to know the picture right when i load up an item i want to know the name of the
picture that's associated and this one is called here let's say we expand this column it's
called don frisco's coffee png that is the same name that's going to come so when i lo add a
new picture that name is going to be changed right here great so also what i want to know is i want
to know understand the minimum quantity the sales price and the purchase price so we have all that
so let's get so inside our new all we're going to do is just simply clear out all the associated
fields i also want to make sure to clear out the selected id located in b3 so that's what we're
going to do here b3 and then we're just going to clear out the remaining of the cells clearing
those out and also if there's a picture associated this picture is always going to have the name of
item pick item pick here i want to make sure that we automatically clear that out i'm not showing
pick folder it's just item pick okay i want to make sure that item pick is automatically deleted
there okay so that's very important and if there's no picture it would create an error so therefore
we've wrapped it in on air resume next and on error go to zero then i'm going to select e5 we're
selecting e5 because that is the first one we want the user to enter that brand new item so they can
enter a brand new item here so test item right we'll give it an item type we can give it a brand
new item type like inventory and non-inventory we can give it a test invoice description and we can
also give it a purchase description test purchase description and of course work order service
description service description here we can give it a minimum quantity when we may place an order
right i want to maybe have that minimum quantity i don't want to have any stock under 10 if our stock
goes under 10 i want to know about it we can set a sales price here and we can set a purchase
price here so we can do that automatically and automatically based on the purchases i'll go
over this formula a little bit later on i want to know the quantity on hand and basically in basic
format is all the items that we've purchased minus all the items that we have sold and that is going
to be the quantity on hand i also want to be able to add a picture if i'm going to add a picture we
can use this invoice with profit which is a great training so we can quickly add a picture here the
picture name i want displayed directly inside here and then we can simply save and update that and
that's going to save that we'll have a fade out message that lets us know that the item was saved
and now it is saved so that is you saw the macro just one called item save and update when i click
that button it saved the item so how are we going to do that well the first thing what we want to
do is i want to make sure that the user has put in an item name and type i do have some required
fields item name and item type are very important and required if the user leaves those blank we'll
let the user know to make sure to add those before saving and we're going to exit the sub out i also
need to determine is it a new item or there's an existing item right if we go into this item
we load this we know it's an existing item if we click a new item then we know how do we know
that b4 is going to be blank for new items where b4 is going to have a row associated if it's
for an existing item so b4 is going to be our differentiator and we can use that if b4 equals
empty we know it is a brand new item there are a few things that we need to do for new items and
there's one thing we need to do for existing items so for new items i want to determine the item row
that is the item row that's going to be the first available one inside our item database so the next
row here is going to be 35 right and we also know that we need to get a new item id for that which
would be 32 so what we're going to be doing is we're going to be pulling that item id directly
from here b5 i'm going to place this directly inside b3 and i'm going to set that row up that's
what we're going to be doing for those new items so the item row is going to be the first available
row inside the item database and we have a new database plus one that's going to give us our new
database row and our item id we're going to take whatever's in b5 this is that located inside here
that's going to set our next item id i'm going to place that in b3 right here i also want to take
that item id and i want to place it directly inside column a so i'll place it right here just
like you saw we put in this 31 i want to make sure that we're going to place it also in column a so
they get placed there so 32 would get placed here so that's it for our new for an existing item
all we need to do is extract the item row from b4 everything else is going to be the same and now
we're going to use data mapping now if you haven't seen data mapping all we're going to be doing
is simply we want to map this column item name or item type or sales description with a specific
cell inside our item screen here so if we notice this item name is located in e5 and we notice our
item type is located in h5 i want to map those cells directly with the associated columns so we
see that item name is e5 and we see the item type is h5 so these are maps all i need to do then is
loop from two because a is already in there from 2 all the way to the last column and that last
column is column 10 right i believe column 10 here so we can loop it up all the way column 10 to
which is this case we're going to loop it all the way to column j right b7 of course is the mapped
one that's our picture our picture is located right here inside b7 here so we're going to map
it all and we can do that with a for next loop for the item column equals two column one has already
been done here right already been done here two to ten and what we're gonna do is we're gonna
update that so i'm gonna take that item database that specific item row or that item column the
value there is simply gonna be equal what is ever in the range where is that raised located it's
located in that first row of the items database we're going to save and update that so we're going
to just loop through that taking basically any data that's here here here or here and placing it
in the associated row inside our table here here here so just bring creating that and our updating
that automatically inside that okay once we have that that's it all we're going to do is i want to
run a macro that's automatically going to resort those names or add that name that macro we're
going to cover next and i want to run a fade out message this simple fade out message is just going
to show this fade out message so it automatically goes letting the user know that the item has
been saved and fading it out so for as far as the sorting names list right if i want to sort that
that's very very important right so if i change this from don francisco's coffee to let's say
fred francisco's because i love fred's a great guy fred francisco's coffee maybe fred bottom out
right and i want to save and update that name i want to make sure that fred is automatically
if i type in f right i want to make sure that fred is automatically in here how do we know that
how do we know it's going to be automatically in that updated list well we can simply take a look
inside our drop down list here and scroll down here and we see that we take a look inside fred
francisco's coffee notice that it's automatically been automatically updated right so if i type
that in here it's automatically if i type it in here fred francisco's coffee it's going to show up
and no longer don is no longer available right so it automatically updated in that and if we
take a look inside our items database here we see that fred francisco's coffee has now
been updated and it's alphabetically sorted that is going to be for the next macro we're going
to cover it's called updated sorted names and it runs every time we save or delete a particular
item so what we're going to do is we're going to determine the last row focusing on the items
database we're going to determine the last row and i want to clear any existing so whatever
existing all the way from n3 and down i want to delete everything there so we're going
to clear the contents out using this line of code and then what we want to do is we want to bring
over all of the names so i'm just simply going to take all the names located right here between from
b4 all the way down to the last one so b34 and i'm just simply going to copy them over right here
inside column n once they've been placed that we can do that right here so n and the last row minus
1 equals b in the last row why are we minusing 1 why is there a differentiator on that because our
first one starts on three while our first one here starts on four so we need to differentiate
between that okay great so then what we're going to do is we're going to run a sort so first
thing we're going to do is clear any store fields and then what we're going to do is we're going
to set a specific key now that key is going to be based on the first value which is m3 i want a
sending alphabetical a to z sort normal and then we're just going to set that range make sure you
reset the items database here and here resetting that sheet name because we're inside another with
sort we're no longer inside here so we're going to set that range to end in the last row and
then we're just going to apply that filter and that automatically applies the filter and sorts it
alphabetically the named range automatically takes care of it remember we have here inside our items
we have a sorted named range it is that same names that we're using inside the data validation all
right so we see when we click data data validation here inside the advanced here we're going to see
that we have item name sorted so it is this item named range that we're using inside that drop down
list so that's the one we want to select okay very good so now we've covered that and i want to want
to show this fade out message this is relatively simple we use this throughout the application
i'm going to take a particular shape that shape is called item saved messages this shape if we
select on all the shapes and we see all of our shape we see that item saved message is hidden
if i were to make it visible we see that we have a shape called item saved message it is this shape
that's going to be slowly faded out to letting the user know all we're going to be doing is running a
loop from 1 to 100 and then slowly increasing that transparency as we increase it it becomes faded
out and then eventually we're simply going to hide it using visible false that's it that's all we
have to do to save message great so now we're also able to as you saw we're able to add a new picture
simply browsing for a new picture just like that that is going to be a i don't think i like that
clears it probably should clear out the existing picture fred francisco so what we want to do
is use add a picture this particular button has been assigned to the macro so if we assign
a macro we see that is the browse item pick this is the macro that we're going to go over
right now first of all again as you just saw we want to delete any associated picture when we're
browsing for a new one so for item pick every time we create a brand new picture inside items
it's always given the same name called item pick and here we're simply going to delete it if it
doesn't exist it could create an error so we're wrapping it around on air zoom next and on air go
to zero i'm setting that picture folder remember inside our admin we created a name reach called
item picture folder just to refresh your memory this particular one is called item picture folder
here so this named range we're going to be using we can use that inside brackets inside the
code right here so and i also want to add a backslash to that right so the picture folder
we're setting that up and i want to make sure if the picture folder equals just the backslash or
if there is an incorrect path right then let the user know to please set picture for the items and
how is that helpful well it's helpful if i decide let's say we have an incorrect path let's
add an s to this and i try to browse for that we go back into the item screen and i decide to
add a picture i want to make sure that there's an incorrect path please select a folder right so
it's going to allow us to select that folder so we can quickly browse for that folder so we can set
the correct picture right but of course we have it i'm just going to make sure that so that's what
we have when we have an incorrect path it helps us get to the correct path here but once we do
have a correct path it'll go directly to that path so this is what we'll do so what it's going to do
is you saw another macro please select a folder from the pictures browse for picture folder this
particular macro is located in our application macros here and all we're going to be doing is
just browse for the picture folder all it does is browse picture file log browse for the picture
folder that's in our application we'll go back over a few macros there as well so back inside
the item macro so we're going to run that once we have it we're going to check the picture
folder equals the picture folder we're going to run another check if the picture folder just
only equals a bad class means they haven't added anything or it's an incorrect path we're going to
exit the sub so we're giving them another chance and if it's still incorrect we're just going to
exit the sub out assuming that it is correct we're going to set that full pathway right i want to set
the item picture as the application file dialog file picker we're giving the user the ability to
pick a specific file so we're going to use file picker and then we can work with it we're going
to give it a title called please select an item picture users we want them to use pictures not
any particular file so we want to give it some filters we're going to add a filter we're going
to give it a name called picture files we're going to let the user select from jpg png gif jpeg or
gif okay these are kind of the same thing so and we're going to give them one so we do not want
them to select multiple just one single picture so allow multi-select equals false if for some
reason shows uh anything else other than negative ones that means they haven't selected anything
we're going to go to no selection which is going to skip down here if they have selected it what i
want to do is i want to see if i want to place any picture they have i want to place it in a specific
folder i want to place all the pictures wherever they browse for it inside this particular folder
right because that way i know where to look for it but what if it's already in that folder if it's
already in that folder there's nothing to do so i want to check is it already in the folder that
means is the folder that is the full path they've selected here that's where they have browsed is
it exactly the same as where we want to put it if it is then go to skip we don't need to skip
copy it in so we're going to skip the copy and go down here but what if it's not what if it's not
the same well what i want to do is i want to check to see if there is another one with that same name
if it is we're going to delete it otherwise we're going to simply going to copy the look picture
inside the existing folder so we're going to copy it from its current location selected
items into its new location picture folder and the directory of the selected items this right
here the directory selected is simply the name of the picture with the extension so we're taking
the folder with the backslash we're adding onto the string the name with the extension and that
is the full file path so these together equal the full file path of the picture we're simply
copying it from its existing location into its new location and giving it a very specific name
and that's it all we need to use is your file copy then what i want to do is i want to set the name
remember that name is located directly in b7 but i only want the name remember the full name along
with the extension we're going to use directory for that directory of the select items it's going
to place that file name directly in b7 then we're going to run a macro called item show picture
right so we're going to have that that's going to come up right now but there's another macro called
clear picture if i want to clear the picture i can do that right let's let's load in here let's
load in that picture just all we need to do is load in fred francisco's okay loading that picture
clearing that picture is going to allow us to do that we know the picture name so all we need to
do is just delete the item picture if it doesn't exist right we certainly don't want an error to
come up if they clear click clear the picture but there's no picture there we don't want an error
to come up so that way we've wrapped it on there and i also want to clear any files inside b7 okay
but what i want to also want to show the picture if i know that there's a specific path and i
want to show that picture how are we going to do that well the best way to do that is i want to
place it directly in the center i want to size it accordingly and i want to make sure it's in the
center of column m and i also want to make sure it's in the center between let's say 4 and 9 right
or 4 and 10 something like that right i want to make sure it is centered so we're going to display
the pictures the first thing what i'm going to do is i'm going to remove the picture of his existing
right if there's an existing picture there i want to remove it next up what i want to do is i want
to check to make sure we have an accurate picture folder just like we did before so we're simply
going to run through the same exact checks that we did before checking to make sure if it's not
letting the user know browsing for the picture checking one more time if it's still inaccurate
we're going to exit the sub out setting that picture path setting it into file that picture
path is simply going to be the picture folder combined with whatever's in b7 and remember b7
is the name and the extension so this is the full file picture path then all we're going to be doing
i want to make another check to make sure that it is accurate if the directory of the picture path
vb directory doesn't equal empty then we can go ahead and insert it right if it does equal empty
there's nothing we can do we're going to insert the pictures so we've already on the item sheet
we're already here so dot items.pictures.insert we're inserting that picture path and we're giving
it a very specific name that name is always going to be item pick that way it makes it very easy
to delete then what i want to do is i want to work with it so with shapes item picture we're
going to lock that aspect ratio so that way the uh doesn't get contorted when we resign president
i want that aspect ratio to be maintained so when we resize it it doesn't change horizontal
or vertical too much what i want to do is i want to check if the width is greater than the
height i'm going to set the maximum width as 90 however i'm going to set the maximum height as
80.
That's going to set the width and the height i want to also set the left position it's going to
be based on m4 right m4 is our original cell and i want to base it this is going to be our upper left
location and our top location so but i also want to add to that because i want to center it so how
do we center it well what i'm going to do is i'm going to take the entire width of m column right
here and i'm going to subtract the width of the picture the difference of that i'm going to divide
by 2 what that's going to do is going to put equal distance on both the left and the right of
the picture it's going to center horizontally i also want to center it vertically we're going to
do the same thing but this time i'm going to take rows 4 through 10 and i'm going to determine the
height of those rows i'm going to subtract the height of the picture then what i'm going to do is
going to take that difference and divide it by 2. that's going to center the picture vertically
then i'm just going to make sure it's visible it should automatically be visible automatically
when i insert it here it should be visible just to make sure we're centering here okay that's it
that's all we have to do now you notice another macro remember all when we did here when i decide
that i'm going to injure coffee here so let's pick another let's pick a espresso machine espresso
coffee machine i want to load that machine here i can download that picture just a little bit
sometimes sometimes it does that's a little bit but it usually does it once and then it's fast
very fast right so if i do here fred francisco and then i go back in here see it's fast again okay so
what i want to do here is i want to make sure that we have a correct item id now how do we know that
well we can use a formula for that if there's a value here a text here what i want to do is i want
to check so i'm going to run an index because i want the item id that's associated with whatever
the user puts in so how are we going to do that well what i'm going to do is use an index match
formula i'm going to index the item id i'm going to use a match that match is going to be based on
whatever's in e3 and i'm going to based on item name and zero i want a column and that means what
we're simply going to be doing is extracting the item id based on whatever name that enters here
notice it's blank and if it's blank there's an error and if there's an error it's going to be
blank which is fine that's exactly what we want however if we decide to load it up right
we're going to automatically notice so if i pause the code here and then i go ahead
and i enter the another comment here enter that you're going to see that of
course it's paused which is what we want and now we see here before it gets cleared
out we see that the selected item id is 22. so what i want to do now is i know that's accurate
because b6 contains a value it's not an error i know that the user has selected a correct item
name i'm going to take this item id i'm going to move it directly over to b3 once we're done with
that we can clear this out and once we clear what's out in e to three it's automatically going
to clear out we no longer need this so that's all we're going to be doing so notice e3 gets cleared
out in the next line of code as we run this you see e3 gets cleared out we're going to bring
that id directly inside b3 that will generate the item row and then we're going to be able to load
that item also using data mapping if there's an existing item picture we're going to delete it of
course we have to clear the contents of all the cells associated with that and also i want to make
sure that b4 contains a value b4 is very critical because that is the row of the associated id if it
is blank then we have an incorrect one we need to let the user know please make sure to select a
correct item i'm going to turn off application screen updating making it false that's going to
help us move things along faster make the code faster but of course we have to turn it on before
the macro ends without any exit subs we're going to set that item row based on b4 and then we're
going to simply run the reverse data mapping as we did before but this time in reverse this
time we're going to pull information directly from the item database on the item row on the
item column we're going to place it directly inside the range and that range inside our item
sheet is located directly here on row one in the item column as we loop through that right so it's
going to take information from the items database and it's going to place this picture in b7 it's
going to place this amount the cost inside k9 and so on and so forth as it loops from two to ten so
it's going to place all those values inside there accordingly okay very very good so we've
got that so that's all we need to do and then if b7 value doesn't equal empty meaning
we do have another picture we're going to run the macro show item picture and that's the macro that
i already went over up here okay great so that's it and then of course before ending we're going to
turn application screen updating on background to true item delete relatively simple when i want
to delete an item it's very easy all i need to do is just do that i want an option to come up if
i delete an item i want to let the user know are you sure you want to delete this item that's the
first thing are you sure you want to delete this item giving the user vb yes or no giving it a name
called delete item if it's no we're just going to exit the sub as i just did then what i want to
know if it has been saved or not why is that important because if i create a brand new item and
i enter some name in here right and some type in here and i decide i want to delete that there's no
row associated with that so when i delete an item all we're going to be doing is just clearing the
contents there's no row associated with that so we can skip deleting any row so we're going to go all
the way to not say which is going to go down here it's going to delete any row i mean it's not going
to delete any row because there's no database row associated with that because it has never been
saved however if it has been saved and b4 does contain that row then i want to put that into
a variable called item row and then inside the items database item row and the item row entire
row delete lastly for all that whether it's been saved or not we're going to run the mac or item
new that's going to clear out all the fields then we're going to again we're going to update
the list if i want to update the list i want to make sure to run the macro that updates that
list and as you can see inside the database here the items database here if we take a look inside
that sorted list here let's take a look inside the item database we see that sort of list the last
one that i added test item right that's already included however if i go into the items and i
decide i'm going to delete this item and i add it in and then i click delete and i do want to delete
it i want to make sure it's cleared out right and i also want to make sure that inside the items
database that it is no longer here so you see the last one is no longer here and that's exactly what
i want to happen and so that's it that's all we need to do for items that covers everything on
items okay very good next up i want to go inside the transactions that we've got some transactions
and that's going to be based on this transaction screen we can get to it directly from the home
or we can get to it from here okay so this transactions is almost the same relatively very
similar except this one's got a really really cool load and filter this time in this case
we're not selecting from a transaction but we have different available options that we
can select from the existing transactions here because we have this really cool filter it can
allow us to search for transactions based on date name account or amount so that means if i want
to search based on a specific date and i only want to know those located on six seven i can
do just that and it's going to load only those transactions based on a certain date or perhaps
maybe i want a certain name so maybe i only want fred as a name it's going to search only those
transactions let's clear that out i don't have any on fred but how about david david sure david
will work for us david i only want to show david's transactions great so we've got a search filter
but let's go over some of the required fields i've got different required fields i mean this is
very important i've got a transaction date i've got an expense an income expense i want to know
what type i want to name right name and i want the account that's very important and the amount
so those are all required i've got five different required fields if i do a new transaction and i
don't enter all those fields and i try to save it i need to make sure that we know please make
sure to add transaction date type name account and amount before saving this transaction so we
had forgotten account and amount so i want to make sure that we put those in so that's very important
so but i need to know how many require fields now inside vba we don't want to check if this is empty
if this is empty it's just it's a it's a lot more work but what if i simply count all those fields
of those five what if i count all the ones that contain a value and if it's less than that
so we've got five different fields so if we use something called count a and we look at all
those and we see that five have five of those five do contain values using counting we know that
they can go ahead and save it because all those fields are required memo is not required so this
is a great way to quickly determine if all the chronic fields have been filled out or not so if
b2 is less than that then we know we've got the transaction id that's the specific as we load a
transaction that id will change remember inside our transaction database they have transaction ids
date so just like with that we also want to make sure that we have the transaction row what is
the row that's associated with that and we know that transaction number two is located on row five
inside our transaction database transaction number two is located on row five again our first ones
start on row four just like with items so we wanna make sure that we are going to use the match and
we're adding three if there's an error we're going to show blank right so if it's a new transaction
of course it would be blank all right so i also want to know the next transaction id using the max
just like we did on items so no different there were simply max all the transaction ids of course
i've got a named range based on the transaction ids right named range dynamic transactions so the
next one would be i've got one for transaction type here and i've got one for transaction date
that's going to help us out so i also want to know the selected transaction row we've got
conditional formatting here i want to know the row that's associated let's take a look at some of
the conditional formatting go into this and we're going to manage those rules and we take a look
we've got three different conditional formattings one is based on the selected row of b6 of course
we're going to use a white font and darker color then i'm going to use i want to call the odd rows
but only if they contain a value and column d and i want to color the even rows but only if they
contain a value and column d as well but only for even rows so we've given that a little bit look so
that way odd rows have a light blue and even rows have white for the odd rows and then for the
even rows we're going to show that bluer okay so we've got that so that's going to be
conditional formatting so that way on our selection change event when i make a selection
change all i need to do is place that row and place it directly in b6 that will automatically
fire that conditional formatting and what i'm going to do is i've also got a hidden column
here some hidden values here and i've got the transaction id located here just as we did with
that so if we go into home again and we see we've got a custom filter if i change this to general
you're going to see that all those appear here if we undo that and remember just as we did before
we given that a custom format and to hide those how do we do that we go into marsh number formats
and give it those three semicolons that's going to hide it now why is it important to have those
transaction ids here because when i select on a row whatever transaction id is located in column
c i'm going to place that transaction id inside b3 and what that's going to do is automatically
calculate that row when i know the row of the transaction i can automatically load that data
here and again using data mapping just as we did before j4 m4 j6 j8 those are all of the cells
associated with those transactions right here j4 here m4 here and then j6 so they're associated
with that that's going to make coding a lot easier i also want to know the selected name row so what
is that selected name row that is important why is that important because i want to know what
row associated so if i change the name on here remember we have names is all i want to know to
see if it's a new name if i decide to type in freder's fred freddy right and i want a new name
and that name doesn't exist i want to be able to add it right so if i how do i if i save that
transaction i want to make sure that we're saving it or maybe i want to add it here so i can quickly
add it as a vendor here and if i save it this is automatically now going to be contained inside
the list quickly and it's associated with this v7 so basically if i have a match based on name
names this is a named manager let's go into that so we can look at that into the formula i know
it's kind of a strange name called name names name is our database right here and names names as
our database here and names is located here so we have a list of names here so what i know is if it
doesn't exist on that then it is a brand new one notice we just add fretters fred freddy here so
we've added that here because we know that if it's blank here we know that when we edit it and i'll
go over this but now when we click it we can add update so if i want to update an address here
i can do just that very very easily with this and of course we can save and i can save it
and kind of change it as customers so now if we go back into the names database we see that it's
already been added okay so i'll be going over that like i said this is a massive application right
i started this monday and it is now friday so working hard for you i hope you appreciate it
don't forget to smash that like button share this video we appreciate you don't forget to subscribe
if you haven't already comment below every little bit helps makes maintaining these videos always
free and of course many ways to support us using the links down in the description so continuing
on with our transactions so i wanted to explain to you we'll be going over how to do this in a
moment but i wanted to make sure you understand why we have a named robe because this is the row
that's associated with this name as soon as we add something that doesn't exist this is blank that
way we know how to automatically based on this if it's blank we know to add a new one however
if it is not blank we know that we can simply add it we can simply select it and then edit the
existing one because we know the order that it's in great so i also want to know the next name
id just as we did before inside and of course we can do the exactly the same right just this same
inside the orders the same thing here right if we add a new one here john jacob smith or jay comes
smith okay so if we add a new one i wanted this name doesn't matter okay actually i don't want
to do that actually i'm going to go into data validation just real quickly and i want to make
sure that users can enter a new one we don't want an error so we want the user to add something
right so if we click here i want to make sure that they can simply add it and save it here okay
good that's exactly what i want all right good so we see that we're going to do the same thing so
we have the same ability both in our orders and our transactions here okay so we also want to know
the next name id this is simply the max of all the name ids right name ids are located right in our
names database these are all the names ids i want to know the next one which is 26 using the max
formula so that way when we create a new one we know exactly what id assigned it to and we know if
it's new or not because this is going to be blank if it's not we know so we've got we understand
what we've done all this let's go ahead and get into some of the particular macros on-site our
transactions and of course we're going to do same thing transaction column some variables here
that we'll be going over answering we want to load the list the first thing of course when i
create a brand new one i want to make sure that we're loading this list of transactions and it's
going to be based on some filters we know what database it's coming from we know it's coming
from the transaction database and we have to have some dynamic search criteria it could be name
it could be several different ones right so when we create that right i want to know what are we
basing it on right if i create an account right i only want to know maybe lease expense so when
i create a name range i only want to know those transactions associated with lease expense so
that's going to be some criteria but it's dynamic based on the name and based on the value right so
we take a look inside the transaction database we have some criteria but it again it is dynamic
as you saw it's going to be based on whatever's in transactions e5 and then also what i want to
know is i want to know the least expenses but i don't want to know the what if they just entered
least i don't want always an exact match i still wanted to come up if they enter a personal match
so if i type in just lease alone right i want to make sure that it comes up so what we want to
do is i want to match any possibility if we add in those asterisks before and after as you
saw in that that is going to be the wild card that means any count containing the word lease
is automatically going to come up if we just add something like e right you're going to see a lot
of them come up anything that contains the word e so inside our transaction database we want to
surround it with that however not for every type of search right because inside our transactions if
i enter an amount if i let's say i want an amount i want an exact amount or maybe a partial match
or maybe i only want to know those great less than 100 so if i want to type in less than 100 i
only want to know those transactions less than 100 so we can use that so for this particular or maybe
on a date maybe we want to set a specific date so if we have a date here maybe we only want to know
less than 6 let's say 6 2 22 now or 2 0 2 2.
Okay so we only want to know those transactions less so
we could for these types for both date and amount we certainly don't want to use the asterisk
right i want to use exactly what's in there so we have to make a little bit of a change inside
our formula and let's take a look inside that so our transaction so we've got a little bit
of a formula here first of all i want to know if it's enter search if the word is intersect then
just put in does not equal right enter search is just the default so we certainly don't want to
search for enter search otherwise what if it's name or account if it's name or account if the
user has searched by name or by account only then are we going to use the asterisk based on whatever
they've entered which is f5 we're going to add the asterisk before and after it that's going to be
automatically so this wild card so that means any search containing those will be up however what
if it is amount or what if it is date in that case we're simply going to put in exactly what the
user has entered transactions right so that means exactly as so here it's less than 62 but what if
i only wanted a 6-1 well i can do that as well inside transactions if i want to search only on a
specific day i just do 6-1 and it's going to enter those transactions only so you see in date and
amount we're going to enter exactly what users has entered otherwise if it is account or name
we're going to use the asterisk and we can clear the filter here i'm just going to clear the filter
okay so now that we understand how the criteria works inside our transactions we want those search
results to come here and i want the transaction id the date the name account amount all that
information is going to be coming directly over insider however of course that transaction id is
hidden using the three semicolons so that's what we're going to do we're going to run that advanced
filter and that's all we're going to do so if we clear the filter right loading the list is here
let's go over that clearing clearance filters next so all we're going to do is certainly clear all
the existing transactions so when we run this i want to make sure that from c7 all the way
through g and down everything gets cleared out that's the first thing we want to do so inside
the transactions b6 i also want to know why is b6 important b6 is also our selected transaction
role i want to clear that out too that's going to remove that conditional formatting so i want
to clear that out okay and also i want to clear out all the associated data here clearing all
existing transactions then we're going to focus on the transaction database determining the last
row if it's less than four that means we have no data we're going to exit the sub up we're
going to run our advanced filter our advanced filter is going to be coming directly inside our
transaction we're going to come all the way to g from a3 to g that criteria is going to be l2
through l3 and those results are going to come o3 through s and that's exactly what we have inside
our advanced filter a33g our results l2 through l3 and our our results o2 through s2 we're going
to determine the last row of our results based on column o if it's less than 3 that means we have
no data right if it's less than 3 here we know we've got no data if we do have data i'm going to
bring all of that data over so the transaction c7 through the last row plus 4 and that's important
because our data starts on row 3 here while inside our transactions our row starts on row seven so
we need to compensate for that by adding four and that's just what we do right here and then we're
going to bring i'll bring those transactions over and then of course if transaction what i also
want to do when i clear that filter notice that that row got removed right we removed removed but
what i want to do is when we run that filter i want to look for that transaction id here and if
it's found remember that's hidden so you can see up here in the formula bar it's 1 right so what
i want to do is i want to take this transaction id and i want to look to see if that transaction
id is here if it's found whatever row it's found on notice it's found on 7 here i want to place
that 7 right here so if i clear that filter you see that that 7 got placed right here that
7 got placed here because it was found on row 7. that's going to trigger the conditional
formatting so the user knows automatically what transaction has already been loaded right so
if i select here and i clear the filter out here i want that exact row to be selected again remember
when we clear the filter out this gets deleted but i still want to know what selected row so once we
locate we know the transaction id is here once we locate the transaction id here has been loaded
so we're going to check to make sure that that row gets selected again simply by adding the row
in here and that's what we're going to do here however if there's no transaction id in b3 then
we can exit the sub out so we're going to simply look if b3 or b4 before meaning there's no row
associated with that we're going to exit the sub now what i want to do is i want to place directly
inside b6 remember b6 is where it's going to go i want to look for this this transaction id and
i'm going to look in here if it's found whatever row it's found on place it directly inside b6
that's what we're going to do with this line of code b6 is equal to the transaction c7 through
the network we're looking for whatever's in b3 we're going to extract the row if it's found
we're going to place it in b6 if it's not it could create an error so make sure you wrap it in
on a resume next on or go to zero okay and then if b6 does not equal empty then what i want to do
is i just want to select and that makes sure that any once we select d it's going to automatically
load in that transaction it's going to make it load it in and that way any changes that have
been taken place will automatically load it in okay great so that's all we can do so that that's
important because if we've made an update right if i change this to test memo 7 right and i make that
change and i update that list and i save it right and i want to make sure please add oh we haven't
had a date okay let's save it i like that adding davis we've added that now he's been added and
so now we see that test memo seven we select here that test memo seven that memo's automatically
been updated okay great so clearing the filter all we need to do is just click enter search
right as soon as i make a change here it's going to trigger that how do we know that well let's
take a look inside the transaction screen here transactions here here we take a look inside if we
make a change a worksheet change to f5 and we want to make sure that it's nothing then we're going
to automatically load this list so basically any change that we make to f5 we're going to run that
macro that we just went over load list so that macro gets run automatically here and likewise if
we make a selection change remember we're making selection change here we want something to happen
i want to take whatever's in c and the selected row and to place it directly inside b3 and then
load it up so we're going to make sure if count large user selects more than one cell exit the sub
if the user makes a selection change from d7 and we want to make sure that c contains a value c of
course is that transaction id we want to make sure then what i'm going to do is i'm going to take
b6 and i'm going to place it directly i want to know that row remember that selected row must
go directly in b6 and the second thing i'm going to do is i'm going to take whatever values in c
i'm going to place it directly inside b3 so b3 is simply going to equal c in the target row set that
transaction id then we're going to run the macro to load the transactions so that's all we have to
do clearing the filter as soon as we enter search near any change to f5 is automatically going to
trigger this macro to run so we don't need to run it again when we add a new transaction all we're
doing is clearing a bunch of associated cells setting j4 setting that default date new i want
to make sure the current date june 10th right j is going to take on that current date and i want
to make sure that j6 select there's one more thing i want to add remember we were discussing having
a default account remember we discussed that early on the training this account we want to set the
default j8 what do we want to equal we want to equal whatever is located here in admin g and we
could just call this let's give it a name which i like called default transaction account okay
makes it easier oops spelled that wrong default transaction okay let's set that up and notice when
you create that we want to create it's better to create redo it through the name manager so default
transaction account edit that and just update that default transaction account that's what i'd like
to call it i'm going to copy that and click ok and we would name exist because we added it
twice so that's fine all right no problem do they're both here we could just delete this one
here good okay i wish it would replace it you know on a single cell but it just adds to it instead of
replacing it okay great so what we want to do is dot range and what is that called so what is
the cell located we want to place it directly inside a particular cell inside that let's go
and do the transactions again here and i want to place it directly inside j8 so the transaction
j8 dot range j8 for the default is simply equal to we can use the brackets here to make it a lot
quicker pasting that default transaction that's going to set the default trends i set default
transaction account that way for new transactions it's automatically going to set that account if
you have that available i mean if you've added it so now when we go back in here and we just
add in our default creating a new transaction it's going to add in that default account right
here okay good i like that so we've now added in that default account and we're going to select j6
and that's going to let the user automatically add in anything they want here inside j6 continuing on
with our code now what we're going to do is we're going to save and update the transaction we'll
move a little quicker because these are very very simple as we mentioned before b2 is the number of
required fields if it's not 5 we know that they have not added enough required fields we're going
to let them know if b8 equals empty i want to let the user know to please save this transaction
before remember how do we know that well b8 is our thing so if we added either none right so if if
ba contains a value i know it's an existing name if it is not if i put in another name i want
to there's if b it is empty i need to know let the user to add that name before so if b8 is
empty please add and save whatever's in j6 so we're going to try to save it please make sure
to add transaction date okay we didn't add the amount here so let's add that first then click
save and we see please add sdss before saving this transaction right so we can add it we can
cancel that so this is going to let us know and add that name first which i really like okay so it
says here so we know because that name has never been saved what we're going to be doing is we're
going to let the user know for this message box and we're going to run a macro called transaction
add edit name we'll be going over this macro in a little bit just a little bit transaction add
edit name and that's going to automatically pop up that user form so b if b8 still equals empty
we're going to even after they've run this macro to add it if it's still empty we're going to exit
this up okay if b4 equals empty we know it's a new so now we need to differentiate between in a brand
new transaction meaning b4 is empty or an existing transaction where b4 contains a row so we need to
differentiate that if it is new all we need to do is set a new transaction row based on the first
available row setting that trans transaction id placing it from b5 into b3 and setting that
new transaction id in column eight if it's an existing transaction we're just going to extract
that row directly from b4 then we're going to run data mapping just as we did before simply taking
all of the information from the ranges there that we've mapped out and placing that inside the
database the transaction database then we're going to simply run the mac or that macro we've already
been able to reload the list and then a fade out message and then fade out message is simply just
what we did before and we just click save and it's going to be a fit of oh mary poppins we've saved
that before okay there we go saving that okay so now we see transactions saved here so we've got
that available here so i like that very much very good now moving on right that that beta message
is simply the same thing that we went over before loading the transaction again we're going to clear
the contents going to make sure that b4 contains a value right we've already know as soon as we
run this that macro to load it's going to run as soon as long as we have a row located in b4 if it
does have a row of course we're going to put that into a variable called transaction row we're then
going to run use data mapping in the reverse style simply adding all that data in and then turning
on application screen updating that is it deleting the transaction very very similar to items all
we're going to be doing is making sure has the transaction been saved before we're going to get
after the warning message if it has never been saved because b4 is empty we're just going to skip
deleting the row but if there is a row we're going to put that into a variable then what i want
to do is just actually we don't need this here we can just delete that that's not a transaction
row delete the entire row delete that transaction row we don't need it twice there that's not
going to be helpful okay so the transaction deleting the transaction row and then we're going
to add the new transaction and reload the list that is it all right now this is one remember we
had another one called transaction adder edit name this is the macro that's automatically been tied
to this right so i've got a brand new user form this user form is located right here called add
edit names this is a user form it's very very cool because it allows us to one have a drop down list
we've got customer vendor employee name and we've got different fields associated with that this is
called field one field two three and it's gonna go all the way to field 7.
They're given specific
names this one is called name type this one is called status right so we've got different field
names so it's going to allow us to add or edit names when i run this macro that's going to
allow us to edit names what i want to do is i want to determine if it's a new or an existing
so transaction add our edit name we're going to define the name field as control and the name room
as long and the name column is long we're going to be using another data mapping what i want to
do is i want to clear any fields in the form first so the first thing with the transactions
focusing on this screen i want to know is it a new name or is an existing name that's very important
right a new name knows harold banks is new while mary badger david davis is existing being
b8 so right if i haven't added merry badges i think i add this through the database
so i think if you add it in a database notice that harold banks here has not been added
so if i decide to edit here we know it is a new name because b8 is empty right if we look inside
our names database we don't see that harold banks is in here anywhere at all so we know it's going
to be brand new okay so to do that we need to differentiate b8s that differentiator if it's
empty it is a new name what i want to do is i want to take that field one from our form here the
value of that whatever is in j6 i want to place there j6 is inside it is our name transaction
right here so what that means automatically when i launch this form i want that name to go directly
inside field one i'd also want to set it as the vendor so what i want to do is i want to set
that default to whatever the vendor name is that type remember the name type here this is the field
that we're going to focus on here this is our name type right up here this first one is called name
type i want to set that up to vendor right because normally if we enter a transaction of course
we can change it but we're just assuming that they want to enter a vendor and this maybe not a
customer i also want to set the default to active right that's status value to active however if
it isn't existing i just want to set the name row this would be existing name existing name
because ba contains a value if it is existing like mary poppins is here or david davis is here i
want to make sure that we're going to load all the information i think this one has addressed as well
i want to make sure to load all the information associated with that vendor pulling in all the
information from the names database so if asia electric i want to put in their po their city
state i want to bring all that inside the form using data mapping so how are we going to do that
well the first thing what we're going to be doing is we're going to set that name row whatever's in
b8 i want to know the database row then what i'm going to do is run a another loop right from 3 2 9
why 3 to 9 because we already know that it's name id we know that it's a customer from 3 here all
the way to the last 9 okay actually to the email which is nine here if we take a look inside this
column equals column we see that that is column nine okay so i'm going to run it there that's the
one i want to run and i want to run a loop okay so this is going to help us out notice that we've
defined the name field as a control so we're going to set that name field to equal to add edit this
is our form name controls field and then the name column minus two why is this important our column
starts out of three but our first field name which is our name is field one do we remember that our
first the name of this field it's located field one this is field two so i wanna put that name
from column three inside field one so to do that we need to map the data so name column three minus
two this is one field and field one so our name gonna come from three so we're gonna loop through
that so our name field value is equal to the names based on whatever's in the name row and the name
column this is our database this is where it's coming from so this will loop out that saves us
a lot of code so we can automatically fill in that form using data mapping and then the last
two i want to set the name type right that add or edit name this one the name type based on
whatever's in column b and i want to set the status whatsoever in column j so again taking a
look at this our type is in column b our status is in column j we're setting those two because
those are different fields those are drop down lists so they're slightly different okay great so
that's all we have to do and then i'm just simply going to display the form and that way as soon
as we run that form that has been tied to this button here when we edit that we see that all the
data will load automatically and if we decide to change that or save it we can automatically update
it so if i decide to save this clicking save it's automatically going to update it here how do
we do that well that's the save button so if we go in here and we click view code we see that we
have the save button click this is the code that runs automatically this is the code that runs on
cancel we have two buttons cancel which is simply going to hide the form and the other button called
save button so this is going to save our fields so again we're going to do name field as a control
we want the name row and the name columns long just as we did before i want to make sure
that they've put in the required fields if the field one which is the name is empty or
the type is empty then we're going to make sure to please add a name and type before saving we're
going to exit this up those are required so once we know we've got the required fields then we're
going to focus on that names database we're going to check for a new or existing what i want to
know is if active sheet b8 equals new name well now why are we using active sheet the reason
we're using active sheet is because i have this type of details and programming based
on two different sheets transactions and orders let's take a look in here both of them
use the same cells b8 and b9 right i've used the same so we can use active sheet selected name
row is going to be here so we can use this same additional feature for both the order sheet and
the transaction sheet so that's why we're going to use active sheet because either one we're using
the same detail same cells so if active sheet b8 is empty we know that there's no row that's
associated with that name we know it's a new one i need to find a new row for that that number is
going to be the first based on the names database the first available row right then what i want
to do is i want to take that next id right now look inside here this is the next id using the max
formula also inside the order screen same thing b9 is going to have the next one exactly the same
cells so we can do the same function on either screen as long as we use active sheet because we
know if they're on orders and they decide to add a new name or if they're on transaction it's
going to be b9 b9 is going to come from that active sheet so we're going to so we know that a
our id is going to come from the axe gp9 this is our unique id otherwise what if it's an existing
so existing for this existing name all we need to do is extract the row directly from b8 this is
our existing name row everything else we're going to do whether it is a new or an existing name
we're simply going to make those updates right so column b is going to take on the type column j
is going to take on the status then all we need to run the loop for the remaining fields from three
to nine just like we did before except in reverse before we loaded the information into the form now
we're taking the form details and putting it into the database we're going to set that name field
again just like we did before field name control our first field is field one but our column three
so three minus two is one field one all the way to field all right okay so then we're going to simply
take whatever's in that database names database the name row the name column is equal to the field
name value this adds it adds or updates the field data inside the database okay so that's it that's
all we have to do then we just simply hide that form then what i want to do is once we've made a
change i want to run a macro called names update and sort remember we have that macro here called
names up to actually haven't gone over this macro simply all we're doing here in this macro which
we will go over in a bit is simply updating those names we did go over briefly updating the names
to make sure that we have a macro that's going to update all names now i want names i want our names
are separated between customers vendors employees so i want three lists one with all names one
with customers one with vendors and one with employees so there's a macro that we're going to
go over shortly that's going to do all that hard work and heavy lifting for us to separate it
into three different rules why because we need names customers for invoices i need vendors for
purchase orders and i need employees to assign to employees for work orders and other things so our
orders are of course our employees are assigned here so i need three different lists right vendors
for here for invoices we're going to need when we have invoices we're going to need invoices so
we need three different lists for that so that's going to help us so on our names database we need
to create a macro that's going to do that and as soon as we make any changes to a name we want
these lists automatically to resort through a macro and that macro is located right here called
update and sort now what i want to do is i want to differentiate a little bit between remember i said
we can run this macro whether we're on orders or transactions so if we're on orders what i want to
do is i want to set once we've updated if we're on the orders form whatever changes that we've
made to value one that name i want to put it in f5 right so if i've decided i've made a change i
want to make sure that that change gets reflected inside f5 what is f5 well that is right here right
so if i decide i'm going to add a name right and i want to update that let's say i want to update
somebody's name let's say john james and i want to update john james and i want to make give him the
last name smith right i want to make that update so when i make that update i want to make sure
that that change gets reflected directly inside f5 right if i decide to make that additional change
like here asian electric and i want to take away that s i want to make sure that that change
gets reflected directly inside j6 so here we have the differentiate so if we're on orders we
certainly need to update f5 which is the name of the orders otherwise inside the transactions
on the transaction j6 is going to take on that updated field that way we update the name inside
that field as well whether we're on orders or whether we're on the transaction sheet and that
is it that's all we need to do as far as saving this so that's all we need to do relatively easy
okay very good so inside the transactions macros we've gone over added edit name we've covered
all the transactions we've covered all the map items and we've covered most of the add and edit
names very good we're making great progress we're going to focus on the order macros and the order
sheet macro so that's what we're going to go over right now mentioned earlier this order sheet has
the capability to handle four different types of orders whether it's estimates work orders invoices
or purchase orders this sheet can handle it all because there's so many similarities between
those four different types of orders we might as well combine them into a single sheet however we
certainly want to make sure that they look and act very differently so that users don't get confused
which type of order they're on and so what we've done is we've used conditional formatting to color
them accordingly and so they can have each one has a different look and feel now when we do change
the order types we have the order type located inside b2 so when we change it to a work order b2
is always going to tell us what that order type is we've given b2 a very important name variance
called order type so that we can refer to that both using formulas inside conditional formatting
and inside the vba code so we're going to often refer to this order type here the name range right
up here you can see this that's very important so the first thing which you'll obviously recognize
very quickly is the conditional formatting that's used to differentiate between the colors of those
type of forms and it is based on that conditional formatting on that so let's go ahead and take a
look inside some of the cells here and we'll look at some of the conditional formatting that we're
using so we're going to click on manage rules selecting some and we see that we have different
colors based on this so we've got our header row we've got the second row we've got a header row
which is row one we've got our second row which is row two and then we have the remaining rows each
one of them has individual conditional formatting and our natural color which is the color is blue
so invoicing is blue so we don't need to change anything to blue because that is our original
color so we're going to make changes for estimates work orders and purchase orders so each one of
those is relatively simple if b2 equals admin c5 we can actually change this to the estimate order
so we can change it i generally like to go with it so we'll do estimate order so this is the same
thing the same named range we're going to get the same output when we apply it so there's going
to be no difference so if b2 equals estimate order we're going to give it this yellow color for row
one in this yellow color just like they are it's simply a fade out color that we're going to use
which is the medium yellow to a little bit lighter yellow so we go into the fill we use the fill
effects and we see that it is this darker yellow to the medium yellow okay and we've done simply
that on the second row so the second row that we have row two here on our edit row is that medium
color based on the estimator so when we do the fill effects we see that we have that medium color
to that light color yellow okay so only of course when our b2 equals the estimate order and then
lastly they're all the remaining cells which is all the cells all the way around the sesame order
including the header here we've given it that light yellow color so this is a single color
we've formatted this this is that light yellow that's single color yellow so i've done this
exact same thing whether we're on admin remember this can say work order i'll add edit that to
underscore work order which is the same thing work right you can use named ranges to make sure you
got the name range correct work order okay this is the same one and it's b2 equals work order
so the greens and then likely the purchase order all have the purple so we're going to use that and
click apply and then we have everything there so that when we change this from to work order we
get the green from to purchase order we get the purple and our original color which doesn't have
any of them this is our original color that blue so each one of those contains a different color
now we have some additional formatting as we notice here income excuse me income and payment
amount those are visible for both invoices and for purchase orders however when we select on
estimates or when we select on work orders we see that those two fields are hidden so we've used
conditional formatting to do that so we go into the home and we can digital formatting we see that
we have additional ones for order type equals work order we want to color those green right so we've
got some additional formatting and we also want to color we don't want to remove the border so we've
removed borders for both estimate orders and order types if either one of those are actually we can
remove some so let's take a look at this rule here we format that and we look in the borders and we
see that the right border has been removed right so i don't want that right border to show up for
both uh work orders and estimate orders if the order type equals estimate order or the order type
equals work order in that case i want to remove the right border and we're going to apply that to
c11 and c12 so we want to remove that right border and again just likewise in c12 to d12 what do i
want to do if we edit that rule again if either one of those are s motor what i want to do is
i want to remove the top and the bottom borders right i want to move those and that's on row 12
so i don't remove those borders so basically we just don't want to show those on either estimates
or work orders okay so but otherwise both purchase orders or invoices can have the income account
okay so that's pretty much it for the conditional formatting and we have a little bit more on
the taxes i've got some additional conditional formatting down here payments and balance that's
not going to show up on the work order so when we click on work orders we see that there's obviously
a lot more here we're not going to show up and also estimates here estimates would also not have
would have a total but we wouldn't want to show any payments so we want to don't want to show any
information so when i click here and go into the conditional format and we see manage rules we got
some additional rules here so let's take a look at this row or so this particular rule or order type
we look at this we see both for work order and order estimator for both of those what do i want
to do i want to give it the font here i want to give it a white font color that white font color
will hide the payments and the subtotal certainly for both estimates and work orders we do not
want to show any type of a payment or any type of a balance on that so we certainly don't want
that however on work orders we also want to add additional work orders we see we don't have
any total fields prices are total so we don't want them to show up on work orders and we also
certainly don't want to show other things on work orders so we notice we want to hide this entire
section on work orders so when we look down here we see the work orders the type work order we
give it a format we see that the borders the right border has been hidden and we've given it them
information the font here is going to be automatic so let's go ahead and switch to work orders
so we can see how that applies automatically on work orders we see that we've hidden everything
everything's been hidden so we've got a lot more for work orders hiding up all those borders
right so going into the home right conditional formatting and manage those rules and we see
that we have a lot more in work orders here on workloads we've got a lot of information here
all these cells we want to hide what i want to do i want to hide that font right so we're going to
format that and we see that the font here is white we've chosen a font color of white so that well
we can hide those on work orders because we don't want to show any total information on work orders
so we can see how conditional formatting can also hide both rows both information and both cells
using the font color and also we can hide the borders on that for those that we don't want to
conditional formatting is a great way to help us differentiate between type orders we also have a
conditional formatting here we also want the font we notice that the font size both the font
and the text are different right we notice that we now have a formula called order type
so the order type whichever is located in b2 is automatically going to be an upper case and it
is automatically going to show that one so when we click invoice it's automatically going to show
whatever is located in b2 which is our order type and we have conditional formatting here
also to change the color of that so we have three different conditional formatting
for purge shorter work order and astromotor the natural color the inherent original
color is blue so this what you're seeing now no conditional formatting applies however when
we change it to purchase order the font will go to purple work order green and the estimate order
this brown or yellow okay so that's how we change that there and that's going to be really helpful
as we go on all right let's take a look at some of the additional data notice when we change this
to work order we see that a lot of things change this changes to work order number this changes to
work order date we can simply use that order type and the number inside a formula we can simply
use the employee name here we can simply use the order type and the date here so that way we can
automatically customize that so we've got that inside there so inside the estimate it's going
to show estimate date estimate number inside the purchase order we're going to show purchase
order date purchase order all we need to do is add in the order type very very helpful we also have
conditional formatting here for both work orders and estimates so that we can hide that so that's
just conditional formatting just as we did before okay great and we can see that we can use the
formula to add in the dynamic order name the order type name here as well and we can have that here
too another thing that we have here is i've got here vendor showing on purchase orders however on
estimates work orders and invoices it's going to show customer we can use simply formula for that
if the order type equals purchase order show the vendor name remember this is the dynamic name
using vendor name right just in case we forget remember vendor name this is the vendor name
whatever use you know right if you want to change this to purchaser right let's say we change this
to purchaser right we want to show that we want to make sure that that gets automatically reflected
in the orders right so that's very important so when we check purchase order right we want to make
sure that this says purchaser right so it changes the word to purchaser automatically that's why
we're using that dynamic one here in vendors so we can change that very very easily and we can change
it back to vendor so i'm familiar with that so great we can quickly see there's that extra home i
was looking for that couldn't find it interesting so back to home back into orders here we're going
to go look and also what i want to do is i want to be able to show dynamically right of course we've
got some data validation that's going to help us but i'll make sure we've gone over all the screen
so again here we're going to use that dynamic order status right order order type along with
the status so that it says purchase order status or whether it shows estimate status or whether
it shows work order or purchase order style you get the point right we're using the dynamically
same thing for here so that status is going to be done here okay very very good in this particular
weather regardless of the form type we're going to have a total in here that's automatically
going to total it so we're going to let's just bring up a particular estimate actually let's
bring up an estimate here or invoice would be fine here so what i want to do is i want to have a
formula this formula will always be here we've got a limited number of rows so it's okay to put in
a formula here certainly we can use vba to put in the formula but in this case we can just use this
if g10 equals empty meaning there's no quantity then show empty otherwise multiply g10 times h10
okay otherwise show blank so does this form us that way anything that's blank as soon as we added
another item that total is going to automatically be present as soon as we change the quantity
that of course total is going to automatically update great we have the payment amount here
so if we decide to enter a payment amount here that payment amount's going to be reflected inside
here notice this is linked to d12 so d12 is the payment amount so that is automatically going to
be reflected we're going to have a subtotal which is the sum of the total right we're also going
to have the tax right the tax is going to be multiplied subtotal by whatever the sales tax rate
is the sales tax rate and actually that should be changed right it's going to be either the sales
tax or the purchase tax right so we should update that let's make an update because why is that
important because we have a purchase tax right this is not going to be accurate for purchase
orders so i do need to update that if i decide i'm going to put in a purchase order right we notice
here's a purchase order this is going to be the purchase tax which is five percent not the sales
tax rate so what we want to do is why not make the update if right our order type let's do order type
right is equal to the purchase equal to let's say the invoice type which is the invoice order then
i'm sure we're good to use that sales tax rate otherwise right i want to use the purchase tax
rate otherwise i want to multiply i-35 here times the purchase tax rate purchase tax rate which
is right here okay so the purchase tax rate is different i want to make sure you cleared that up
okay and then fix that up don't forget the comma this is then times not and okay excellent so now
we have automatically so now we can calculate that and we have the payments here we can calculate
payments obviously can't have fifty dollar can have a fifty dollar payment so we have a balance
here that's gonna look good so that way our purchase tax is automatically based on whatever
the purchase rate is if it is not an invoice order okay good i like that so we have a dynamic taxing
based on either the purchase order or the invoice we have the payments amount we have the total
which is simply the sum of both the subtotal and the purchase tax and then we have the balance
which is simply our payment amount minus the balance amount that's going to give us that okay
great so we have an understanding of the basic idea of what we're focused on here we're going to
be able to search for an order that footer message we have seen we have that footer message it's
going to be linked to a formula and if we remember correctly we do have named ranges associated with
that for the message we've got two name ranges one called the footer message text and we'll call
the footer message name right we have those two so if i want to extract that footer message text
we can use an index to do that and we're going to base it on the footer message that user has
selected located directly here in d9 if they have selected a footer message called delivery time i'm
going to index that footer message text i'm going to extract that based on the row and i want the
column and so basically all we're doing when the end user enters the footer message here i want
whatever text associated with that to show that right here okay so when they change that it is
automatically selected here great so that's just simply that and i've centered it and increased the
font a little bit to get as nice footer message automatically very very good so we understand that
and also we have the account set up with that now keep in mind that this is an expense account
expense accounts associated with the purchase orders if we have to dispense out however if
we create a brand new invoice we certainly don't we want those to become income accounts so
we've got some data validation that's going to change if based on that if we take a look inside
the data we take a look inside the data validation we see that the accounts associated with
income accounts on this invoice for cell d11 however if we change this to purchase order we
see that we have some differences here so if we take a look inside the data data validation
we now see that the accounts associate are the expense accounts right when we purchase
materials we need them expensed out when we invoice we need them income so we're going
to use the same cell and all we need to use is differentiate between the two different
accounts listed i'm going to show you how vba can change that based on of course the order
type and then we have the payment amount here okay great so let's get into some of the vba to see
how all this is working we're going to start with the on sheet code because there's a lot of sheet
that's going on code when we select a vendor here we want the address to show up here right when
we make a change so let's go ahead and go on to the on sheet code and so we see what's happening
here so we're going to click on the order sheet and we don't have too much code just a little
bit of code here a few lines of code right sami along with this so we've got the item row name
row the found item is arranged so i'm going to need to look for that item and the order type that
order type is very important so when a user makes a change to e10 through e34 what do we want to
happen e10 through e34 all the way down here well what i want to happen well if they select
an item i want to have the item description the quantity default quantity the price and the total
automatically appear here assuming that the item is the correct item that they've selected from
the list if they don't let them know that it's incorrect item okay so when they have a correct
item here and also i want to make sure that of course if they do type it in i want to make sure
that it's automatically auto populated so we have auto population autocomplete right here as well
using the same thing again we are starting we have those links notice that those links are down here
just like we did before remember we too so i have again i have again here space is located here just
as we went over before so we have the don't break the chain method we have all these cells filled
with values spaces right to make sure that it's linked to this chain again if we were to take
a look at that and we were to change it back to general you would see all of those items appear
here but they're hidden because they're using the three semicolons and again we've used that here as
long as our link so i'm going to control undo that control z it's going to go back to
hidden that's what i want so they can quickly add a new item so if i want let's
say grind and brew let's type started another g r i and grind and brew automatically it's going to
come up okay using autocomplete so what happens if if this particular item has been found inside
our item database i then want to extract the the description on that what is the sales description
or whatever let's go ahead and put that that disappeared so we have our purchase description
here so what i want to do is i want to have the purchase description the sales description or
the word description appear based on the type that's very important how is that different notice
that inside our orders here take a look based on we have a purchase description however if
we're adding a new work order we want our ad stock value this is going to be our
work description and then if we click invoice we have three different we have an
invoice description that we use on sales so we have three different descriptions based
on the form type estimates would also handle of course the same estimates and invoices would
both get the same description so for those types we would also find out that the sales and estimate
we have the same description purchase orders would get this description work orders would get this
description so that's very important so it's going to be based on the type so we're going to
so when a user makes a change i want to make sure that b3 is false what is b3 well b3 is when
we're loading a specific invoice when we're loading right this is going to go to true that
means when i load it that's also a change right we're also making changes here but that type of
change is different that type of change i want to load whatever was previously saved here inside
our order item database right here and that way if we make a change to it that change gets saved
what i mean by that so let's say i want to change this to commercial brewer let's just say one set
i want to change this just this just this once to one set right and i want to save and update
that right and now if i just clear that open dove i want to make sure that that automatically
sets up johnson brewer but i don't want to save that in the main file so if i put that in again
right i want to save whatever is in the work order description here so this is the update so this is
the work order description i have some older data here on that so i want to make sure the work order
description comes up on a work order however if we add let's say a new estimate i want to make sure
it is the sales day so if i put in brewer right i want to make sure that the sales description
appears here on an estimate on an invoice okay so let's continue down with the code so we want
to make sure that b3 is false right b3 means when they're loading when this is loading from a
previously this will go to true and we'll explain that differentiate between that so either it's
going to come from our items database right here one of these three values or if it is an existing
one it's going to come directly from whatever description we previously entered notice
we saved this right want to come from here okay we know that okay so we want to make sure
that b3 is false we want to set the order type into a variable that comes from b2 okay we're
going to set that and also what i want to do is if e does not equal empty meaning the user has
entered a value if they have entered a value we're going to continue what if they haven't what
if they've cleared it out if they've deleted it i want to make sure that we've deleted it so we
need to differentiate between entering a value here or clearing it out by entering a blank
if it's blank we're going to do something else and that is clear everything out if it is not
blank we're going to enter that so we're going to first check to see if it's blank if it is not
blank right here contains text okay we're going to check it out what do we want to do first i
want to set the item row i want to know i want to find out this item but that's very important
that's the item row from this existing database i want to look for this item name inside our list
of item names i want it found and i want to know the row that's associated with that we can use
the find command to do that so we're going to look in the items database we're going to use that
item name remember not the sorted one the original one the item name i want to look for the target
value i mean the one the user just put in and i want to extract the row out of that using the
find and i will put that inside a variable called item row if it has been found it won't be zero
if it's zero let the user know the best is the the item was not found however if it has been
found then i want to move on we're going to use select case and why is that because remember we
need to do different things based on that right i also have a different price right i have a sales
price and i have a purchase cost right if it's a purchase order i want to put that purchase cost in
if it's a sales price i want to put the different amount in so we can see here it's inside this
would be the sales so if we put in that first item here let's say brewery right brewer right i want
to put in that 8.45 however if it is a purchase right if we're making a purchase i want to put in
a different price right so brew here this one i want to put in that price of 7.49 so this is the
purchase price and it's going to be based on this purchase type so we need to differentiate between
those two so if it is an order if the order type is in the estimate order or the invoice either one
of those right sales or estimate then what we're going to be doing is we're going to be putting
the sales or estimate description into column f right that sales or estimate description is going
to come directly from our column d here d is where i want to put it directly inside column f here so
that's going to come from here i also want to take put that sales price that sales price is going
to be coming from column h it's going to go into here h and it's also going to be coming directly
from column h here right we see our sales prices at column h our purchase price is in column i so
we need to differentiate them of course for sales and for estimates and invoices we want to make
sure that sale price goes in there so it's going to come right here continuing on that's if it
isn't either one estimate what if it's a work order well if it's a work order i want to take the
work description and where's that work description that work description we're going to work for a
specific item is located in column f right f we're going to take whatever work description and we're
going to put it directly in f so that's if it's a work order also if it's a work order i want to
make sure that we're clearing the cost right i don't want to put any specific cost in column
h i want to clear whatever's there what if it's a purchase order if it's a purchase order we're
going to pull in extract that purchase description from the items database i want to put in that
purchase description coming from column e in this case column e is what's going to go into column f
and i also want to extract that purchase price as you saw and place that directly inside column
eight so that's what we're going to be doing if the order is cases of purchase order column
notice we're using variables because the user this is user defined purchase order name user
defined work order name and so on and so forth so the purchase order is located here and we will
be applying the purchase description and the purchase cost okay we're going to regardless of
the type of form we're going to set that default quantity to one and then the user can change it
so g is going to take on that default quantity just put in qty make sure okay so now all we have
that so what we're going to be doing what if it's else cleared item this is of course this is if it
contains a value this is if the user has cleared it right so if it's clear you saw what happened
right as soon as we clear it what do we want to do i want to make sure that f through h is all
cleared out and it's automatically going to clear out the total all the formula is still there so
f2h is going to clear it out also i have a save to database button if this has been previously
saved let's pull up one that has been previously oops i click email order don't want to do that
but we'll show you that in a little bit later no need to email that just yet nice okay so if
we have a previously saved order let's pull this up let's say order eight i also want to have a
database row associated with that why is that important if we take a look inside this order we
see that order one we have a database row four five six seven eight so if we pull an order one i
want to save these rows so i know what row inside the database it is important so we have that
here so if i pull up order number one i want to put those and i want to put them right here of
course they're hidden just like the others are we don't need to see that but if we were to
change it back to general we would see that four five and six are saved these are the row
numbers that have been here right so these are the database row numbers if i delete one i want
to make sure that we also clear out the row number and when we save it we'll also clear it out so we
want to clear that out in case we need that okay so j actually j through k we need that just as j
okay but actuality if it contains a database row we should also clear that database row out as well
so that's important so let's write that up right now right we got to make sure that it gets deleted
it should get deleted so if right let's check on that j contains a value if column j contains a
value we want to make sure so how do we know that we don't need this clear content it's j right j in
the target row clear contents we want to clear it but i don't want to clear it just yet what i want
to do is i want to clear the database row first so if let's double check this range j contains a
value dot value does not equal empty right then we know it's been saved in the database then i
want to make sure to clear that out that's super important then what are we going to do we're going
to do a few things right i want to make sure to clear it out in the database so we're going to set
the database row so we're going to dimension the let's say item database row as long okay in case
we have it i don't think we've used it here let's see up item nope we haven't set it as a variable
so once we do that then i'll just want to do this inside the item database order items database
dot range okay what does that range we need the item database we're going to put that we're going
to define that just an item database row and the item database row dot entire row dot delete we
need to define it though so we'll do that right now so we're going to delete that entire row and
add the and sign there and then what we'll do is that what is that it's going to be this one right
here we're going to set that into a variable here and that is going to be the item database row
item database row equals here that value good so we've got that set up so now what it's
going to do is we're going to delete that item directly inside the database if it exists
right that means it's been previously saved so let's give that a try we know it's
been saved i decide i want to save this let's refresh that order once again load it up
because we haven't deleted it it's here right so now when i decide to delete it and i want
to save and update that order it is now going to be automatically deleted so when i load that
up again you see we only have those two items perfect that's exactly what i want we're going
to delete the item from the database only if it exists of course if it doesn't exist if we haven't
yet added to the database we don't need right so now it doesn't have a database rule associated
with that so that's great so continuing on so we've got that here we set the default quantity
we've cleared out the database if it exists here and now what we're going to do is that's it
that's all we need to do is simply add items but what about when on a name change i want to set the
address right if i make a change to this name and it contains an address i want to make sure that
that address the city the state and the zip all get put into the following lines so we're going to
do that with this line of code user makes a change to f5 and i want to make sure that f5 is not empty
then what we're going to be doing i also want to make sure that b8 is not fb remember b8 is the row
that's associated with that it's like the name row if it does contain a row if we know the row then
we can then extract the address and extract the cd and state directly from that row from our
names database right here because we know that that address is going to come from d the city the
state and the zip are going to come from e f and g respectively so we can put those in accordingly
so we do that with the following lines of code we're going to get that name row we're going to
extract that from b8 i'm going to take whatever's in f d remember which is our address and place
that directly in f6 and then what i'm going to do is i'm going to place the city coming from column
e in our names the state coming from column f and the zip code coming from column g and place
them all directly inside f7 separating them by spaces and a comma here that's it that's all we
have to do to simply automate the address into line rows f6 here and f7 pretty simple there it's
assuming that we do have row okay continuing on there's another change i want to focus on here
inside d8 notice when we load it when we could change the da and we search for a particular order
we can load that order based on some change events to d8 so that is what's coming up next on a
search change order when we make a change to d8 and we want to make sure the d8 doesn't contain
empty then what i want to do is i want to check the first thing what i want to do is i want
to search it based on a row if we take a look at this one we have the search id row here so if
i were to stop this code right here let's take a look and then what we're going to do is we're
going to make a change to here enter 1 here and we see that inside the code we automatically
have a search row this search row is based on a match based on d8 we're ordering i know that we
have the order search row this should probably be adding three but it's okay either one is okay
plus three that's the correct row of the order but it works either way i know that it because
it doesn't contain empty that it is a correct search order number right we know it's the correct
order number because it contains a value here in b4 right so we're going to base it on the match
so assuming that it is correct then we know we can take this order id and i can place it directly
inside our located b5 i can take that id and put it in b5 and that's just what we're going to do
in the direction code if b4 doesn't equal empty if it is empty please make sure to enter correct
order id right if it's incorrect right we can do that but if it's not empty what do we
want to do i'm going to take whatever is inside d8 that order number i'm going to place
it directly inside b5 taking whatever order id the user has put in here and putting it directly
inside here so that way any order id they enter is automatically go inside here b5 once they do
that it's automatically going to generate a row using the match formula it's going to generate
that row and then what we can do is we can then load the order so once we generate that row we
can run a macro called order load and we'll go over that in just a moment but i want to go over
the remainder of the macros inside this on sheet now notice we also made some changes remember here
correctly when i made a change to the order date or when i made a change to our terms we want
that due date for both invoices and purchase orders automatically to update based on that so
that's simply going to be a change based on either on i4 or change based on i6 and certainly not
on the order load so the changes when a user makes change but not when we're actually loading
in the order so not those types of changes okay so we want to differentiate between different
types of changes to make sure that we're loading the proper order okay so how do we make
the changes so if the user makes a change to i4 or i6 and we know that the order is not being loaded
because b3 is false we then want to continue so i want to also make sure that we have values i4
meaning the order date i6 meaning the terms i want to make sure that those aren't empty if either
one of those are empty we can exit the sub out i want to get the term row this is very important
because i want to extract inside the admin i want to know what row this term is what row is it row
anywhere 15 16 or not all the way through 19.
Once i know the row i can extract the number of days
once i know the number of days i can then take the number of days add it to the purchase order add it
to the date here and then we can automatically get the due date so we know that if we simply change
this to 30 it's automatically gonna extract that 30 is going to be found looking on row 17. it's
going to find this and we're going to be able to simply add that to the order date to to get our
due date so that's exactly what we're going to do inside the code we're going to determine the term
rows long we're going to get that term row what we're going to do is we're going to look for that
based on whatever is in i6 value this is our terms we're going to look for it inside our named range
called terms and we're going to extract the row if we've found it correctly we know
the term row is not going to be 0. i7 which is our due date is simply going to be our
our order date plus whatever was found on column m and the term row column m in the term row of our
admin screen right here column m and the turn row is going to extract whatever number of days there
and we can use that to simply calculate our due date very very easily great so that is all we have
to do for our particular worksheet change we just went over all the worksheet change events located
in there we only have the selection change a few ones we've got two pop-ups here and i just want
to make sure this pop-up and this pop-up i want to make sure that if the user selects anything
that those disappear right these two pop-ups this one is going to be called add new group
this one is going to be called copy2group so i want to make sure that both the add new group
and the copy 2 group are not visible when the user makes a selection if they are visible we
should hide them on selection change so if add new group is visible when user makes any kind of
selection change then hide it and the same thing with the copy 2 group if it's visible hide it so
that's all we're doing with the selection change so let's take a look at some of the order sheet
macros i've got just a few of them here on this order sheet and then next we'll get into the order
macros now remember we had add an edit name we had this on transactions it was very simple this
is the macro that we're going to be doing when i want to add and edit that name right so it is
exactly the same but just with a few differences inside that so it's automatically going to load
up that name and of course if it doesn't exist right if if it doesn't exist let's say we have a
new order let's say add a new estimate and we put in a new name right that doesn't exist i want to
make sure that we're loading that when we load up we load that up no it doesn't match restriction
to find the cell that's okay and let's update that i do want to update that data validation
actually i'm going to update that through the code because i want to make sure that the code takes
on notice that we did make that change but it is the code that does it so i'm going to go over
that so basically the code automatically added the restrictions if we take a look inside the adder
restrictions we updated that but i want to make sure that that update that error alert inside the
code is unselected i'm going to show you exactly how to do that so that the error alert is simply
just a warning and or simply allow and not the error alert okay so that's going to allow us to
enter anything we want because i want to make sure the user can enter anything we want if they want
to do that and how do they do that well inside the code right we don't need that right so we want
to update the validation so if we go into the data right in advance we need to make sure that
the error alert this is what we want click ok now we can do that but we want to do that inside the
code why is that important because the code that's going to change this data validation it's going to
update that from customers to vendors because it's going to be on a purchase order so i'll show you
how to do that because once we do that we can then add james right so now james has been added to the
list and i'll show you how to do that inside the code so that's we're going to get into right
now but i want to make sure that when we edit either visit a new name or an existing name if we
remember correctly both 8 and 9 is going to let us know that right so if we enter a brand new name we
know that b8 is going to be empty and that's going to help us out so with this when we click on that
button right here this is the macro that's going to run let's add an edit name we're going to set
the name field to control the name row is long just like we did before we're going to clear out
that entire form that form all the fields in that formula to be cleared out we're going to focus on
the orders again if b8 here is empty we know it's a new name we're going to do exactly the same
thing we're going to set whatever's in f5 this time set it to that new name we're going to set
the customer name here that customer name is going to be set to the type value right i want to know
its customer here set to fall to customer or event actually should say customer here customer name
probably should be the customer or vendor right if it's a purchase order we should send it to
customer if it's a vendor let's do that huh shall we if right dot range b2 dot value equals right
what is that customer order we'll set that up right i want to know if it's an invoice order
invoice order then we know it's an invoice then we want to set a customer else we want to set it to
vendor then vendor so then right customer else and then end if okay so how we do it so what i want to
do is i want to set the default name if it's a new name probably most likely user can change it right
so either to customer if it's an invoice or vendor if it is a purchase order so this is else purchase
so that's going to be here purchase order purchase right probably purchase order so i would say
i would let's change this to purchase order i'm going to change this because really only
purchase orders use that so purchase order i'm going to check that name range to make sure that
we got it right so if we go into the formulas name range i want to check to make sure that it is
the purchase order here this is the one i want to know purchase order here purchase order if it's
a purchase order change it to vendor everything else change it to customer that's going to be
sufficient because both work orders and invoices and estimates all use churches all use our
customers while purchase orders use the vendors so we're going to change this up if it's a purchase
order then we're going to set this to vendor name right otherwise customer name vendor otherwise in
every other instance use customer that's going to be much better so that way we have automatic
default and we're just going to change this to vendor good okay i like that and i'll show you how
that's going to work and why that's so important all other orders and then this is only going to
be for our purchase orders purchase good here's why i like it so now we enter let's say a brand
new let's say invoice right and i decide i want to enter something new right if it doesn't exist
okay no problem i want to add a customer right so we figure it's a customer if it's on an invoice
but what if we're on a purchase order if we're on a purchase order we decide to add something
brand new right then what i want to do is i want to make sure the type is vendor right default it
to vendors so that's all we did is differentiate the default to that vendor so i think that's
kind of a nice addition and that's exactly what we did here so we just differentiated
we're setting the default here okay what i want to do is i want to set the name bro if
it's an existing name this is existing name i want to set the default name row to ba so you
want to set the name to ba then again we're going to do exactly the same as we did just run that
loop setting the name field as we've been over set that name field to those controls starting
from name one three minus two starting at one going all the way to nine and then just setting
the values based on whatever's in there and then saving that and we're just going to simply load
in whatever is currently in the database here in the names database loading it into that form using
that loop then all we're going to do is just going to update the name type to our name type field and
of course the status field we're going to update from column j and then we're going to display the
form so basically this loads all of the data from whatever's in the database up into that form and
that way if we have a brand new one we can load it's going to load the name in however if we have
an existing one it's always going to load in that name here so that's exactly how we're going to
do that all right great so we see how that loads it in directly from the order form and now what
we can do is move on into adding the new menu remember when we selected this here it has a new
menu so that's very very easily we have all we have to do with this macro is simply display this
particular menu right here i want to display this sub menu which is just a group of four different
shapes all together grouped together called add new group so it is that add new group that i want
to display when we select this button here so when we click the order add new menu all we're going
to be doing and it's going to be a toggle and that what does that mean the toggle helps because if i
click it again and again again it's going to hide or show or hide or show and that's exactly what i
want so what we have to do is we have to check is it currently visible if it is hide it if it's not
show it and that's all we're going to do inside the code if the order shapes add new group is
visible equals true then hide it otherwise show it that's it and now what i want to do is i want to
do the same thing with copy to menu now if i have an existing let's go ahead and pull up an existing
order and i want to create a brand new one we're going to use copy2 maybe i've got a purchase order
and i want to copy it over to an invoice so i can do that just with this one line of code copy 2 is
almost the same we have a same group but this is called copy2group and again it's just another
toggle how we're going to do it so that's all we have to do inside the code here is simply just
check to see if the copy to group is visible if it is hide it if it's not show it that's it that's
all we have to do so just a few macros on this okay now we're going to get into the order macros
we have the order macros this is how we're going to create these new orders first thing we're
going to do all these variables i'll be going over as we go through the code we have order type as
a string i'm going to set that as the order type this is going to be the save or update this is the
order that's going to actually save it's the order tied to this button and i want to save or update
it if it is in a brand new order new estimate or new order we know that our order row is going to
be empty located in b6 because there's so if it is not it is going to be an existing one right if
it's an existing one we know that it's going to automatically have a row located in b6 so that's
going to be our differentiator but the first thing we want to do is we want to make sure that we do
have a vendor a customer located in f5 that is a required fields if it is not we want to let the
user know to please save it now i like a nice dynamic right if we add a new one and i want to
know are we going to be adding it so if i try to save this it says please be sure to add a customer
before saving this estimate i like that because it's dynamic and if we take a look at the purchase
order and i try to save this it's going to say please be sure to add a vendor before saving this
purchase order so notice how vendor can switch to customer switch to vendor and purchase order
switched to estimate switch to purchase order because we're using dynamic variables inside that
so meaning if it's missing a if it's missing a day if she's missing that name we're going to show
please be sure to add whatever's in f5 e5 and what does that mean if i was going to be either
the vendor or the customer that is the name based on here right it's based on a formula so please
add a vendor or customer based on whatever's in e5 before saving this whatever it is in b2 which
is our order type right here before saving this order type also i want to make sure that
they've added a date that date located here in i4 is very important we want to have an order date
whether it's a work order purchase order invoice or estimate we want to make sure that it contains
a date so if i4 is empty please sure to add an order date before saving this order type whatever
type it is so we don't need a type number that is unnecessary if it's in we need to different just
as i mentioned before is it a new order meaning b6 is empty or it's an existing order if it's the new
order we're doing just like we did before in items and also names we're going to get a row being
the first available row in the orders database we're setting the next order id based on the max
so we're simply taking the next word id and we're going to place it directly inside b5 here and then
also what we're going to do is we're going to take that same order id and placing it in the first
available row inside column a inside the orders database however if it's an existing order all we
need to do is extract that order row from b6 and we want to set some variables i want to enter the
order id into a variable i want to know the order date inside a variable i wonder the order status
also the status is very important here the status is going to come directly from d10 so i want to
put that inside a variable and i want to know the account that's associated in d11 that account is
important for both purchase orders and invoices in d11 you could probably require this as a required
field as well located in d11 and then what we're going to use is we're going to use data mapping
all i'm going to do is again take the information whatever's in these fields here and map them
to the directly to order database that means b2 or i4 is going to take on that order date here
d10 f5 are going to go inside these columns we know whatever's in i5 is going to appear in the
employee column i6 and so on and all the way through column l and we do that here for the order
column equals 2 to 12.
We're simply updating the orders database based on the order row and order
column whatever is located in those ranges those ranges located in row one so we're going to save
the data to the database now what we want to do is we want to update the items right we've saved
all the information inside the order here all this information but what about the items we need to
make sure that when we've added items we want to make sure that those items get saved as well and
we don't know if they've been saved previously or not but we're going to look inside column
j if they have not been saved if there's no database row associated we will assign a brand
new row if they have previously been signed we can use the existing row it's hidden here using
the three semicolons but we know it is there so we're going to do going to check on that first but
first we need to determine the last row of the i items based on e34 and excel up row that's going
to give us the last slider mode so that we can loop through all of the items as we loop through
all the items in this case the last item is row 12 so we'll loop from 10 to 12 and all the items
if the last item row is less than 10 then go to no items right for the item row equals 10
to the last item row we're going to check column j is there a database row that's already
associated with it if it's not empty we have an existing database row we're going to assign a
variable order item database row that's a long variable based on whatever's in j if it has not
yet been saved then we need to create a brand associated brand new order we're going to look
at the first available or row inside of course our ordinance database plus one that's the first
available row our order items database is located right here so inside this database right i want
to find the first available which is 70 right inside that we want to add the order id the
order type the date the status the account then the item information total in the rows i want
to add all that information inside the order items database and so we can do that here so we're going
to set the row if it's a brand new one right we're putting in the order id i'm putting in the order
item row located in k right k is going to take on that order more k is going to take on the row
of the item 10 11 12.
Remember it's going to here this row 10 11 12 whatever row we're on we're
going to also want in the particular formula this row format to take on the database row so we're
going to have to set that up in column l and we're going to do that right here column l it's going
to take on this column l is going to take on this formula right here then also if it is brand new i
want to set that order added item database inside j so that we know that it's been already saved
okay everything else we're going to do regardless if it is the new item or the existing we're going
to inside b we're going to put the order type the order date the status and the account i'm going
to put in those respectively bc d and e columns respectively then also in a single line of code
all the way from f through j we're going to take on here item description quantity and amount and
the total all the way is going to come directly from our orders all the way here from e through i
and bring in all those information over here great so we've brought in those information that's
it that all we have to do is loop through the item and then just run our fade out message saved
message and this is the same type of faded message that we've gone through before so we don't need
to go again so when we go ahead and save those orders it assigns those rows they're not hidden
now we've previously hidden them just remember home general custom three semicolons we'll
keep them visible for the training right but three semicolons will hide them right here so we
we've added that up and of course if we decide to add a brand new item to that right we know that
it hasn't been assigned a row so we would have to assign a new row as soon as we save it it's
going to assign that new row to that that's it for how we save the orders but what about a new
order new orders are a little bit more tricky because we don't know what type of order adding a
new estimate adding a new work order adding a new invoice or adding a new purchase order and it gets
even a little bit more tricky because i decided to add more features maybe from our main menu here
i want to add a work order or maybe from our main menu i want to add a purchase order so i also add
an estimate so how do we know just based on that i want to use a single macro for this so if we look
here we've tied a macro if we can that's going to be the same macro so if we click n here we see
i'll bring it up we see it is this called order new macro so it's the same order it's the same
macro whether i click here or when i go into the orders and then we start a macro it is the same
if i click on one of the individual buttons here click assign macro here we see it is the same
macro order new so it is that macro so inside this macro we're going to need to differentiate between
where they are okay so if they're on the home screen i want to do this i want to set them order
types right if else from the order screen we're going to do some other things okay so based on
where they are being sent from we're going to make some changes okay so the first thing if they're
inside the home screen i've given these particular names right this is called estimate this button
if they click it this button name is called work order work underscore this is called invoice and
this one is called purchase order purchase order so if they click this it's the same macro but the
name of the shape that they click is different based on the name of the shape that they click we
know to add what shape which order to add so we can use that here so if the active sheet is home
we're going to access from the home screen the order type is going to be whatever the name of the
shape that called it but i really don't want those underscores so i want to remove those underscores
and replace them with shapes so we know that our order type is going to be invoice estimate right
and however but those are not the dynamic names right those are the hard-coded names those are the
these are these names what if they've changed it to proposal right i need to make sure that that
proposal right is the one that goes in here right so that's very important so how do we extract that
well first of all what i want to do is i want to get the row we know if it's estimate i want to
look for the word estimate from b8 to b5 and what does that mean inside the admin i want to look
for this estimate if it's found on row 5 i want to return whatever's in here so that way if i change
this to proposal and we go into the home screen and i this change deposit when i click here
i want to make sure that proposal goes here not the word estimate that type should
change to proposal very very important so inside that it's also important that we update
that so what i'm going to do is i'm going to look for order type which is estimate let's take a
look i'm going to pause this right here we're going to go into the home screen and i'm going
to click this okay i want order type is estimate right because that's the name of the application
caller the shape name of the shape that called it so what i'm going to do is i'm going to look from
b5 to b8 i'm going to look for estimate i'm going to find what row it's been found on it's been
found on row 5.
Right if we take a look inside is we see that it's found on row 5. so if i know
it's founder row 5 i want to put in the type whatever is located in c and row five so as we
continue the order type now becomes whatever's in c and row five so the order type if we move
through this a little bit we see the order type now becomes proposal we've updated that i also
want the status type the status type is going to be based on whatever the application caller is
in this case estimate or edit so that's fine the status type why is that important the status said
because we're going to have to set some status right we need to differentiate our status here
is different right we have different a different dynamic data validation based on whatever
type it is remember that remember that here inside the admin we have this proposal we
have this so we have all different types so i need to know which one to use that's going to be
based on that i will change this back to estimate and then we'll click it again to make sure that we
have everything's functioning properly clicking on here it's going to add it okay let's continue
on with the code let's reset the code here and then we'll undo this we don't need to stop
anymore continuing on with our home page and going to a brand new estimate i really like the dynamic
nature of this it's very flexible so what we can then have is continuing on with the code so we're
going to activate the the order screen right we want to activate it we don't want to activate the
home screen we're going to set the status what if it's from the order screen well if it's from the
order screen a few different ones if i click here we see that we've got two things i've got estimate
and estimate i've got two different things now this is going to be interesting here we've got
two different values here that we can extract one is the name of the shape okay and two is the text
within the shape so let's go ahead and change that again oops that's okay let's go i want to change
it one more time and i want to show you again now how we can propose all okay so we add a proposal
now and now we're going to go back into the orders now when i click add new you see proposal is
here so if i've changed the text here proposal i can take this text which is the estimate or
we're using that named range right estimate order to change the text here i'm going to use that
text and i'm going to place it directly inside the order type and i'm going to use the name of the
shape estimate as our as our status type because we'll all go into the status type and why that's
important okay i know it's a little bit confusing but it's valuable so when i click proposal i
know to take whatever's the text of the shape the text right the text inside the shape and put
it here and that's exactly what we're going to do inside here the order type is simply the text of
that shape the text right the text of the shape the status type is the name of the button right
where remember every shape can hold two data values one that's what i like shapes are so great
because they can hold a lot of information it can hold whatever's in the text can hold one whatever
the name of the shape can hold another and here we're extracting two different values from the
same shape however there's one other thing when we delete an order when i delete it i will always
want to make it automatically go to this so let's go ahead and there's something else here when we
delete an order i want it to set a default to new invoices so let's go ahead and just create a brand
new order here just a simple order some estimate and then we're going to delete it and then i'll
show you so i'm going to save this order and then what i'm going to do is i'm going to delete it i
should want to delete it and then i want to set the default this process has been deleted and i
want to set the default to go back to invoice when we delete an invoice we also create a brand new
one so if the user has deleted it where do where should we set the default what should it go back
to i just say let's go back to invoice and show clear invoice so that's all we're going to do if
the order type is delete order it would be delete order because because the name of the button is
called delete order the shape of the name that they've used is delete order we're setting that up
here then what we're going to be doing is setting the order type equal to the invoice order and
the status type to invoice so basically we're just setting the default it's invoice if they've
deleted the order the order type would be delete order when we delete it okay all right great so
continuing on what we want to do is i'm going to set that order type right b2 is going to take on
whatever order type you saw that automatically b2 updated automatically as we add it inside that and
then what we're going to be doing is i want to set the order type number now i want to set a number
based on their order type basically one through four and we can simply use the match based on the
order types the order types is the named range based on this it's a dynamic actually it's not
dynamic it's just a named range called order types based on these four possible so as they change
we're simply going to look for proposal and we're going to find it and we're going to return one
two three or four and we're going to have that that's going to also help us inside that so back
into the orders we go i'm just going to return the one continuing on with our code so we're going to
set that order type into a variable long variable it's going to set that order number b3 i'm going
to set this to load right i'm going to set it to load and then by the end we're going to set it to
false i'm going to clear the contents of all the order fields just using clear contents i'm going
to set the current data's default in i4 and i want to set the next order id it's a brand new order so
whatever that next order id using the max formula here we're going to set that up into b5 so b5 is
going to take on whatever's in b7 and that's going to set the next one i also want to basically hide
that add new grip remember when i select something a new work order i want that group of shapes to
be hidden so that's going to be hidden right there then what i want to do is i want to clear the
validation in three different cells d11 d10 and f5 so let's take a look at that let's take a
look inside an invoice here okay so d10 is our status d11 is our income account and then we have
our either customer vendor here so those are all validations but they're dynamic based on the
form that's used right because we know that this is going to be a status if we take a look
at this status and we go into the validation here we look at the data validation we see that this
is called status invoice status invoice so however if we create a brand new one let's do purchase
order and we take a look at this status here we go into the data validation here we see that
this is status purchase order so what i want to do is i want to make sure it's dynamic but before
we update or change any validation here here here we must always first delete any validation
so that's the first thing we're going to do all three of these they get their validation
deleted okay and now what i want to do is i want to add i want to add a brand new i want to
set that status type this is where that status type is very important remember that status type
is either invoice it is purchase underscore order work underscore order or it is estimate right
and that's going to go in here so that's going to be the status and the status type why is that
important because look in the formulas and name manager let's click on status right we have status
estimate status invoice status purchase order and status work order so those all pertain to the
individual statuses here in our admin screen so i want to set the data validation based on those
so that's very important that we do that so we're going to set it so this way we can dynamically
place the status the formula of that's going to change automatically if there's an issue we'll
wrap it around on there okay great so now what we need to do is we need to add additional
validations right we've added the status here no matter what it is the status is going to change
no matter what now i want to add a validation income or expense accounts here i want to add a
validation vendor or customer here so we can do that with the following based on the order type
so we're going to use select case based on that order type if the order type is an estimate i want
to make sure that that customer name validation gets added to f5 also if it's a work order i also
want that customer name added i guess we could add it here and here if it's in in other words we
could use a comma here and then work order that would probably be okay too so we could combine
these two that would be pretty easy but if we want to add additional things to estimate our
work order okay but if it's an invoice order i want to do a few things f5 of course is going to
take on the customer d11 is going to take on our account income remember i want the income accounts
associated with this we create a brand new invoice i want d11 to take on income accounts only we have
income accounts we have accounts income accounts accounts income here and we count expenses expense
is going to be for purchase orders and income is going to be for invoices okay so we have both of
those here both expenses and income here okay all right so expenses was here okay all right so
we have both of those but i want to make that update so we know that if it's an invoice right
our validation is going to be formula accounts income however oh one more other thing i want to
set the default income account remember if we we have sales income that income came from a direct
location that came right from here it's called our income invoice income account named range located
here in g11 i want to set this as our default account if i were to save it change it to other
income and then if we create a brand new invoice here and it's automatically going to change to
other income as i've already demonstrated for you previously so i want to make sure that that gets
done and that gets done directly inside here so we're going to place that right here invoice this
named range invoice income account is going to set the vault into 11.
Okay great well that's all if
it's an invoice but what if it's a purchase order if it's a purchase order we want to add the vendor
name right we're going to be pulling down vendors so we know that when we added or a brand new
purchase order i want that drop-down list to be a list of vendors so if we take a look in the data
validation here we see that it is vendor name as opposed to an invoice where it would be customer
name so that's what we're differentiating invoice work order or estimate would be customer name
so you see the difference in the data validation that happens directly through here
this formula gets added based on that okay in d11 we want to take the account expenses
remember it's expensive counselors associated with purchasers so we want to give the user the option
to select which expense accounts but we do want to set the default expense accounts as to whatever we
added directly inside here called purchase expense account so whatever purchases are that is the one
that we're going to be using inside those orders for purchase orders so that's the one that we want
to set for purchase orders that material purchase is going to show up directly inside d11 right
here great so that's it that's all we need for the validation that's updater validation now we just
need to update default payment terms and purchase orders on invoice and per servers remember invoice
and purchase orders we've set a default payment term right it's net 15 but if i want to change
that i can change that to net 30.
I want to make sure that we look for that default so that when
we create a brand new invoice or a brand new purchase order that default gets set automatically
right here into i6 so how are we going to do that well what we're going to be doing is i want to
determine what row is that status what i want to do is i want to look for that little check
mark inside our status here we've got a named range called here actually i don't think from d
i'm not sure i don't think we have a name rich n15 through n21 i want to look for this check mark
now that check mark inside vba or character code is called character 252. so i'm going to look for
character 252 how do we know that it's character 252 well if we insert right a symbol and we want
to insert a symbol here we can look for that check mark here and we see that it is character number
250 oops and we see that as character number 252 okay so we know that it's character 252 from
our character there so what we're going to do is we're going to look for that so here i do have
default stats or status default and status type what we're going to be doing oh sorry this is for
the default status sorry we're going to get to the default oh here we go default payment returns
i almost skipped it default payment terms here then we're going to focus on status then footer
message okay so again as i said that named range and 15 and 21 i'm going to look for something i'm
going to look for that character 252 that's that little checkbox and i want to extract the row from
it and i want to put that row inside this variable called term row if it is found meaning it is not 0
all i want to do inside i6 is place whatever is in l in the term row what does that mean that
means we found where there's a check mark we found that it is on row let's say it's row
uh here 17.
I want to return whatever's in l and whatever's been returned whatever that row
that it's been found it's been found on row 17 so i'm going to return whatever's in there l okay
so that's all we need to do and we're returning that directly inside here and that's going to
place directly inside i6 great so we have that set up and that's all we do now all we have to do
is default status based on the order type remember we've got some default statuses too pending
remember we showed you that purchasers have pending whereas work orders may have unassigned so
we have default status we've set that up directly in here our default status is here here or here so
our default status is whatever we've checked again we're going to look for the character but we want
to look for it in a specific column now how are we going to find that well what we're going to do is
we're going to find it in here default status and the status type status default i've created named
ranges for this let's take a look inside that formulas name manager and it's going to be called
status default estimate here we've created one so it's here actually this should be one more row
probably and then what we're going to have is status default invoice here so here oh it's
dynamic that's good i like that there so it's dynamic based on that which is perfect so what i
want to do is i've created a named range called status default and then whatever it is so what
i'm going to do is look for that look in that named range status default look for the row that
has been found and then return whatever's in the previous row so how are we going to do that well
we're going to set the row we're looking for that character we're looking at for we know the status
type is estimate purchase order invoice or work order so this completes the named range we're
going to look for that checkbox inside that we're going to extract the row if we have the row
then all we need to do in d10 is going to go admin default status row right and the add we know
the row but what is the column the column is based on whatever column our range is here what
is the range of that status in the status type this is our range where it's found i want to
extract the column where that range is found so our status could be status invoice status
purchase order i want to return the column that that range is found out if i know the column here
and i know the row i can return exactly the status that i want so that d10 is going to place the
default status based on whether whatever the user has set up directly inside here so we know that
if we decide to set this to ordered on brand new purchase orders and we create a brand new purchase
order we know that that default's going to be set to ordered great but we also have footer messages
the last default that we need to set up right we create a we have default footer messages i know
it's comprehensive i'm telling you i hope you have more than one coffee so i've got our defaults set
up here here here and here so we can do based on a column so let's take a look at what column number
that call number is going to help us we know that estimates are going to appear in column 18 19 20
and 21 okay so we know based on estimate workers so if i know here's where it's going to come in
handy that number if i know that our default is in column 18 and i also know that if it's an estimate
here proposal estimate same thing that this is order type number one i know what column to look
for it in so that's going to help us right because i know it's a column so our default footer message
row what row are we going to have it on right i want to know i want to extract i want to know
where the check mark is right for the estimate this is our estimate so if i know the column i
want to know what row is the check mark i need to know what row to put this where to find the
check mark is this our default estimate message or is this our default so having that in there
it's going to put that in here so we continue on so we're going to look at that so it's going to
be based on here our admin we're going to look through a range and where's the first row the
first row is 28 that four looking in first row is 28 what is our last or our last row is 39 now
what column it's going to be column 17 plus our order type if our order type is 1 meaning estimate
it's going to be here if our order type is two work order three invoice figures so our order
number is going to help us determine what column so we know the first row but what is our order
type remember this is one two three four plus seventeen is going to be our column so that means
the estimates would be on column 18 and so on so i know that so this is going to develop our range
this is a dynamic range so our order type in all the way to look so we're looking from row 28 to
row 39 inside column 18 19 20 or 21 and what are we looking for we're looking for that character
252 and when it's found we're going to extract the row of it that row is going to tell us exactly
what that footer message should be as long as that row is not zero then d9 is simply equal to admin
f and the default footer message how do we know that we've extracted the row we're going to take
a look inside f we've got the row and i'm going to place whatever has been found in that here and i'm
going to place it directly inside here our default footer message located in d9 and that sets up our
default footer message great now also we have copy 2 this is almost exactly the same and copy cue
is a great little feature because it allows us to quickly and easily copy whatever we have if we
have a work order let's say we've completed the work order and we want to now create an invoice
based on this all we need to do is just click here and it's going to create an invoice based on that
we can just update the totals if we want to very very quickly it can automatically copy over or
maybe we want to take an estimate or an invoice and automatically create if we've created let's
find an estimate here i think or maybe purchase order there we go so let's say we have this
estimate we want to also create an invoice based on this estimate all we need to do is use
copy2 and it's going to automatically create an invoice based on that so i like that ability to
have that and so how are we going to do that well of course we're going to use mostly what we have
we're going to set some very specific just as we did before we have some very specific names in
here estimate here we're keep remember this stays the same as our estimate this the text updates to
whatever the user has changed it to so notice that the name of the button is still estimate but
the text is proposal that's going to help us out tremendously so the order type the order type is
based on the text inside the write the shape in this case proposal the status type is based on the
name of the button which is estimate so the status type stays the same it's not dynamic however
the order type does b2 is going to take on the order type just as we did before the order type
number is going to stay b1 just like it did before everything else is the same really all we need to
do however there's a little bit different we're going to hide that copy to group right so we're
setting the order load false doing everything we have and then what we want to do is we want to
make sure that we uh setting the order load to true it's good enough as it is i like it the way
it is okay so there's not too much difference here so again we're taking we once we have extracted
the order type and the order says everything else stays the same everything else stays the same
however there's a few differentiates we're not when we clear out the information remember we're
clearing the contents there's a big difference here clearing the contents here and clearing
the contents here notice there's a lot more fields when we're adding new and we're copying to
right why is that because when i create a brand new one i want to clear out all the fields however
for copying 2 i want to clear out only specific fields i want to keep all of these fields here
but i do want to clear out any database rows associated with that i also want to clear out the
order id actually order id when we copy two should be cleared out but notice the order oh actually
that's fine we're taking the next order id we're placing it here the next door id has not been
entered in the database which is fine so our order row is empty and this tells excel make it a new
order so as soon as we save this a new row gets assigned we have new database rows assigned so
when we copy to we're not clearing out everything we're only clearing out things that are associated
with the database we're clearing out the order id we're updating this to the next order id so that's
why we're clearing out just more limited fields in our copy to here limited fields not in the we
certainly don't want to clear out all the data inside we don't want to clear all the items so
that's very important so that's it for copy two everything else is the same because once we
copy everything to when the user clicks save it assigns a brand new id it assigns a brand new
row and all new database rows so that that way it gets assigned a new record very very easily to do
that that's copy to what about order load remember order load that's what happens when we click one
or two or three or whatever we're clicking here we automatically enter that it's going to
automatically enter so that's going to be on order load when we order load we need to know the search
order we need to know make sure that b4 contains a value we went over that just briefly b4 is going
to be based on the order id of whatever is located in d8 the user will enter an order id here it will
determine the row of that order if it's not empty we know it is an accurate ordered number we can
then take that order number and place it directly inside b5 and that's what we're going to do here
so we certainly want to make sure if b6 is empty we know it's incorrect b6 of course here is our
order row we want to make sure that however when we make that change we do that all in the change
event orders we went over that already remember on d8 change we're taking care of that we're placing
that that order row here then we're running the macro order load so we went over that already
now we're ready just to make sure that b6 doesn't contain it must contain a row must contain a row
associated with that if it is empty we got to let the user know otherwise we're going to extract
that and put that into a variable here order row i want to set the order load to true as we clear
out all the contents of the fields to making sure that everything gets cleared out and we're
going to turn off application screen updating we're going to again load all the information
using data mapping right just as we did before bringing in all the data from the database because
we have an order column and we have an order row so we can bring in all the data we're setting the
order type into a variable and we're also going to delete the validation just like we did before and
we're going to update all remember also on order right also we load order i want to make sure that
those are income accounts i want to make sure that the status here are all the statuses for those
for only for invoices and i want to make sure that only this is associated with a customer right and
not vendors because when we load a purchase order things will be very different right so or work
order things are going to be different okay so we want to make sure to do that okay because purchase
orders of course are going to have vendors and different accounts so we want to make sure just
like we did before deleting the validation and using select case to update all of the validation
except we're not putting any defaults in here because it's not a new one great so we're going to
set the status type we're also setting the status type here and then what we're going to do is
we're simply going to update the status type just as we did here now what we have we have additional
loading items right we want to load all the items i need to load all the items associated with this
order all the items associated with order 5 and that's going to come directly from our order items
database here so what we want to do is create a filter based on some criteria now that criteria is
going to based on order five right we know inside the orders b5 is going to take our order id number
so we can use this formula to create a criteria then we only want to extract those items with
those specific uh on order five and i want to get the item description quantity amount row order
row and row bring all those in here and we do that with an advanced filter okay so first of all we
need to know the last item of the order the last order item database i wonder the last item here
right because we're going to create an advanced filter so we need to know the last order item is
80.
So once we do that we're going to put that into a variable if it's less than four that means
we don't have any items we're going to set that advanced filter based on the order items database
a3 through l so we're going to set all the way to l right we're going to use criteria n2 through
n3 and we're going to have those results appear directly from r3 r2 through w2 so bringing those
results in having our criteria and 2 then 3 and those results from r2 through w2 making sure
that that's correct r2 to w3 good then what we want to do is we want to determine the last
row of results using column r right so that last results row based on column r of that database
and if it's less than three that means we have no database items we're going to skip and go to here
if we do have results we're going to loop through those results from three to the last results row
we want to set that item row based on the order items database v that order item row that order in
row is very important because we need to know what row to place it row 10 row 11 church of where are
we going to place the values here here here okay so we need to know that okay continuing on what
i want to do is i want to take all the names item name description quantity and amount and i want
to place them directly so we can use that so e through h is simply equal to here r through u so
that's all we do through this line of code rfu and then i want to add that database in that
database here that database row must go into column j here normally it's hidden okay so we're
going to place that there because we have to know it's coming from there these are these are hidden
right that's why these don't show up remember these are hidden right using that just so you
don't know why they're showing some this is custom more number formats they should be hidden with
these three semicolons it's going to hide them these are not hidden so okay perfect so we know
that bringing those bringing that item database roads we're going to set our order load back
to false and then we're going to turn on screen updating perfect great so we're almost done with
these orders we've done a lot so i want to be able to print so we've covered add new copy to we've
covered save and update i want to print the order that's simply all we've already done is just set
the print we know that we have a very fixed print range right we know it's going to come all the way
from here so this is our print range right here these cells so when we do place layout we can just
print set that primary it's going to be fixed it won't be changing because we have a very specific
invoice we did do a training if you want to see a few weeks ago how we can do unlimited rows for
unlimited items on a multi-page invoice that was kind of helpful so we've got a printer so all i
need to do with the macro is just simply print it out so orders print out and then just add in
these variables right we know we know what we have we remove the comment and then add it in we
can see all that it's front page two page copies preview add to printer print to file false
and then we ignore print areas false we want to make sure that we are keeping those print areas we
want to email it i do want to email you saw that briefly earlier if i want to click email i want to
email that order and then what it's going to do is going to create a pdf and then automatically email
that order so that's going to help print it out it's going to put it into a pdf and automatically
email that order as you can see down here this purchase order created inside an email so what we
want to do is i want to set that email to whoever where our vendor is or our customer i want to
set some default subject i want to create a pdf using that relatively simple so we're going to go
over that so first of all the file name i need to set a file name where are we going to save that
pdf it's going to be based on the current workbook path using this workbook path i'm going to add a
backslash and along with b2 right i want to add in what type of file right i want if we noticed in
that pdf that we just created it's going to be called had a very specific name and the name
is going to be that file name they'll afford it so is it an invoice is it a purchase order
right so when we create that pdf we see that a very specific pdf called purchase order and
then underscore five right so i want to make sure that that b2 that order type along with that
purchase order get inside that name make sure that file name so we're going to add b2 underscore
b5 that is going to give us a name and then a dot pdf if for some reason that file exists
it's going to create an error we try to create a new pdf so we're going to kill it delete that
file if it already exists we're then going to use export as pdf file type pdf and so we're going to
export it out into the pdf using that file path we can export it out right using the pdf using that
file name we do not if we want to display that pdf we would mark this to true we're going to set
that outlook application we've already allocated alec mail we're going to use late binding those
two right there have been defined as objects here so we're going to use late binding we're defining
the outlook object here outlook app as an object with the outlook application i want to create that
email that initial email select that outlook email to create item this is going to create that
email once i create that email i'm going to set that 2 to whatever's in b10 how do we know
what's in b10 b10 is the email we're going to use that i've used that i want to use either a
vendor or of course i want to base it on a work order if actually if it's a workload i'd like
to email it to the employee right so that would be kind of nice if it's a work order and we've
selected an employee i want to email it to the employee think like lisa let's say i want to lisa
so generally we'd probably email a work order to an employee however if it's a customer purchase
order or work order i want to email it to a customer or vendor so we need a little bit of a
formula to help us out if the work order type is a work order type why don't we index whatever
is located in i5 that's our employee name and i'm going to index our email we've got a name
range called emails that's for all the emails for our name range and we're going to look for it in
row based it on the match in i5 so this is going to extract the employee email however if it's
not a work order if it is a estimate invoice or a purchase order i want to email it to whoever
we've put inside b8 so in this case we're going to index email based on whatever's located in b8
minus 3 because we already have the name row in here right we've already if we set a customer here
we already have the name right here so that way we can extract if it's a proposal we know let's go
ahead and put in a customer name here that we can extract the email from your app so we can get
the email so based on the form type we're going to either the email is going to be from the employee
or from that so we're going to set that inside the two whatever is located in b10 we're going to set
that as the customer actually should put that as or employee email could be all throughout either
one of those okay we're gonna attach that file name we've already created that right we've we've
created that pdf file right here so all we need to do is add a chat and we add a dynamic subject
please see the attached we want to know if it's an invoice the order type right here right and
also we want to know the whatever's in b5 and i want to make sure that they have that number
that id number here and then nothing for the body and then we're just going to display it if
we want to send it right away we'll use dot send okay it's cleaning up i'm going to set that up
mail nothing okay the last particular macro for this particular module then we're going to be
did dashboards and i think that's going to be it delete right we want to make sure that we're
going to delete the order if b6 is empty right b6 is empty like in this case because they have not
saved it so if i decide to delete it and i say yes it's just going to simply clear the clear it out
and go back to the default order which is invoice however if it has been saved before we know that
b6 is going to contain a value we can extract an order row directly from here so we know if order
row is going to go to not saved it's going to skip all the way down here go to not save however if
it has been saved before we're going to take that order row and put that into a variable here then
what we're going to do is we're going to delete that from our orders database using order row
and order row entire row delete we're deleting it there i also want to set the order type
based on b2 because that's very important right i need to know how we're going to deal and update
that so with the order items database we also need to delete all the rows associated with that so
if i just decide to delete this i need to loop through these i need to extract get these orders
determine those database rows and extract them or run an advanced filter and do that as well so we
can do that with an advanced filter meaning i'm going to run an advanced filter i want to know all
of the rows associated with this one i want to run an advanced filter to do that inside the ordering
database so we're going to take that order id i want to know all the items associated with that so
that i can delete them but i want to delete them in reverse order meaning i want to start in row
5 and go to row 4.
I always want the highest row first so we do need to sort them so we're going
to run that advanced filter getting all the rows right determining the last row then if the last
row is less than three go to node database items okay so we don't need that however if the last row
actually probably does the last row is less than four then skip sort right because we only
if there's one item we don't need to sort it so let's do that if the last row is less than four
right because that means there's just one row of data then let's go to no sort go to no sort okay i
like that better so we'll skip the sort here then no sort okay okay so we can do that there inside
no store we don't need to sort it there based on that there's just one row if there's more than one
row we're going to run sort based on that we're going to clear any sort we're going to base it on
w3 right i want to know i want to reverse these i want the highest row first here when i delete
them i always want to delete the highest row first so sorting them based on w3 and i want to make
sure they're sorted descending the highest first we're going to base it the range entire range sort
that entire range from r3 to w it's going to sort all this entire range once we have that then i can
then delete the rows the result row equals 3 to last results row i'm going to set that order item
row based on whatever is located in w then i know what row to delete so we're going to set set this
delete row we're going to delete that entire row very easily enough so now we have that inside
there we probably don't need this right here because we're already set on that one good so now
continuing on so now all we need to do is just let the user know that it's been saved and run a
brand new order that's it so that's all we need so if i decide i'm going to delete this order right
here we can do that right now inside the orders so all we need to do is just let's say enter one
of the older orders that we just created and then delete the order and i sure want to delete there
yes purchase order has been deleted we got the message and now it goes to the default okay
great great so that covers the orders right this we don't need here don't need to test that
very good we also have a very cool home screen then we're going to get to the dashboard if you
take a look at this the home screen relatively simple all we have is just a few macros that are
going to help us out clicking on the application macros here we've got go to sheet this is going
to help us so how do we do that right so for these few all we need to do is just name the shape
whatever the name of the sheep is so if you see this transactions or you see this called dashboard
and then this is called items and this is called admin and this is called orders so if the user
clicks on any one of these all we need to do is go to the sheet based on whatever the shape name is
and then another one we have home same thing here home right so the same macro can be used for all
those and let's say macro is called menu go to sheet so all we need to do is take the name of the
shape and activate the sheet associated with that as long as the name of the shapes are exactly
the same as the name of the sheet the shape is exactly the same so that's all we have to do just
one sheet will do just that for us so relatively simple now it's slightly different we went over
this already outside the home remember we went over these these eventually we'll do a little
bit differently this one we're actually going to go to the sheet so we create a brand new one
remember this one we've already shown you on this let's just take a look at that under the
home remember this one the macro that's assigned to this let's go assign the macro off the screen
here remember that's called order new remember already knew we went over that one remember just
inside the order macros right here or order sheet macros here order macros here all the way at the
top order new we already went to inside the order new remember if it's a home screen we did all
this based on whether they're coming from the home screen so we already covered those that's going to
create the automatically the new so that's it for the main menu very very simple just creating these
shortcuts okay cool but what about the dashboard let's try to get to the dashboard this is a really
really cool on our last screen that we're going to go over today in this marathon training right so
we've got a profit and loss of period profit and loss based on the period that they've selected
right and we also i don't think we have much i don't have much very very much data in here a
little bit but uh we have this year as we see we've created additional data here so how do we
create this really cool date range well the first thing is we do understand that we have this date
range here that we've created in name range called date range so it's called date range and then
what i want to do is basically when a user makes a change to this date wrench here let's go ahead and
pull up our headings for now and what i want to do is if they make a change to e3 i want to then add
that from date to h3 and i want to add that today to k3 and of course that's going to be based on
whatever the user has set here and here okay so to do that we make sure that's going to be a change
event based on change event on e3 so let's take a look at that inside our dashboard here we're going
to make a change to e3 okay so if there's been a change what i want to do is i want to dimension
the date range as a string and then range row is long and then what i want to do is i want to set a
a1 to true what this is going to do is just set us a temporary field right here called aa1 i want
to set the date range to true the reason is is that i need to differentiate between when a user
makes a change right if they make a change to here that's one type of change or maybe the user
makes a change to here and we can set so there's two different types of changes one here and one
here and i want to differentiate those two types of changes and i'm going to use this field true
or false a1 to differentiate those so i'm going to set this to true and then back to false when
the user makes a change to e3 so we're going to do that for right here a1 equals true we're just
going to set it back to false when we're finished so we're going to set the date range to the target
value and basically what i want to do is i want to see what range they've chosen right if i take a
look inside here inside the code i want to know what range this year i want to know what row is
found on i want to know that that's found on row 17.
If i know it's found on row 17 i know that q
17 is the front date and i also know that s 17 is the two date so that's going to help us so we're
going to extract that row using the find function and we're going to base it based on the date range
right we're going to look for the date range that they've entered and we're going to extract the
row if it's found then a date range is not going to be 0. then again all we need to do is inside
h3 place whatever's in q and the range row that's the from date and then s from the admin screen
that's the two day i'm going to place that in k3 then calculate that just to make sure then you're
going to set a1 back to false and then we're going to run the macro that refreshes the dashboard that
dashboard is going to help us out that refresh then also if they make any change to either one of
these if they make a change to either h3 or k3 i also want to refresh the dashboard too that's
going to come here if the user makes a change to h3 or k3 and i want to make sure that h3 and k3
are not blank right checking to make sure they're not playing then i also want to make sure that aa1
is false right because that kind of change i don't want to run it twice so making sure we're going to
refresh the dashboard so basically on any kind of a change whether it's an individual date change or
in a date range change we want to update the data that's going to update this data so how are we
going to do that so let's take a look at some of the macros non-net data refresh so this dashboard
macros here all the way up here we just have a single macro here it is let's see right here i
want to make sure we show this dashboard refresh first of all we notice that we have some pictures
associated these pictures are associated with this i want to know the top 10 products to order
right i want to know what items we're running low on i know that we need 14 coffee right we're
running low on this and we because we have a minimum set how do i know that well if i take
a look inside our items database and we're also going to focus on the transaction to calculate
the r under remember we still have to go over this formula right here the quantity on hands
i want to know how many order items to order so if we look inside that coffee cream of beans here
coffee let's beans crema i want to know that where our minimum quantity is 30 but we only have 16 on
hand so we need we need we're short 14 right so if i take a look at the dashboard i know we need to
order four at least 14 of them we're short that so that's the number one we're order we're running
short on that so we know we've got to run that so how do we know that well again the first
thing what we want to do is we want to make sure that we're going to run advanced filters so we
know all the details i want to clear all these pictures out that's the first thing we're going
to do inside this macro so these items are always called item pictures so if we take a look say
it says i didn't pick 12.
I didn't pick 13 here and 11 they're going to reverse order 10 9 but
they also contain the word item picture so that means we can delete any item picture here so we're
going to loop through inside the dashboard all the shapes any picture that contains item picture
we're going to delete it now what i want to do is i want to get the data for the profit and
loss we'll go in order on this okay so profit and loss i want to get the data that's going to
contain this profit and loss i want to know all the income all the cost of goods sold the gross
profit and that and that's going to come directly from our orders database right i want to extract
all that and i don't want to put it inside here so we're going to have that dashboard results i
want to get the profit and loss so the best way to do that is run an advanced filter based on the
dates the user has selected if we take a look back in our dashboard i've given these named ranges we
see that this is called from date the name range here and we see that this is called to date the
named range here right so knowing that's going to help us create an advanced filter so i want to
know all the orders from this date to this date right but i don't want to know just all the orders
i really want to focus really on purchase orders and i want to focus on invoices because those
are all parts and i also want to know all the transactions right so we also want to know all the
income and expenses from here so we got to combine two rows of data here so let's take a look at that
so the first thing what we're going to do is focus on the orders database i want to clear all the
existing contents we saw some data right here inside our orders database we saw some data here
so what i'm going to do is i'm going to clear all the data from here aa3 through ae so here's what
we're going to do we're going to clear all that data from right here then we're going to determine
the last row of the orders the last one is less than 4 it means we have no orders what i'm going
to do is run that advanced filter but i want to copy down the balance formula i want to know what
balances right that's very important i'm going to copy down the balance formula here's the formula
right and we're going to copy this down to all the last rows just bringing it all the way down to
the last row so once we've done that it's going to come directly from m2 that formula m4 the last
one bringing down the balance formula then we're ready to run our advanced filter it's going to
call from a3 through l all of our orders here in order to list a3 all the way through l right and
then i don't need the balances in that that's just health card and i want to use it from criteria the
order date right i want two order dates it must be greater than or equal to from date and it must
be less than or equal to two date greater than or equal to from date less than it goes to date and
the reason i i have two there is because i want both the invoices and purchase orders i want to
include both of these right because the invoices are where our income is and our purchase orders
are where our costs are associated so i want both of those if we're focused on our profit we will
need to know both income and our cost so invoice contains our income purchase orders container
costs both are very important so our criteria is going to be s2 through u4 s2 through u4 all
that and i want those results to come directly inside here aa2 through ad so our results are
going to come a2 through ad2 then we're going to determine the last results row aa right i want
the last results row if we take a look at this here we're going to get our order type is here
invoice all the way to here actually it doesn't include this so this is we'll delete it so that's
going to give us the results i'll show you how the rest of that comes right and that's important
as well but i'll show you that in this step so we're going to bring in all the information here
so that's going to bring in get us our last or if the last results less than three we're going to
go to no orders then what i want to do is if the last results row is less than three then put the
last results equal to two right i just want to set the last results row it's either going to be 12
if there's no data we're just going to set it to two or three was it two or three it's going to set
it to 2 because i'm going to add on top of that don't worry about that i'm going to set it to 2 or
the last results rows 2 right because there's no data so what i want to do now is i know that our
invoice we have all of our invoice and purchases but in this application we also have transactions
transactions include expenses and income as well right we've got income and we've got expenses
so what i want to do is i also want to bring in all the information through our transaction
database based on those dates right so i want to know all of the transactions based
on those same dates so we have criteria greater than or equal to the from date and i also want to
less than or equal to the front i want to bring in all of that information based on the transaction
dates i want to have those results come here and i want to bring in also those results i want to
bring it on top of that into our orders database here and have that appear down here so that way we
have all the information here so actually bringing it down to right here and then bringing the
type of expense or income would show up here so that's what we're going to do so you see as soon
as i refresh this dashboard just by clicking here we go back into the orders database we now see
that we have all the additional information so we're going to also go through the transactions
we're going to focus on the transactions right so we also want from our orders we want all
of our invoices and all of our purchase orders and from our transactions we also want that so the
last row based on our transaction database if it's less than four go to no transaction data so we're
going to run an advanced filter from a3 through f inside our transactions database here a3 through
f right i also want f is fine we don't need the memo in here our criteria is going to be right
here we can also use this v2 through the w3 that's sufficient enough right two dates i want to lessen
this so that's always through three it's fine here so that's all we need to do inside here where are
we and w3 that's sufficient enough not the fourth one okay so it's our track i want our results to
go into aa2 through ad2 our results must go here a2 3d and i want all the results to come down here
i want the type the date the amount and account notice it's very very similar to our orders
database here right order type order date the amount and the account right also the same right
because i want it almost the same accounts are going to be the same right in here so we're going
to extract the transaction database i'm going to bring it right down to here so can determine the
last transaction row right here the last row is going to be based on column a a i want to know
the last transaction row in this case it's 10. so i want to take all this information and i want
to basically copy it over and i want to determine the last row of our order database which was right
here and i want to paste it right in here just paste those values in here maybe one above one
above here so basically i want to paste it all above here that's what i want to do inside here
so that's exactly what we're going to be doing so once we determine our last row of our expenses
we can then bring it all information so our orders database ae 3 formula oh i forgot i want to bring
down the formula right here so so here here we combine the two of them almost missed a line here
we're combining them our orders database a a and the last results are a plus one through a d in
the last results row plus one plus the transaction basically all we're doing with this line of code
is taking all the results from our transaction database right here all those results and simply
bringing them over to the next available row right here so just bringing them all over here then what
we're going to do now we have all this data then what i want to do is i also want to know not only
do you know the account i want to know the account type is it an income account is it a cost of
goods sold or is it in the other expense account and so we can use a formula to determine that
right here so all i need to do is look it up so what i'm going to do is i'm going to take a look
at this account here and using a formula determine that count so that formula is i'm going to use
isair i want to check is it an income account the first thing we can do is run the match and
if it returns an error we know it's not an income account but if it's false we know it is an income
account so we're going to use the match if there's no error we know it is an income account so we're
going to because i'm using a mass the account's income i'm matching whatever's in ad3 if it's
false we're going to setting up if it is if there is an error then what we're going to be doing is
we're going to index the accounts expense accounts types remember we had a named range called
expense accounts types and that was right here insider expense i want to basically index this
i'm going to look up for the expense i'm going to match it i'm going to determine what row and i'm
going to return whether it's a cost of goods sold or other expense and we're going to bring that
directly inside here and i'm going to copy that all the way down here otherwise if there's an air
show empty so what that's going to do is going to return the count type bring it all the way down
here once i know the account tab i can then create a summary based on that that's all we're going to
do here so great so the last row here and then all we're going to be doing is bringing down that
formula getting the last row basically in a a getting that last row bringing down that formula
once i know that formula our excel formulas can take over so we have our dashboard panel all
i need to do is create total income cost of goods sold and then simply do a sumif so what
we're going to do is we're going to summing all these columns based on the column types so
if it's an income based on the type of income i want to sum it here if it's a cost of goods sold
i'm going to put it here i want to know the gross profit which is simply the total income minus
the total cost of goods sold and i also want to know the other expenses we're going to use some if
other expenses then the net profit which is simply the gross profit minus that now if we go into our
dashboard we see that we just need to create some links so i just created some simple text boxes to
link actually to those fields so if we take a look here we see that we have a text box right here so
this text box here is located orders db h3 h4 so these are all text boxes simply linked to that
background let's move that nice little picture back up here not this one this one then i just add
a little bit of a picture although you can almost not see it and a coffee picture here which i kind
of like so we have this so these are all just text boxes simply linked to our to these fields
right here all the way so i definitely link them that's it that's how we do our profit and
loss okay continuing on with the code let's focus on our get transaction for the actual we did
that top 10 needed items so we've covered data for the transact for the profit and loss we see how we
did that now we're going to focus for the top 10 needed items remember in the dashboard i created
this i want to know the top 10 needed items so i need some code to help us out with that so that
we can determine all the items that we needed so how are we going to do that we're going to focus
on the items database so that's going to take our focus right here and again we've created some
criteria and what i want to know is i want to know all of the items that are inventory right remember
we don't want non-inventory items we only want inventory items and i want to know where the
minimum quantity is greater than zero right if you set a minimum quantity to zero then we don't
need it but i only want to know those which the minimum quality is greater than zero and i want
to return all those items and the information here so i want to know of the item i want to know
the picture the minimum quantity the item name i want those rules to come in here then using
formulas we're going to calculate the quantity on hand and the quantity needed so let's take a
quick look inside the vba here so the first thing what we're going to do is we're going to clear any
previous results that's burned all the way from t3 through x clearing all these previous results here
we don't need those results there then what we're going to do is determine the last row if it's less
than 4 exit right i'm going to run an advanced filter just as i'd mentioned to you q2 through
r those three those are going to be our criteria right making sure that we have an item type of
inventory and a minimum quantity greater than zero q2 through r3 make sure that's set to r3 what the
results come through t2 through v2 all of those results right here then we're going to focus on
quantity on hand and both quantity needed once we determine the last row based on column t if it's
less than three we'll go to no items then what we want to do is we want to create a formula now if
created formula we need to know the quantity on hand essentially the quantity on hand is all the
items that we have ordered minus all the items that we have sold right that's essentially it but
what we want to do is i want to make sure that we order so i want to know the order items and i want
to base it on the status right i need to know the status right if we've just ordered them we don't
necessarily know that they're in stock so we want to sum all the order items based on the quantity
based on the item name right so this specific and i want to know the order type if it's a purchase
order only if it's a purchase order and the status equals pa what does that mean basically what i
want to know is i want to know all of the items that we ordered through purchase order but i only
want to know those statuses where we receive right i want to know this or this right p8 or p9 if it's
a purchase order and it's pending those items are not in our stock if it's ordered it's not in our
stock if it's shipped it's not in our stock right i only want to know those purchase orders
where they have this status received or unpaid or received paid because those two statuses
are the only ones in which we actually have the inventory right so i only want to sum purchase
orders with those particular statuses either one of these two either p8 or p9 so i'm going to take
all the purchase orders with p8 add in those and all the purchase orders with p9 i'm going to add
those in only those two so that's what we're going to focus on so only those with status of p8 and
only those with status of p9 so i'm going to add in all those purchase orders and then i'm going to
simply subtract all of the invoices in which that particular item came up so we're subtracting all
the quantities right order items quantity those are the order and the quantity we're subtracting
those from the invoices so again every item every specific item that we have ordered that is either
received and not billed or received in stock minus any items that we've been invoiced
that's going to get us our quantity on hand now how do we know the quantity needed well the
quantity needed if we have a minimum quality of 5 and we only have 3 in stock we know we need 2.
so once i copy down these formulas i then want to automatically sort it and the graph isn't going to
actually be opposite so when i create a graph it's going to start at the bottom and go to the top and
that should be there so i'm sorting it based on the lowest to highest so that way inside the graph
itself inside our dashboard it's going to show highest to lowest so that's what we're going to
do inside the code so once we have our results we copied down our formulas our formula is located in
w1 and x1 here's our two formulas located in the adams database w1 and x1 i'm going to bring down
these formulas that's going to set automatically rows but i'm really really only focused on the
top 10.
So once i sort them we're going to take care of that as well so how do we do that moving
on inside the code right i want to make sure that when we bring down those formulas and check to
make sure that we actually have last row if it's less than four we don't need to sort them and then
i want to set the last row the last row is going to be based on 12. so we should probably move that
down here but for now we'll keep it up so we also want to sort the fields right i want to sort all
of the fields and i want to make sure that they're sorted beta based on x3 because why is that i want
to know the top ones right what is going to be the top ones inside our digits i want to know x3 right
what is the most quantity we needed i want to put it down here so we're going to sort them based
on x3 i want ascending right i want the lowest first because that's with the dashboard then what
i want to show is the last results row i want to run that sort based on t3 through x right
that's what we really want to focus on here t3 through x so when i pull up the item database i
want to sort all this information here and i want to know the last so we can that's it once we
create our graph we're going to create a graph based on the quantity needed so that's what we're
going to do inside the code we continue on here and we're going to add the pictures once we sort
it i want to add the pictures we're going to set that picture folder we know that in picture folder
making sure that we have a correct picture folder i want to set an initial left position and not
top position i want to place all the folders inside the dashboard i want to place this i
will be making a few adjustments because it's not quite exactly it's almost perfect but i want
to make some adjustments based on the last row so making some adjustments by the time you get it
okay so what i want to do is i want to set the last position i want to create these pictures and
have these pictures appear next to the items we since we have the pictures models we're using i'm
going to set the initial left position based on n6 and i'm going to move it 48 pixels to the
right top position based on n6 and 4 pixels up we're going to loop through all the results
row from the last results row from the bottom to the top right i want to go from the bottom to
the top why do i want to do that because i want to make sure that we're going to the highest ones
possible right the last one's possible so we're going to start at the last row the last results
was less than 12 we only want 12 results right we don't want greater than 12 we're going to set
the minimum to 10 so it's going to go from 12 to 2 setting our 12 to 3 setting the maximum of 10.
what we're going to be doing is we're going to run a loop right then we're going to set the picture
path right i want to set that full picture path i know inside the item database if i've got the
picture file name and i've got the folder i can have the full picture path combined so that
picture path is simply going to be the picture folder with the backslash based on whatever is in
column t we're going to insert that picture we're going to give it a specific name called itempick
and the result row then what we're going to do is we're going to place that picture on the top
position after we've inserted it we're going to set the left position we're going to bring it
over based on the name right i want that based on the name if i know the length of the name right
this item name right some of the shorter names how i want to put the picture closer to them if we
take a look in the dashboard we see that this name is a lot less characters so i want to bring that
picture closer over however this one here has got a lot of characters i want to keep it on the left
so we can use this formula to determine that 26 minus the length of the name multiplied times
4.5 and basically that just space out the left position accordingly so that we can space it
out so that it stays to the left of the name since we're not basing it on any cells the height
setting a specific height of 25 and a width of 25 and then we're going to increment the top
position plus 31 so that's going to bring it down bring that picture down along with the
ice so it's going to keep going down that's how we're going to supply the pictures then
all we need to do is just simply create this graph based on the data so if we select the data we
see that we've got a graph based on the data that's supplied here we're going to be showing
the quantity needed and then all i want to show is the items associated so we just created a bar
graph to show those very simple items and that's just created i want to show the labels here so
that's all we've done here great next up what else do we have to do well we also want to get the
sales data based on a range right i want to know this sales data and i have to get that based on a
range and then we're going to create a pivot chart based on that so if we take a look at that we're
going to focus on the orders database because that's going to get us our sales data we're
going to run again we're going to clear the existing data we've got some more data going back
inside the orders database here and we're going to see here we've got some sales data so let's take
a look inside we're going to be clearing out ak3 through al999 so ak3 through a i want to clear
all the sales data right i want to bring it in here so i want to know all of it bringing that
based on whatever sales that we've created so but we don't want to create i don't want all the sales
data i want to base it on very specific criteria and what is that criteria is going to be based
on s2 through u3 let's look at that criteria s2 through u3 i only want order types of invoice and
i only want based on the very specific dates that we've created so that's what i want to return only
invoices not purchase orders and i want to bring that data directly inside here so that's what
we're going to do here the last row i'm going to run our advanced filter s2 through u3 bringing
that data over once we have that data i can then create a pivot chart based on this so i've created
that and we create a pivot chart based on that so as we bring this information down we have a
dynamic pivot chart based on that and then i just created a graph based on that so if we take a look
inside the dashboard and we look at the data based on that we see that the data is based on this
total data based on this information here this mini accounting application and we've based it on
all the data here inside our orders database the sales pivot data sales data pivot that is the same
data that's correctly right here the sales pivot data so if you take a look at the dime we see that
the sales data is directly based on here so we just created a chart based on that just a little
bit of a line chart not given in any backgrounds and just giving a little bit more information here
adding some fonts and things like that relatively simple on that nothing too famous on that
continuing on i also want to get the outstanding invoice data now that outstanding invoice data
we're going to create this very cool create this cool tree map for both outstanding invoices and
outstanding bill payments but first we need to get the data so that data that we can use of course
inside an advanced filter we're going to have that data the same we're going to use our criterias aq2
through a s how do we know that let's take a look inside the orders database here and we're going
to be looking inside our aq so we have outstanding invoices criteria certainly we want to know it's
an invoice we also want to make sure the due date is greater than less than today right it has to be
less than today and i also want to make sure the balance is greater than zero so i want to know
all the invoices with that criteria and i want that those rules to come directly here and bring
those inside here so it's going to bring all the data inside here then what i want to do is i want
to create a tree map based on that so if i decide that we're going to insert right here tree
map i just want to insert a tree map here it's going to base that on here and then we're going to
customize that tree map based on that data here so we can select that data here and then of course
we can make the updates right if we don't want to include the particular order id we can just
include the name and dip and we can there we can create the name so there we've also done that here
just this little tree map here and then i can just simply move the chart and move it to a different
that's just what i did here i moved that chart so if we take a look inside the dashboard and i
just updated the colors we can update the color to this blue which is a little bit of our theme and
then we can customize it here if we decide we're only going to add data labels here we don't want
a chart title we don't want a legend just data labels and then i've increased the font made it
bold and white so that's pretty much it i've done the same thing with outstanding bill payments
outstanding build payments exactly the same i've created however differently differently this one
is a little bit different if we take a look inside our order database here if we move it over here
this time i want outstanding purchase criteria i want to know only those purchase orders i want to
know a due date is less than the current day and i want to know balance greater so those are bills
that we owe and i wanted those results to come right in here and then i've created another tree
map directly just like i did there and put it here wow what an epic training for those of you
who stay till the end congratulations thank you for sticking with us to the end let me
know in the comments that you stay till the end i want to hear from you what did we learn in
this training wonder how to create an incredible mini accounting application complete with a home
screen where we could quickly go to any screen we also created a amazing vocabulary we could
fully customize an application based on the end user requirements and then have those new names
throughout the application we learned how to create this incredible order screen whether we're
creating proposals work orders estimates invoices and we showed you how we can easily quickly
change those on just the thing to automatically update the application and update the end users
experience on that using conditional formatting data validation creating orders creating inventory
we also were able to add pictures and items inventory and quantity on hand we would also show
you how we can quickly and easily edit customers employees names and also add new ones of course
we will also show you that email print the orders copy to orders quickly create a brand new order
based on an existing order with our copy to and of course we have transactions where
we can create multiple transactions selecting transactions quickly locating existing
transactions based on multiple filters whether it's date name account or amount how
to create an incredible comprehensive admin screen using named ranges to help us quickly
locate default settings we have that dynamic tax date ranges and a whole lot more and also this
incredible dashboard where we're able to show a dynamic date range thank you very much for
sticking with us i appreciate your continued support and i look forward to bringing you many
more applications don't forget i've got a whole ton of you if you want to support this channel
so many great ways including the 250 workbook pick that up i'll make sure to include this as a
bonus inside that of course you're always welcome download using the links down below thank you so
much we'll see you next week have a great week