All right thanks so much for joining me i've got a really fantastic application today not only that we're going to be creating our own mobile app not only that but we're going to show you how to take excel sync it to the mobile app data take anything entered into that brand new mobile app and send it back to excel we're going to have seamless integration seamless synchronization between both excel and that and of course we're going to create this really cool application here something we've done before scheduling drivers customers and something like that so it's going.
To be a really fantastic training i hope you'll stick with us through the entire training so i'll try to not keep it too long but i'm going to move slow on certain parts especially things that are i really want you to pay attention to so it's going to be a fantastic if you do like these trainings i just ask a few things very simple things i know you can do the first is smash that like button that'll help others get to this and find out how they can learn how to build applications through excel that's what i'm all about if you also i would appreciate if you could subscribe just go.
Ahead and click that subscription icon below and of course that notification icon bell that's going to ensure that you get these trainings each and every week this application is absolutely free it's actually a template for you all you need to do is click the links down below either with your email or your facebook messenger we're going to go ahead and get that set over to you right away my goal is to not only just teach you excel but to make you successful with excel and i've got a few.
Courses that can help you do just that if you want to take your passion for excel or your inspiration and you want to create applications on your own for passive income i've got an incredible course that's going to help you do just that and that's called the mentorship program mentorship program in that course i teach you every step of the way through a 12-phase program exactly how to define design develop and deploy your own excel applications for passive income that you can count on each and every month right it's not just good enough to know excel but how can you make money with excel this course focuses exactly on that you can get to that through my excel mentor dot com my.
Excel mentor dot com i hope you'll join up it's an incredible course tons of students are graduating and very successful from it all right let's get started on this course because i've got so much to show you this is actually a mini course in itself right because we've got an application to develop we have to show you the mobile integration now for those of you that have been with me a while this format looks familiar right i as far as developing the application it is something very very similar if you want to know what it looks like from the original training go ahead go to the maintenance manager the maintenance manager that is an application we created last month and we went over a dashboard just like this we went over this menu like this and the scheduling very similar.
With macros does not work on tablets or does not work on mobile phones at least tablets that don't run the full version of office so what we need to do is we need to bridge that gap everybody wants to use at least these applications on their mobile so my job is to teach you how to bridge that gap and have seamless integration between apps and excel and it's all doable we've got some really great apps so i'm going to show you every step let's go ahead and overview on this we've got a dashboard it's kind of a simple dashboard but basically we want to know total jobs by status completed and closed jobs and total deliveries very very basic on that we've got a few timelines here nothing a whole lot now we've got jobs we've got a list of customers that we're going.
To be able to do and we can include delivered if we want to just by selecting it's going to load all the delivery jobs or just those so it's going to be a quick filter we'll be able to filter by delivery address job id customers pick up address and a whole lot so there's some really cool features here we'll be able to of course save the job that's going to automatically update the mobile data so if you've got 10 different or 20 different drivers each one of their phones is going to automatically update with any new jobs or updated jobs we can create a new job we can print the job we can delete a job and of course we can sync the mobile data if there's any data so we're going to go into all that i've got a really cool schedule we'll be able to go to a schedule we have i only have one pretty much one month for the most part jobs but it's.
Really really cool and of course we'll be able to select on any job it's going to go directly to that job so a very cool schedule we've got a list of drivers it's kind of just basic some basic information for drivers basic information for customers and an admin screen we'll be able to have we need to know our application folders we'll be able to customizable job statuses notice these colors we'll have a color palette so we can change the colors now this is indicative of the schedule right so those colors are basically colors based on the schedule we've also got custom icons right.
So based on the job status so notice this merry badger notice that little truck if we click on that we see that that has been picked up so that's the icon that we're going to use for the picked up so we go into the admin let's scroll down here we see the admin here and we see that the picked up is going to have this picked up icon so i've got a list of icons and colors that are going to be help us for the schedule and then we've got a list of times and a few other things okay so let's get into it so that's our admin we've got our dashboard we have our jobs here we'll be able to add new jobs we'll be able to show a signature if a job has been delivered let's see if we can find one with a signature i believe this one's got one here a 42 we just did that as a sample that one's got a signature so we will display a signature as a picture that's going to come from a signature on the mobile device okay and then the rest of the job information so we've.
Got pick up address pick up time pick update right and uh delivery date and delivery time everything else is kind of standard with this and then that's pretty much it as far as mobile as an overview it's a relatively simple uh overview but we'll go over this in a little bit of detail and then we're going to really focus on that so let's go over quickly exactly how we created this then i'm just going to go over briefly and overview then we're going to get right into the app development because that's super cool okay so we've got a dashboard here right and we've got.
Different sheets so let's go into the vba and see how we do that so we've got a macro that's going to run this it's basically going to allow us to control different sheets now this is a single
Macro okay so each one of our sheet names are very specific right so that i can use a single macro so if we take a look in this we see this is the name of that shape it's called dashboard and of course the name of this shape is also called dashboard so regardless of whether the user clicks here or here they're going to get the same right same thing here this is called here if i select on this called jobs right so each one of these shapes these are all just basically shapes they're the same name as the sheet right so when we go ahead and go into the developer we're going to see that we can quickly move to another sheet with a single macro and what is that macro well we can.Go into alt f11 to get you into the vba of course we can assign the macro when we assign that we see that we've got the edit that's going to take us directly into the vba editor here so inside our vba editor alt f11 is a shortcut we've got four different modules we have application macros we have job macros we've got a pop-up calendar which we're not going to use much that's just for the pop-up and then we've got the scheduling macros so that's pretty much it four modules now the application macro we have a very very simple macro here this is the called menu select this.
Is the macro that's been assigned to each one it is the same macro so when i right click this is an entire group all the shapes in the group if i want to assign the same macro to all the shapes in a group all i need to do is just click on here and then just click find the macro here which is called menu select here and just assign it this will assign that macro to every single shape inside that group automatically okay so now so how do we do that how do we create a single macro so.
Remember the shape names are critical remember the shape name is called dashboard right if i know the shape names are critical and look if we take a look inside our dashboard we see that this has a very specific code name called dashboard this is the same name here this code name drivers jobs very specific so we've got both the code name on the left and the sheet name on the right inside the parentheses okay so that is how so all we need to do is focus on the name of the sheet so if i want to call that shape and i want to activate that particular sheet all i need to do is use the shape name because they're exactly the same to get that shape name of whatever we clicked we're.
Going to call application collar that's gets us the shape name so when we turn off screen updating and turn it back on it helps from flashing so that's all we have to do now keep in mind if you try to run this macro from here there's no shape name that's selected it's going to create a bug which is fine because there's no shape name but if you run the same macro right you're going to see its application color why is that because nothing no shape called it right we clicked here no shape but when of course we run the same macro and we click on here it's going to work just fine okay so.
Keep that in mind all right we've got a few other inside this module a few other macros we can set a booking color this is relatively simple we're going to be moving a little bit quick because i want to focus our attention on this admins right we're going to make a selection change that selection change is going to make display this color palette it's called color palette right when i make a selection of the color that's going to automatically change the color in here so it is that single macro that single macro that's been assigned to every single shape right if we take a look here we look drill down into an individual shape we us right click sorry it's off the screen.
Let me bring that up a little bit here okay so when we right click here and we click assign macro we see that it is called the set booking okay that's the macro that we're focused on here all we need to do is take whatever the color of the shape that we clicked and take that color and put it inside the background of a cell so our active cell interior color that's the one that's going to be changing and it's going to come directly from the shape that we clicked and whatever the fill the four color the rgb of that shape is that's all we're doing and then we're just going to hide that shape so that's it we've got a few brows i need to browse for some application folders so we'll move it a little bit quickly because it's kind of basic.
All i want to do is i want to get a few information i want to know where are we have a central folder where all of that data is going to take place and so that means when i make changes or when the drivers make changes on the road that has to come to a central folder we need to map that center folder so i've done it right here we're going to call it inside it's got to be inside of dropbox we'll be using dropbox for this app sheet we're going to be using that data and this is our folder so this is all the data we have the application data that's going to come in we have driver images we have job images job images would of course be the signatures so if we take a look inside the view properties we see that the signatures are going to be stored inside here.
And we also have data the data is going to be the data that comes in so if we have signature data or a change so that's going to come in so we have a lot of information that's going to come in and of course we have the driver pictures here so that's what's so we need to map this folder i need to know what folder this is so all we need to do is browse for this folder this is the folder i want that's where our data is going to come in and that'll come into play a little bit later on so what i need to do is i need to know what folder so when i browse for this icon of course we need to find that automatically and so we have the mobile data file right this is the.
File right this we need to know what file this all go over into this is a specific excel file where
Our mobile data is located separate from this file inside that basically what we're going to i'll go over that in just a moment we have two tables one for driver and one for jobs okay and i'll go over there and of course we need that folder where our applications or icons are called right we need those icons those icons are going to be inside our admin right here these are the names of the icons and i need to know what folder they're located in because when i browse the schedule i need to create those icons and put them directly in the schedule we have several icons scheduled pending and so on and so forth right so we need that so we're just going to browse so i just need a few macros they're going to do that so browse for the data folders here data file is here and data.Folder is here so those are just macros to browse for files and folders keep in mind that a file we're going to only look for xls you know i only want xlsx or xls right we're not looking for a macro this is simply a data file an excel data file so that's all we're browsing for and then of course the data folders any folder for the mobile data folder and for the icons any folder that's pretty much it this is the app update from application we'll be going over this a little bit later so that's for the application jobs is going to be very very simple i'm going to go over that very very briefly we have one macro that's going to load the list and all we're doing.
In this macro here is we've got a database of jobs okay so what i've got here is all of our jobs here customer information this is all the information is we have a picture name of that signature and a folder here okay all i'm going to be doing is setting some criteria based on the status and the in this case the delivery address so this is going to be based on f4 so that means if i change this criteria inside here right if i change it to let's say job id and i want to search for a specific job id i can do that right here so easily just search for that so what we need.
To do when we search for that is we need those cri that criteria and then we also need to know if we're including the delivered or not right if we're excluding delivered since this one's already been delivered we're not going to include it but in this case we are so how do we know that of course that's based on criteria inside our jobs database so let's take a look at this dynamic criteria job id is three notice this is linked to jobs f4 this is linked to jobs f5 if it's if it's not enter search right if the words are not enter search javascript also keep in mind the status the status is either going to be if jobs it's either b does not equal or doesn't equal so.
Let's take a quick look inside that when i change this to include delivered so we're not including delivered now let's take a look back here now it says does not equal close right i want to know it does not equal close or delivered probably should say let's just change that to does not equal on the description i really want close let's take it like include closed okay i'm going to put close i think i updated the name here so what i want to do is this should be closed because when you close out you may not want to do include close that's what i'd really like okay so we're going to include or exclude close delivered right we have got a few different ones so i've got delivered and i've got close this data so i'm going to just exclude close delivered's okay.
But closed i don't want to so that's a good way so now what we're doing is we're excluding anything that's closed so inside that now we see we're excluding does not equal close so we're looking for job number three if it does if enough of course nothing's appeared there because job three is closed so if i take a look inside that and i take a look at job three we see that the status has been closed that's why it doesn't appear when i clear the search it's going to go back okay so the macro to do that is very simple the first thing we're going to do on the job sheet is clear our results then we're going to focus on the jobs database i'm going to move a little quicker here getting the last row making sure we have lasso running an advanced filter with our criteria that i just went over having those results and then checking to make sure that we have a last row and then bringing those results into that okay that's very very simple clearing.
Filter all i need to do is simply change this to enter search we have a change event as soon as there's a change to f5 it's any change at all it's going to load this macro enter search is going to do that okay so that's all we have to do clearing that search is simply going to clear it okay that's it's going to put enter search here and then it's going to clear the list very easily okay moving on saving and updating we're going to this is very very basic except where the point is we're getting the mobile details okay i'm gonna go over this macro because there's some updates in a moment right after we create our mobile app saved message is just a fade out message right as.
Soon as i save that job we have a little fade out message can appear here in the upper right that's relatively simple and it's just going to fade out of shape until it's completely gone right so it's going to you can speed it up or slow it down as if you change this and this it's going to do that showing the signature pick this simply shows a signature pick right if i've got a signature in a folder here including our let's say our job images here i need to map that right i need to know where these signatures are and i need to put it so i've got it each one has a signature name and so what i need to do of course i need to map that so that i can get the full file path if i know so what we're going to do is we're going to save that name and we're going to also save it the folder as well in that because here's what we've done we've mapped all the way up to this folder is our main folder.
All the way to this the rest of it is going to have to be saved job images along with the name so it is that name that we're saving inside the database so for inside our jobs database we're going to save the folder that it's in and we're going to save the signal so we're saving the folder that it's in job images and we're saving the name of that so when we combine this folder this name and we combine this path right here so we can combine all that we get the full file.
Path when we get the full file path we can then actually notice this one is called this is called mobile data folder mobile data folder for named range okay so that's the one we're calling it and so when we combine that we combine it with this path here we get the full picture when i have that full file path and it is accurate i can then display that picture in any particular so let's go ahead and pull up a job with a picture here job number three has it so we'll go back.
Into the dashboard here jobs actually job number three here all right reset that and now we can see the picture is gonna show up okay so we've got a picture there and that's automatically just updated the database on that so we can show a picture in there so how do what i want to do is i want to get this picture centered perfectly in column m and perfectly centered vertically between rows 11 and 14. so that's what we're going to do here so for showing the picture right first of all i need to make sure that we have the correct mobile data equipment folder mobile data folder and i want to make sure we've got the correct one so we're going to check is it empty.
Then please let you can see i program these fast and then i just copy and paste it's quick mobile data pictures okay so we need to actually please set a folder for mobile data i guess it's just mobile data and so that's all we need to do so we're going to browse for the data folder let the user know that we're browsing okay if it is still empty we're going to run another check if it's still empty we're exiting the sub after we run this macro so we've got this signature folder here it's located in the mobile data folder we've got the full file path all we're going to do is we're going to put it now this particular file name is actually saved right here in b12 so when i.
Load that data notice that this one doesn't have it but this one does so when i load that job in this is the name keep in mind that we have the images folder and the name combined so when we combine those two we get a full file path it's located in b12 once i have that inside b12 i got now i've got a full file path because i've combined both of them to do that so we've got that we're going to check to make sure it's inaccurate if it does not equal empty we want to check for an accurate path then all we need to do is insert that picture we have a correct path we're going.
To insert that picture we're going to lock the aspect ratio to make sure it doesn't get contorted we're going to set a maximum width of 115. we're going to set a maximum height of 80. then all i'm going to do is center that center it inside column m taking the width of column m subtracting the width of the picture and dividing that by 2. that's going to center it horizontally then i want to center it vertically i'm going to do the same thing but this time i'm getting the height of rows 11 through 14 and i'm going to divide that by two that's going to center it vertically that's it new right all we're going to do is clear out a bunch of cells making sure that the signature pick that in case there's any we're going to delete that if there's not any it could create an error so we're going to wrap that in on-air regimen x and go to 0..
Okay loading it all we're going to be doing is just take clearing all the contents about deleting any signature that might be there making sure that b4 is empty b4 is going to contain our row if you know so i've got a job id here i've got a job row here okay so i need to know make sure that job row right and when i click new job you see there's no job row right there's no job id so we need to make sure that if we're going to load it from this row here inside the jobs database we need that we're going to use data mapping we've been over that 100 times if you've seen in my videos so there are videos that are dedicated to this so basically we're just going to take the information and bring it directly inside here i'm going to turn off application screen updating we're going to load all of that data from the database into the fields and then what we're going to be doing is we're.
Going to i want to extract here's something kind of new and i want to show you this one i'm going to spend a little bit of time in this because it's brand new so here's the difference this is kind of tricky but but i really like the idea of it so i want to share it with you what i've done today okay so if we take a look inside our database we have a pickup date and time and we have a delivery date and time they're combined right now and i like to have them combined inside the database right they're much easier for scheduling and sorting and things like that however when it comes to user entry i really like to have them separated in other words i'd like the user to set a date and set a time and so i like that ability because they have drop down lists so i like but what i want to do is i want to combine them so when i'm saving them.
Into the database i want to combine both the date and the time and i want to have a single column so what i'm going to do is i'm going to create an additional field additional cell here that combines them and that's what we're going to do inside b10 so b10 all we need to do when we create a date and time all we need to do is simply mathematically add the date and the time together and that's going to be in b10 so whatever is located in i11 and k11 we're simply combining them and that's going to create us both the date and the time same thing with the delivery all we're going to be doing is combining them we're going to take in the delivery date and the delivery time and we're combining them so when i change this to 6 7 30 am you see it automatically changes here to 7 30. so they're just linked so it is this.
Information that's going to go directly inside the database and that's great so when i'm saving it all i need to do is map instead of mapping these two fields i'm going to map b10 and b11 now that's really good so that means when i all i need to do is bring this value in to our jobs database right here into common notice it says b10 up there so i can bring that and notice this says b11 up here so i'm bringing that in that's fine however however when we load this data in when i bring this information i want to bring it back into the jobs right i need to separate it i need the date and the time separate when i'm loading it in here what i do i can't bring it back into b10 because b10 is a formula i cannot do that right i need to keep b10 as a formula.
So what we want to do is we want to take the value we want to separate it using vba put the date here and put the time here but not loading it into b10 and so that's a difference so what i'm going to do is i want to check i want to make sure it's not empty we're looking for a pickup time right and the pickup time is located inside column f so i want to make sure it's not empty if it's not empty what i'm going to be doing then is i'm going to be separating it so first we're going to check in bba make sure to pick up time and date or this is really a pickup date right pick up the time and date is not empty first we're going to check on that and so once it we know it does what i'm going.
To do is i'm going to separate it right so to get in i11 which is the date that's where i want it to go i'm going to i only want the whole number right remember in a date it is a when you have a date and time it's mixed right if we go into general let's take a look inside this one here and we go to general change of the format we see that it's both the date which is four four six five oh that's the date the time dot three zero two zero eight that's the time right so what i.
Want to do is i want to separate to get the 4 4 6 5 0 all you need to do is use the integer command int and that's going to separate that and then the next thing in the next line of code i want to get only the decimal and that's going to be the time right so when we format that as general we see the separation there okay so it's very very simple so let's take a look how we do that so to get inside just for just the date all i want to use is the integer command of whatever's in f that's going to get us the pickup date which is the whole number what about the time well in the time all i need to do then once i have i know the the value of it all i need to do is subtract out i know that also i'm going to take that entire date and the time together and i'm going to subtract out the date when i subtract out the date it's going to leave us.
With the decimal that decimal times going to go into k11 so that is all we need to do to separate it okay so keep in mind also when we load it we don't want to load in columns six and eight six and eight six column six is the pickup date column eight is the delivery date and time so we don't wanna load those in right we wanna load all the other columns but not these two columns these two columns are taken care of here and here okay column f and column eight so that the delivery time again all we need to do is just to get take that whole number the delivery date.
That's going to give us that whole number using int and then take out that subtract from that main date and time just that specific delivery time once we have that we struck it out it'll extract that delivery time and put it directly inside k14 that's all we have to do great this is for the customer that's going to load the customer and then b12 we're going to show the signature as long as we actually have a signature right the last thing is as long as b12 is not empty we're going to show that signature so as soon as b12 we want to display that signal just going to run the macro.
Okay very good and then deleting it all we need to do relatively simple extract the row the database row from b4 and just run the delete printing it out we're just going to print it out based on the set very easy macro customer save and update we're going to save it right so if i want to just save or update the customer it's going to save any changes we made it's going to save that directly inside the customer database and that's going to be based on the row the customer row we've got that customer row right here located in b10 very good relatively simple so that's it for the jobs it's customer save messages or fade out messages that's it for the job let's get into the cool part of developing our own app and now how we're going to do that well we're going to use a really cool.
Feature we've done before called app sheet right it's called app sheet this is a really really cool app sheet is an amazing uh application where we can create our own app sheet so if i want to create a brand new app first of all you sign up it's free at least for this portion of it the portion that we're going to be doing of course you can get in deploying it when you want to deploy it to many people you'll you'll pay you know there's a pricing and things like that but for our purposes everything we're going to do is free okay so what i want to do once you get signed up all right i'm going to click make a new app so we're going to click make a new app and it's going to be easier to start with the template so we're going to do that start with the template of course you can have an own idea so we'll click start with a template and we'll call this just call this test.
App here's fine and we don't need a category because the one we want to use start is right here under field delivery and i also want to set our app to dropbox right i'm going to use dropbox right so you can also connect your dropbox right that's what i want to do once you connect your dropbox into my apps you can do that and templates okay so we're going to set it to dropbox because that's what i want to link to and i'm going to click free delivery so that's the one i want to do now it's going to simply set up our app that's all we have to do to set up our first test app once it's set up you'll have this we'll have a a little app here we've got it already so we can click customize our app and now we're here we've got a list of drivers here.
We've got active jobs here so we can show all of our act jobs it already comes with sample data if we take a look inside our active job we can click here and we see that we've got job number 38 here we can edit this job right we can delete the job here so we're going to edit it we've got a driver we can select from different drivers here if we want we've got a job name we can click notes we've got a pickup delivery pick up date of time now notice it says pick up date and time but it didn't show that so we're going to need to customize that it should have right it should have showed it time but i'm going to show you how to mess it up drop off a little bit of unclick this add a signature.
Here and then also we can set the status now i've kind of updated this on my app but i'm going to show you how to do that so the status is here and then we're going to click save right and all that's going to do is just save it then we click up here and it's automatically synced and linked okay so that's basically the time so let's go ahead and go into this and take a look at that now this particular app comes with two basic databases we have drivers and we have jobs so drivers if we click on here right we see that we're allowed to add updates storage and what we want to do is we want to look where is this located right if we click on storage we see that now we've connected.
We've already connected our dropbox and i believe inside my account here i think it's going to show um our integrations right so what you'll want to do is you want to make sure you add in your integrations here so channels out inside sources here sources we see that we've got dropbox so we want to add that before you know if it doesn't show dropbox when you create your account when you first create your account add dropbox as a so when you click new data source you get a lot of options right you want to add dropbox there and it'll connect to your site automatically connect your dropbox you want to make sure you have a dropbox account you can use other you can use google drive so there's a lot if you want to try another one go for it for our purposes.