Training i've got so much to share with you this week i hope you do appreciate that of course i do create these trainings each and every tuesday this template is absolutely free all you need to do is click on the links down below and either with your facebook messenger or along with email we will get that sent over right away to you however if you do like to support us there are some incredible ways to do that in fact if you like accounting and you want to ever create your own entire accounting application and also learn how you can create these applications to create your own financial.
Independence with reoccurring income i show you how to do that in my mentorship course full-on mentorship course it is a 12-phase program where i'm going to show you every part of defining designing developing and deploying your own excel-based applications for passive income so i hope you'll join us in the mentorship program i'll include the links down below we've got some really cool promotions going on with that all right let's get started right on this training because i've got a ton to show you don't forget to smash that like button subscribe and click the notification icon bell i'll make sure that this training gets ranked high up and i appreciate that.
Also don't forget to comment i reply to each and every comment okay so what do we have here well i've got this really cool hover menu right how do we display hover is it's one of those few things in excel that allow us to hover over the bill and have something happen on hover so i'm going to show you how to do that we create this hover menu and builds and payments right when we add in bills and payments we've got a specific vendor we may want to search for individual bills we can search by vendor bill id status reference so if we want to search for a status and we want to search only closed we can do that and just click and it'll show you the three closed jobs we can clear it out so we'll be able to select and load a specific build we're also going to be able to.
Display the receipt here if we see we can have different receipts displayed very very easily saving and adding new bills printing them deleting them will be able to save and update the vendor on that we'll attach different bills or we can clear a bill out okay so we've got that that's going to be able to add unlimited bills so very very cool we'll be able to add a status to each individual bill a reference how much the bill amount if it was paid or not so if we decide we were going to market paid we just market paid let's go or unpaid partially paid might be partially paid or notice how the status change to partially paid or when it's completely paid we can also show that right completely paid without do when we save that that's going.
That inside the database right so we're going to be able to do that inside the admin screen we're going to have a set uh scheduling weekday and we're going to be able to also set some dynamic payment types we're going to have attachments right we know that inside the builds we may want to attach a screenshot of the attachment right so you'll see some of these have attachments like this so if we want to attach bill all we need to do is just select this and attach any type of bill so that's called we have to set a folder for that so we're going to set a default folder here and we have an icon folder because on our scheduling screen right here we've got icons these icons denote whether they've been paid or whether they're open or whether they're closed right so we can have different colors and different icons if we have bill date we can set the display so i.
Want to know all the bills when was the bill date or maybe i want to show when their due date that might be kind of important when do they do i've not got some due dates so we're going to have that i've also got a list of basic list of vendors which we can show and update and of course the admin screen so this is a five part schedule along with the hover menu so we've got a lot to cover in this training right we're going to start off and of course of course completed by a dashboard where we're going to show you some timelines we're going to show you some of course slicers and we've got a whole lot more to show you in that so some really really cool effects that we have on our dashboard so it's a patch training if i move too fast i'll try to keep it slow you can also.
Slow down this recording of the video you can slow down the playing of it as i record it in a certain speed but youtube allows that and most other players allow you to slow down the speed if i tend to move a little quickly because we do have a lot to cover so grab your drink of beverage and we'll get started right away the first thing is this very very cool hover menu and i have the same one on each screen so if we go to the bills and payments you see that we have a vertical hover menu so as i bring my cursor over here that we see the hover right here and we can hover over.
It so how do we do that well the best way to do that these are individual shapes right so there's no when i click a shape there's no way to create some kind of a macro on hover however we do have some types of shapes inside our developer if we look in the developer right and you see insert we have some active x controls now these generally don't all work on all macs but they do on pcs so an active x control allows us to for us to program something to happen when we have a hover over one.
Of these shapes so if i were to place these shapes over these other icons we could actually make something to happen so what i mean by so let's just take a look at this label right if i create a label here we've got a label here we have a label here we can assign something to happen uh when the user hovers over that now nothing's going to happen let's go let's go ahead and pin this down here so we can see nothing's going to happen alma is if we're in design mode okay keep that in mind if i click now that we're in design mode when i hover over these nothing's going to happen okay keep that in mind as soon as i exit out of design mode everything becomes live okay so while we're in design mode it is what we want to do so what i want to do is basically create.
Posts Related:
- DAX for Power BI Part 1 - Getting Started with DAX for Power BI
- Carteirinha de Membros Word - Veja como Baixar Grátis
- Power BI Relationships Not Working How to Add Missing Values Yourself Part 3
A macro i want something to happen when i hover over this okay so if we double click on here it's
Going to take us into vba under the bills and payments right here and we also see that we have label one which is the one i just created and we have the action click well when we click it we want some we can have something happen but in this case i really want to focus on when we uh move our mouse over the shape and that's called mouse move mouse move so it is this here we can clear that.Out now it is this here that something's going to happen so if i do message box and then test as soon as i exit out of the design mode it's going to become active so nothing's going to happen as i hover over it as soon as i exit out of design mode you're going to see as soon as i hover over we're going to get that message box test okay so keep that in mind so we can perform any action and so what we want to do is but we really don't want to look we really we really don't want this to look like this so what we can do is go into the properties here and we can then clear that let's bring in the properties over so you can see them and i'll bring them down a little bit here so you.
Can see them okay so we've got some properties but what we don't want to do is we don't want the user to see that right it's not it's not very pleasant so all we need to do is just make it transparent so if we have a border style we've got none which is what we want and we have the back style if we click here and we go transparent we're not going to see that and we remove the label we have to give it a name that's critical but we don't have to give it a caption if i clear out this caption here then we have basically an invisible box right so again same thing getting out of design mode as soon as i as soon as i hover over that we're going to get this test here all right great so we see how we can perform actions on that now what if i were to create different ones some of them that when we hover over and some of them make them disappear so what do i mean by that if i close that here let's close out of this one and go back into design mode here and keep the.
Properties open and now what you'll see if i if i click on here we see we've got some bars here here here and here and here so so i've got five different horizontal these are all of course text boxes these are all as we saw here in the indies insert right here we saw we've got different i've got all the label they're activex label controls activex label controls there's no border and there's no filled right but so they're completely invisible however when we hovered over them we want something to happen so if i double click this here or any one of these let's go ahead and and.
Show that double clicking it and what that's going to do is going to see hide menu so i've got one hide menu one two one through five for each one of those horizontal bars i've got a five okay so what that's going to do is run a macro called menu hide and that macro is basically going to hide all of our shapes so let's take a look at some of the shapes we're going to go out of design mode and what we're going to do is we see we've got to shape it this is called menu 1. and if we hover over this one this is called menu 2. so we get you get the idea of what they're all there's five year menus and they all have the same written and this last one's called menu five so what i want to do.
Is when somebody hovers over one of these these bars here these invisible bars you can see it there if i click on it if when somebody hovers over that i want to hide all the menus however that's great but how do i get them to show up well i've got some other ones some other activex controls and there if i click on the design mode here you can i will be able to zoom in and let's go ahead and select one right a little bit hard to find but if you ever want to know how to find them these are the icon shapes right so that's the icon that's not it but they're hidden right but but how can you find them well all we need to do is go into the selection here and look them up so.
We're going to use our selection tool we can close out the properties now and we go we see we have one that's called hover five hover one number three two and one okay so that's how we're going to get them once notice we can't select them but as soon as we go into design mode keep in mind design mode is how everything happens so now when we select hover one here it is here is our hover this is our activation control so it's right here under hover one so here it is right here this is our label shape so that's it we can move it anywhere we want and so here it is this is the shape what's this called this is called hover one and we have hover two which there so basically this is a label without the border without filling and then there's a macro that's assigned to the.
Hover of that so when we go back into the sheet bills and payments here we see we have now these shapes here all the way down here called hover three hover four hover five hover two and over one so it's these specific shapes and now for each one of these i'm going to do something for example on hover one what is hover one hover one is right here if i select it we can see keep in mind that you cannot select it unless we're out of designer as soon as i exit out of design mode that selection goes away design mode is critical for us so this is called hover one i want something to happen when we hover over that what do i want to happen well i want to take this shape and i want to display it let's go ahead and i want to take this shape and i want to display it menu one.
So that's just what we're going to do inside here so as soon as we on hover one as soon as we mouse move over it we want something to happen and what that is is basically width we're going to focus on that menu one shape and i want to change the menu if i want to give it a little bit of a slide out look it's very fast i almost can't see it for the menu width we're going to start it off at 33 which is just like a square basically and we're going to go to 133 and we're going to step point five right so we can step it up and then what we're going to do is we're going to adjust the width of that width is going to grow until it becomes full and that's just what we happen to happen as we go.
Over it so when i slide over here you can see it goes but when i slide back right it disappears
Why is it disappearing because we're sliding over this bar so the first thing we do is hide it so that's it that's all we have to do and the slide out is very quick you might want to make it a little bit slower you can adjust this this step up if you want or you can slow it down a little bit but i kept fast i kind of wanted it fast okay so that's how we do so we hover over one to show it and then over this bar where it's going to hide them all then again the same thing here.Here showing it bar doing it so this of course is called hover 2. the same thing here go back into the design mode clicking on hover 2 we could see that hover 2 and hover 2 is the same except this time we are focusing on menu item number 2 and we're increasing the whip so that is all we have to do then all i did was duplicate this code here this code all the way from the hovers in the menus everything like here all the way from so we have five for the menus and we have five different ones.
For the basically hiding the menu then all we have to do is simply copy and paste that on each of the sheets that we want to so we have six different sheets we've got the admin we've got the bills we have the the dashboard we have the schedule and then that's it so so we have that so that's all we need to do five sheets actually so that's all we need to do to create this very very cool hover effect keep in mind that we're using of course labels activex labels other shapes a button would work a few other ones would work but we're going to use labels because the other ones have hover effects too but labels is the easiest one so that's what we're going to that allows us to run a macro on hover keep in mind that if something doesn't work it's probably because you're you're inside or outside of design mode right if you can't select.
Something it's because you're not in design mode if it's not working if the hover is not working it's because you are in design mode so keep that in mind this can be a little bit confusing so keep that in mind all right so all i did was just basically duplicate that on every single zip and then what i did is i also assigned a macro to the button right i assigned a macro here so that macro is also if we select on this and we click assign macro we see that we have something called menu icon go to sheet so i have a specific macro that will allow us to go to the sheet now we notice that the name of this is called dashboard we notice that the name of this is called bills and payments the name of this is called build schedule right so if we assign a.
Specific name and that name is exactly the same name as these sheet names down here right build schedule then all we need to do is just run a little bit of a macro that allows us to select any shape and go to the page based on the name of the button right it goes to that page all right great so how do we do that well of course that's with a macro if we right click okay and click assign macro we see that it's called menu go to sheet okay we edit that it's going to take us to a module called application macros application macros very very simple some things first of all we have menu hide this is the macro that we saw recently remember when we look in here.
And we saw hide menus remember those horizontal bars that's called menu hide so how do we get them to hide well back inside the module here it's very easy we're going to set the menu number is long we're going to run the menu number from one through five because we have five different menus and if you remember those menu shapes there they're named very specific menu one menu two menu three menu four and menu five okay so if we've named them all consistently like that we can do that with just the blue just in case there's an error we've wrapped it in on our resume next and on our go to zero and basically we're going to say use the active sheet because.
We want this action to perform regardless of the sheet we've got five different sheets that we're going to be putting it on so we want to make sure it runs on any sheet that it has so shapes menu and menu number one through five the width just goes back to 33. basically we could hide it or unhide it but all we're doing is setting the width back to 33 and what that's going to do is going to basically hide it under this bar so it's going to be hidden under this so if we move it over we see that they're all there but hidden right so if they all if we run that macro they all get reduced you see they're all under there but they're just simply under that bar and they're hidden there so as soon as we as soon as we highlight over they are sliding out very very quickly very very easily like that okay so it is that macro that gets tied to these.
Height shapes and we're going to just simply run this loop and then hide all the shapes accordingly but it's actually just getting reduced in width and then they're simply hidden behind that bar okay great then you saw another macro where we're automatically when we actually select on one of those shapes those icons that we're actually going to there's that test again we can remove that going back into design mode right we want to find it so we're going to select all the area and we want to know where it is so if you don't know where it is just use this or of course we can use the last one label one that's going to be up here if we can't find it label one make sure you're in design mode selecting on label one that's gonna select it right there and then we find it.
And then all we just need to do is just delete and it's gonna be gone that was the test one so when we assign particular macros two buttons and we name them very specifically we can then automatically go to that sheet with a macro so it is that macro that we're going to go to now so we can see here the macro of course is called go to sheet and we edit that it's going to take us right back to the application macro this one here menu go to sheet right that's the one the first thing what i want to do is i want to run the macro the one above that we're going to hide all the menus the next step we're going to take the application column this is the name of the button that called it and the name of the button is exactly that so sheets application caller activate that's all we.
Need to do calling out that sheet it's going to activate that very very simple so all we need to do is click on the individual icons and it's going to go directly to whatever sheets the name of it just make sure that we have the name of our shape the name of those icons is exactly the name of the sheet whether it's dashboard or whether it's bill schedule exactly as it and that's it that's how we create a really really cool very very simple hover menu we just need to hover over it and as we move down they all get hidden or shown or hidden and shown just like that and clicking on them is going to allow us to go to that individual sheet all right great i'm glad i got to show that part to you it's really really important because we've got a lot to cover next up what i want to do is i want to focus on this area this is the bills and payments right let's reduce that we don't need it back to 100 percent and we want to be able to create unlimited bills and then also.
Show them as paid or mark them as paid as they come up and of course be able to attach a document for that so when we assign particular macros to buttons and we name them very specifically we can then automatically go to that sheet with a macro so it is that macro that we're going to go to now so we can see here the macro of course it's called go to sheet and we edit that it's going to take us right back to the application macros this one here menu go to sheet right that's the one the first thing what i want to do is i want to run the macro the one above that we're going to hide all the menus the next step we're going to take the application column this is the name of the.
Button that called it and the name of the button is exactly that so sheets application caller activate that's all we need to do calling out that sheet it's going to activate that very very simple so all we need to do is click on the individual icons and it's going to go directly to whatever sheets the name of it just make sure that we have the name of our shape the name of those icons is exactly the name of the sheet whether it's dashboard or whether it's build schedule exactly as it and that's it that's how we create a really really cool very very simple hover menu we just need to hover over it and as we move down they all get hidden or shown or hidden and shown.
Just like that and clicking on them is going to allow us to go to that individual sheet all right great i'm glad i got to show that part to you it's really really important because we've got a lot to cover next up what i want to do is i want to focus on this area this is the bills and payments right let's reduce that we don't need it back back to a hundred percent and we want to be able to create unlimited bills and then also show them as paid or mark them as paid as they come up and of course be able to attach receipts or bills just with a click we can also attach bill so we're gonna go over to that now how do we get that in there how do we create it how do we create this really cool filter and how do we get them to display all right so that's what we're going to get to well of course everything you see here is stored inside our builds database which is located right here.
And it's basically a table we've got the id the vendor bill date status receipt name all the fields associated with that including the paid amount payment type notes and a formula associated with the amount due and the amount due is simply the bill amount minus the amount paid so that's where everything's going to be stored we have a unique bill id so back inside the bill payment screen along we've got some hidden columns as you may have seen i want to know if we're going to include the paid or clone this particular true or false is going to be helpful for when we want to search bills right when we include we can include or exclude paid and close so if we decide.
We're going to exclude paid and close we're only going to show the open bills here otherwise we can select on this it's going to show all the bills so that's the true or false option and that's going to be located in b2 i want to know the selected bill id that's going to be based on the selected if i select a different bill i want that bill id to change i also want to know the row that's associated with that bill that is the database row that associated with that and it's located here so that means that bill id number three is located in row number six as you can see right here and in order to get that we need to create some named ranges some dynamic name branches that.
Will grow as our data grows so if we look in the formulas and name manager and we see that we have bill id we're going to use the offset formula starting in the header a3 and then offsetting one row below because we don't want to include the header we're going to count all the values from 3 all the way to the last possible row we're going to subtract 1 because we don't want to include the header row that's going to create a dynamic named range and we're including the header row because we don't want any errors in case there is no data so including the header row is important for that case so along with that we also have a few others while we're here we have bill amount due i've got.
A name range for the amount due i've got build the entire it's going to encompass the entire data that build due date we're not going to use the data in this training so we're going to use the bill due date i want to know one for the due dates i also want to know one for the vendor name okay and also i think that's it for the bill so as far as the bill in this table that's it that's all we're going to be focusing on here so if i want to extract that row i can use the match so back into that i want to extract that raw i want to know that it is on row 6. to do that we're going to use the match formula we're going to match whatever is based in b3 which is the id we're going to run a match based on the named range bill id we're going to add 3 because we want the actual row number.
Our first result row 1 that we want is going to be on row 4 so we want to make sure to add 3 meaning our first one is on row 4 bill id one is on row four i don't wanna return one i wanna return the row number so we're adding three if you've been with me for a while you know this the next bill id i also wanna know the next available one we're gonna use the max formula based on all the bill ids as long as our bill ids are numerical we can extract the max and then basically i want to add one to that that is going to let us know our next available id if there's no data at all and there's could create an error and therefore we want to set the default to sit bill id number.
One and we can use that on if air so that's why if there's an error that means there's no data and therefore we're going to set it to one i also want to know the selected bill rule we've got some conditional formatting here and i want to make sure that that format shows so the selected road 10 or 9 i want that displayed right here located in b6 if we highlight these and we go into the home and then we go into conditional formatting we're going to find that we have three different conditional formattings the first is based on that selected row where we're going to get it that dark green white font in bold then we're going to color some rows based on making sure that there's.
A value in e7 and then we're going to give it that white color for even rows and for odd rows we're going to give it that color green that's how we're going to get this alternating row look this professional look great so next up i want to know if it's going to be loading or not right when we load it that's going to go to true and back to false i also want to know the vendor row what is the vendor row well we have a list of vendors here i want to know the row that's associated 4 5 or 6 right so to do that we of course we need the named range so we look in the formulas back in here and now we have something called vendor id and vendor name right so if i'm going to be looking based on a vendor name we have the name range based on that vendor name and we have one for vendor id.
Because for example i want to make sure that we're using a vendor that's on our list so if i create a new bill and i select a vendor from our drop down list of vendor names that you saw this is a data validation based on our vendor name so we look under data and we see that we see that that's based on the vendor names that named range we just saw so what i want to do is i want to know what role i want to make sure it's the correct vendor so to do that what i'm going to do is i'm going to run a match based on the vendor row that vendor row is based on a match this is our next vendor id we'll get to that next i want to know the row that is associated with that vendor we're running a match based on what is in j4 and i want to know the vendor name right so the vendor name so that's.
All we have to do based on the say we can change the name the row wants in because this is where we're changing the name in j if we j5 however we're simply going to look up the one in j4 we're going to add three of course because our vendors also start on row four and that is it that's all we have to do to make sure that we here's our vendor row starting on row four okay i think we need a name and title here don't we yes we do let's go ahead and copy this group here this is our name and i'm going to put that over into the vendor list at least we have something there we go we know what we're dealing with all right so we've got a list of vendors here and we know we can extract a row directly from here to make sure that we have a correct vendor and i also want to know the next vendor idea if we want to add a new vendor id we want to be able to do that so we.
New bill and we can add a new vendor just simply by saving this vendor saving it so if i want to add in frida fred's wife freda fretters and then i want to do give an address one two three four five six that's the long address main street i want to be able to save this vendor right so all i need to do is just click save vendor and we know the next vendor id that we've got a fade out that's gonna say vendor save so we know it's been saved and then we'll see back in the vendor list that that's now been saved we now have frida freder's here okay great so we're saving the vendor so we can save the vendors we can also save new bills we can create brand new bills we can re-enter different vendors and we can also enter a bill date we've got a pop.
Up calendar shape based pop-up calendar we can set the terms based on the bill date which is going to automate the due date we can set the pay date if we want we can set the payment type if we make a payment type a reference number if we like we can add a bill amount how much the amount that's important and then also the paid amount and then we can of course as mentioned we can attach a receipt if we want okay great and adding some notes so all we need to do then is just add some notes save that bill it's going to save that bill it's going to automatically add that bill to the list and we can display that okay great so how do we do all of that.
Well that's going to come in a module so let's go over the module that's the one i'm going to focus on is the bill macros module so that's the one we want to focus on and of course we've got some variables here we'll be going over those as we'd have the first one i want to show you is this list load remember we had some filters here and we'll be able to load that list dynamically we're able to search on status right so if we want to search for status or maybe we want to search for a specific vendor maybe we only want to show lisa here so we only want to show lisa perkinson clearing that out or maybe we want to search by notes right if we want to search only by notes i want to search anywhere by notes it's going to show only those of course bills that have tests in the notes so we have a dynamic search filter and we're able to clear the filter along with.
Of course adding only those bills without that so we've got a lot of different filters here so we can do a whole lot with that all right so how we going to perform this well the first of all this is of course based on an advanced filter we want to filter these records dynamically we're going to we also want to have that criteria what are we searching by bill id vendor status reference or any of those so how are we going to do that well the best way to do that of course like i mentioned with advanced filter and it's going to be based on our original database located in the bills.
Database here and we're going to set some criteria now we have more than one criteria possibly why is that because we're going to be filtering based on certain status if we uncheck this we are going to exclude anything that's paid and we're going to execute anything that's closed and that's going to be based on our admin screen so if we look on our admin screen we're going to be excluding anything that's either here paid in full right fully paid or closed so those are the ones we want to exclude there so how we going to do that well that's going to be in our bills database so here was here's our criteria right here we have a vendor of course it's not empty and but maybe we.
Want to search for a specific vendor so back into the bills and payment if i want to search for only nancy right and i only want to include so that's going to be only nancy and only those that are not paid and not closed so that's going to be set some criteria so let's take a look inside of radius now we have some criteria here we have a vendor and it's nancy smith both the same and we have does not equal fully paid and does not equal close so that is our criteria that we are setting up this time and it is that criteria and we can link that so all we need to do is simply link it with the original data however we notice that also our criteria type is also dynamic this.
Is linked to bills and payments f4 meaning it's dynamic sometimes we're searching for id maybe we're searching for notes so on and so forth so when we link this cell to f4 whatever we put in f4 is automatically going to be set so back into the bills and payments if i clear it right and i show let's say bill id or anything like that and i show paid and close here with now we're going to be searching for a different bill id so if i'm going to search only for a bill id let's say bill number one i only want to locate that bill based on the id and we're going to include any paid or.
