To handle every type of pop-up we can also show the records associated it for example we might want to show customer invoices associated with the customer we might want to show invoice items associated with an invoice this is called the quick view pop-up and it's going to allow to be used on any type of table any type of list with just a single macro and it's going to be fully customizable in the setup screen we're going to be able to we'll select the header row the start column and if there's a picture if there's a linked table and we're going to be able to do it very very easily this is going to be a quick training i hope you do enjoy these trainings i bring these to you each and every tuesday i always appreciate your support.
Many great ways to support us first you can start by making sure you've subscribed to our channel click the subscription button below and don't forget to click the notification icon bell that'll ensure that you get these trainings to you each and every week also feel free to smash the like button and comment below i respond to each and every comment i do appreciate that i've got incredible deals for you if you want to further your learning the best way to do that is either with our mentorship program where i teach you how to create your own excel based software for passive income and i take you through an entire 88 week course 132 hours of an incredible course.
And that's with our mentorship program my excel mentor dot com will get you all the details there also if you want to further your learning each and every week i created an updated feature request based on these videos so if you have a specific feature that you want added you want me to fix something or you want me to focus on an area i'm doing that in our patreon platform patreon is where it's at for just a few dollars a month i'm going to have that plus beautiful pdf code books of all the information we've got downloadable videos i've got tons and tons of other stuff on our patreon including previews of upcoming weeks feature requests and a whole lot more so i hope you'll join us there on patreon i'm going to include the link down below all right let's.
Get started with this week's training because i really got a lot to cover although this will be a relatively quick training compared to the others some are many hours this one may be probably an hour or so let's see how it is i'm going to walk you through every step of code now the idea is we want something really customizable so let's say we've got customer information here but maybe i want to show customer id notice customer id is in column a it's on the first row here so what if i want to show that well the best way to do that is just to set that column if i'm going to start at the starting column at column 1 right that means i want to show that customer id so all i need to do is just select a customer that customer id is now apparent so we now see the customer id.
If we don't want to show a picture we don't need to that picture's on the last row i just switched to column nine that picture is not going to show up so now when i go in the picture that picture is not because the last column is where our picture is column 10 is where our picture is located this is column 10 so it's fully customizable of course we can change the theme through a drop down list and the setup screen we can also have a linked table maybe we don't want to show the invoices for that customer so we just delete that right we don't want to show customer invoices so now we just click it and it's going to show our customers so this is really cool because it's fully customizable for example in our invoice table here we're showing invoices we will show up the order date customer id and so on and so forth customer name and we have the invoice items maybe we don't want to show the invoice items so in our setup screen we just remove the invoice id.
And or the linked table either one would be fine because it requires both so now going to invoice it's only going to show the invoice information and not that linked table so i'm going to show you how to do all this with a single macro so it's going to be really really great so we'll put those back on customer id and we'll put those back on linked id so let's go over this with you we have a just a setup screen a customer list an invoice list and an invoice item list so it's very very simple right there's not a whole lot going on in this but the value is huge because we're able to look at these records on a single click and look at them really in an easier way so.
Let's say you you don't want to create a specific form we do a lot of sheet forms for customers but you just want a view of from a list right we want to see what's who's that customer we want to get all the information from that maybe we want to see a picture how do we do that on just a single click well what we can do is we first need to set things up so what i want to do is i want to create a list of worksheets that's going to be really important because we need to select the sheet in which we want it to show up so we want to know where we're going to show up at right i only have two but if you have a large workbook you can put a lot of them in here right so you just need to follow this pretty much all you need is this quick view set up so you can create this yourself you can create this on any workbook you can copy and paste this sheet into your own workbook.
And the single macro and a few other small macros that help you organize it we're going to go over every step so you're going to be able to apply this to your own workbook so as opposed to starting with this workbook you can just take all this and i'm going to show you how of course you can download this workbook absolutely free using the links down below for you of course if you'd like to pick up 200 of my best workbooks i've got that available to you as well all right so we have a list of workbooks i'm going to show you a quick macro on how to get those we have a drop down list of themes so we can change the theme what we also have is i want to know the width of what is the standard width we can set a specific width if we want to this width is defined because we've got a picture right but we may want a default width we want to set that default width to less.
We can that's the width of this shape the width we want to set that up to a default and we always want to know the selected row the selected row is going to be highlighted so if we go in here we know the selected row notice the selected row is five so that is going to use conditional formatting but what i don't want to do is i don't want to put that row five i don't wanna put it on this sheet i wanna have a single sheet that applies always has that selected row so many times we've done conditional formatting where we put the selected row on the current sheet but this time we're going to put it in the setup screen that way you can apply this to if you have 100 tables inside a single workbook you can apply this single macro to every single table so it's really helpful.
Really dynamic and really customizable so you may not want red of course but you can easily change it to any color you want and it'll customize that so it's really really dynamic so we have a list of sheets and of course with the selected row here what we also have is a sample shape now we're going to be creating this particular background shape this background shape and what we want is a sample right so we want to sample we'll copy this sample into whatever sheet we needed it we also needed a sample close button here i want to be able to close it using this close button so i want to be able to turn it off just like that so we need that close button so i'm going to bring this one in now keep in mind that our close button already has a macro sign to it we'll be going over.
RELATED TOPICS:
That i need a sample label this shape label shape i need this sample because it is this label that when we create these we're going to be using these labels here these are all labels here
Right here and here and here so we need to be able to create that duplicate it and then also what we need is to have a sample field this here is our sample field it is this sample field that's going to get duplicated when we create these little pop-up quick view forms here now these are just for visible of course they're not actual forms we can't actually change and update data in there we would use a user form but i really like the idea of this now we could use a user form to do it but there's a lot more flexibility and a lot more speed when we use this if you just want a quick view you just want to get some information you want to see it it's really helpful now you also might not want to have it on selection you might want to put a.Little bit of icon here so that when you select a line it doesn't show up this doesn't show up but it only shows up when you select an icon maybe you want like a little magnifying glass here you could do that too put a little magnifying glass and only when you select the magnifying glass run this macro very very simple and very easy to do that too this macro right now we have it running on selection change event when we change we'll make a selection change it pops up you could just as easily tie this macro to a specific icon and that icon can be displayed on a selected.
Row so there's many things you can do that a lot of flexibility but what's really nice is the is that it follows it so no matter how long your list is you're gonna always be able to see that there's no picture on there's no there's no data there's no data right there's no invoice data nothing's gonna show up which is kind of nice and convenient right only when we actually have invoice data do we get something which is kind of helpful i like that too okay so continuing on so we have our samples those will get duplicated those are going to what's going to create our little pop-up quick view then we have a quick view sheet details this is where all the details this is how we set it up right so when we make that setup we need certain things i need to know.
If i want to display that quick view pop-up what sheet do i want to display and we have a drop down list of sheets what is our header row now what we can do is we can also create some type of help if we know the header row we can also automate the start and end column those can be automated through macros and it's going to help guide us so we know the column but of course we can change it so that can that can be done through a macro and i'm going to show you how to do that so but we want to know what row is the header our header row is in this case 2 right 2. so i want to know what row is our header row it's not the title it's the row where our headers are in.
This case it's going to be 2. okay let's see how many let's count how many times i say in this case in this video that's 3 so far all right so up to back in there so we want to do is really something really dynamic if i don't want to show all the columns if i only want to show columns 3 and 4 i can do that as well so just showing which whatever columns of data i want is there a picture if so where is that picture located here right and uh which of the so what we also want to do is once i know the start and end columns what i then want is the dynamic drop down list on all of the headers notice that the invoices right if i let's add it here.
Notice there's no drop down list here right here right there's no drop down if i add invoices here and this can come in handy and i know the header row here automatically it's going to be able to determine what are the header rows and we can then select if there was a picture here what field would the picture be in here so that's really the dynamic and i'll show you how to do that too so there's a few macros that are associated with the setup there's one single macro that's going to help us create this okay and then if we're going to be linking a table right so invoices we might have an invoice items and how are these two tables linked.
How is invoices and how is invoice items linked invoices of course has an invoice id now we go to invoice items we see that all these invoice also have an invoice id right because we have to know what associated so for example this would be our first item this is just kind of the way i did it our first item in invoice one would be here our second item would be here it's kind of strange usually they're all together our third item would be here so you get that it was just easier for me to create some sample data like this our fourth item in invoice number one will be here so it's.
All here and all we need to do is run an advanced filter based on a specific invoice id and then we get all of the items that are associated with that id we can then take those invoice items and bring them in to our pop-up form and i'll show you how we're going to do that so we want to know right so we want to be able to do that so how are we going to do that and of course we want another drop down list notice there's no drop down lists are created dynamically based on this type of table that was selected so that means if you have a large workbook with a large table these drop-down lists are automatically going to be based on the headers right so very simple.
Now those headers are going to be located right here as we create these notice if i delete this if i delete that and i just double click on our invoices again here it's going to automatically recreate and repopulate these are going to be drop down list customized drop down list based on this so it's from this list that i'm going to be able to pull that data validation if we look into the data validation here click on data validation we see it's invoice header invoice headers and if we look on here it's going to be customer headers data validation so we're creating data.
Validation cost based on that so as we add more for example if we were to put invoice items down
Here set a header row would automatically populate those invoice items although we're not using it in this particular training because there's nothing under invoice student items but we could easily create one for that too so how do we do all of this well first let's go over some of the macros that are located inside the setup screen then we'll get into the quick view so we're going to go directly into vba through the developer individual basic if you don't have that alt f11 will get you inside there we're going to start with the setup screen we're going to focus on some worksheet change events when the user actually makes a change to the worksheet we want to do.Something and if they make a selection from g5 through h99 g5 that's we're gonna start out g5 is here through eight so anywhere that they make a change notice that we have some things that are required remember i want to populate this list and i want to get those data validations if i know what sheet and i know the header row i want to populate the drop down list both in column k and i want to populate it in column n so how are we going to do that well the first thing we do is determine all of those headers and then what we want to do is put them in a list here then we'll create a named range and it is this named range that we're going to add here and.
Here so that's what we're going to do inside the code so the first thing we want to do is see when the user has made a change we also want to make sure that g and the target row doesn't equal empty and we also want to make sure h doesn't equal up to you that means we need to make sure there's a sheet we need to make sure that there's a header row right we need both of those things in order if we're going to be getting all of those headers i need to know what the header row is and i need to know the sheet for that so those are all required so assuming that we have those we can then move on so first thing what i want to do is i want to make sure that h is a number if it is not if that equals false is numeric h in the target was false then x to the sub all right we have to have.
A header row the header row has to be a numeric value i'm just going to put a note in here called header row must be numeric value okay so then i also wanted to mention the sheet name as a string you're also going to mention the header row as a long i need to know the header column why do i need to know the header column i'm going to be looping through the columns and getting all the values so as i go from column 1 all the way to the last column from the start column to the end column i need to get all those values so we need to dimension a variable for that header column sheet column i need to know what column and i need to know the first column and the last column as we loop through those columns i also need to know what setup column is what do i and i need to know the setup row what do i mean by that well inside the setup screen i need to know what column we're going to be adding those headers and i also need to know what row starting with row.
5 and moving down i need to know what column i'm going to have to look for those sheets in here i need to find the sheet see what sheet it's on once i find the sheet i'm going to know what column customers we know is on column equals column right column v is going to be 22 invoice is going to be column 23. so if i know that i need to know what columns so we have to define those and i also need to know so the setup row and as we move adding those headers down those rows we need to get the row so also we're going to set the header row we know the header row is in column eight so we're going to set that into a variable here h is going to take on our header row okay moving along the.
Sheet name is going to be located in g so this is our header put in header row and this is our sheet name worksheet name work and that's not the code name worksheet name sometimes we use the code name sometimes we use the actual sheet name okay so the setup column here's where we're going to find that sheet name i need to look for that sheet name how do i know that she name well it's right here it's going to be based on this right this here is going to be based on this right here we'll get into the macro that creates these but it's going to be basically a link so all of my sheet names are located here i need to find what column that sheet name is on so the next line of code is.
Going to help us find that column using the find command so the setup column is equal to range u4 through ab4 this is all of our sheets if you've got a large workbook you're going to have to expand this table many as many sheets as you have just expand this so i'm just simply looking u4 all the way through ab4 that is the range that we're looking in we're going to use the find the find command and i want to find the sheet name using excel values and axle i just want to find the column that it's in so we're going to use that as column okay we're going to set the initial set up row as 5 right so now we have the column here if it's found we also have the row here the.
Starting row is 5 we're just going to increment that every time we add it so all we need to do is range we're already we don't need to specify sheet because we're already directly in the setup sheet so we do not need to specify a sheet in this case and then that's four cells five row five right starting with five all the way the setup column all the way through 34 we want to clear the contents i want to clear any previous contents as we add them i want to make sure in case we change the header rows first thing we want to do is clear all the previous headers next up i want to set the.
Last column what is the last column well we're going to use this i'm going to go all the way to column 23. so basically we're going to go in every single if you have a large database that's going to extend beyond column 23 you're going to need to set that so we're setting the last comma right around here i need to fix that notice it should make sure that it's going to actually have data okay so w is column 23 so i'm going all the way to w here but my data ends at right around j so keep that in mind although a quick view should probably have some data on that but it's interesting that it shows up there so it's kind of cool so we have that just to show the customer we can show limited customers if we want to joe just a little bit we can show just a little bit you know just phone and email would of course be only columns three and four so that's kind of kind of a nice way to do.
That if you only want to show a little bit of data and that's just because we've duplicated it here it's founded here so we're gonna do we don't need two customers right three and four here we don't need so let's clear that out right we only wanted to find it on one location here you wouldn't want to have duplicate tables so we can show all the data right we only want to have one instance of one sheet down here not two instances as i've had here so we have that so we want to show the last column so what i want to do is i want to get the last column so i'm going to choose the last possible column and then we're going to use a single line of code to do that so the last column can be equal this workbook sheet name based on the sheet there and then based on the header row what is that header row 23 i want the last column with the value to the left this is.
Going to get the last column with a value a value and with automates based on autohotkey because i'm typing with i like to type in end with usually except when i use width inside the comment here so the first column what's the first column the first column is going to be based on the header row column one we'll just set that to column one that's the first possible right first possible so then we're going to say if the first column equals the last column then the first just going to reset the first column just on no if there's no blank columns right it's just going to set that first column to 1 just to make sure that's going to automate because i want to know what the first column is in this case if it's 1 of course we're just going to set it to 1. all right next what i.
Want to do is i want to set the default first column right if i in the target row value i'm just going to put set the first column default to 1. not not really an if and not so what do i want to do so basically inside the setup screen once i've created it and let's say invoices here and i've put the header on why don't we set the default to the starting column why don't we set the default to the ending column that's just what i've done here setting that default right notice invoices go to from one to seven so we're setting the default one we're setting the last one in case we want to add them all right if we do then that automatically adds last one of course you can change it maybe we don't want to show total maybe we only want to go to six so we can set all the.
Columns here all right so we have that it's going to set the defaults of course you can change them we're setting those two defaults in these two lines right here i is going to take on the first column j is going to take on the last column then we're going to run a loop now i'm ready to build what i want to do i want to build this list right here starting in row 5 going all the way down building the list so we can do that with a loop for for the header column it goes the first column to the last column loop rule all columns header i'll call them header so i'm going to take that header and i'm going to place it directly in the setup row in the setup column taking this value remember setup row is going to increment one by one by one this workbook sheet.
Name sells the header row the header column the value right header row value so that's the head the value of the header row which is going to bring those in so that's going to basically bring them in directly in here this once they're all there we can then create a named range based on that now if it already exists that named rage would have to delete it so if we look in the formulas name manager right and we see invoice headers here invoice headers here this is the name range you have to create if i delete that click ok close it's no longer there but all i need to do is just double click on here on make change here or here and if we go back into the name manager.
We see that it's now been created invoice headers here it is it's right back in is that change code on g through h that's going to make that happen this is the code we're focusing on so how do we do that well the first thing anytime we want to create a named range we certainly must make sure that that that any named range with the existing name gets deleted right because what will create a bug if i don't delete it if i try to create another one automatically by changing here or changing here it's going to create a bug because we haven't deleted the first one so the first thing we want to do is make sure now if it doesn't exist it would create an error so we want to wrap that in on air resume connection on air go to zero this is going to delete that name range.
Also keep in mind that named ranges cannot have any spaces but sheets can notice invoice items has a space between those names right but named ranges cannot have spaces so we certainly want to remove any spaces so we're going to re we're going to simply use the replace command sheet name any space is found in the sheet name we're going to replace it with an underscore and this is helpful because if i create one for the let's say invoice items it does and i put two in here it does create and notice it does create which is what i want but if we look in the formula.
Name manager we see that invoice item headers that name now contains that underscore so we replace that space with an underscore there so otherwise that prevents any bugs from happening so we have that there so the next thing once i've deleted it what i want to do is i want to create that name so we're going to use setup names add we're going again we're going to replace again here any spaces with an underscore using the replace command and underscore headers that's going to be our named range i want it to be equal to right where it equal to what's it going to be equal to basically all that range also on the setup screen cells five we probably don't need the word setup here because.
We're inside the sheet but then from the setup column all the way to the setup row minus one the address of that so basically the address of whatever this is the address of this row from five to the last row minus one but we have to subtract one because the four next loop would put it here on twelve but i really want to put it here just on the last one so that way it's going to create a named range we're going to set the address so we look into the named range here name manager this one right here editing that when we edit that we see that this is the address right.
Here that we've created the setup this is that formula this is the address that we've created once we have that address we can then create that named range so here's the address that we're using here equals address so that's the address of that so once i have that what i want to do is i want to add the validation now is where we add the validation into columns k and into columns in but of course any time we do add in validation we must first delete the existing validation so in two separate lines of code we are going to delete whatever is in k we're going to delete the validation whatever's in and then simply we're going to.
Add it in k and add it in end with the validation add the validate list alert stop formula we're giving a specific formula that formula is going to be equal to and again we're going to use the replace sheet name and the headers right so here we're adding that named range that we just created we created that named range here using this formula right here we're simply adding that both to k and whatever charge rows and n that adds the validation it creates a named range and we've done it all created default headers so with just this little bit of code we've done all of that great so i've shown you that so once we have the names all we need to do is just put in the information here and we can put in the link tables here we've already got our linked tables here so let's go ahead and i've got some macros in this setup but we've only got a little bit of on.
Selection change if the theme group first of all this is the theme group i've created a group of shapes how do we change themes well basically i've just created a group of rectangle shapes right here and it's based on individual themes right because i want to be able to change the theme right very easily if i wanted red i want to show red show up so when i select customers it is that red color that's going to show up so we can easily change themes but to do that i'd like to have a nice drop down list so how do i do that one selection change i want something to happen on the selection of e3 and that's when i want it to happen so what do i want to happen well i want to show this group of shapes basically it is these seven rectangles here i've grouped them together.
And called them theme group and called a group called theme group so it is that one that i'd like to display so when a user makes a change however when when it's up if we select any other cell i want it to be hidden so how do we do that well the first thing we do is on selection change of any cell on this worksheet i want to hide this group so we do that with here the first things but only if it's visible only if it's visible how do we know if it's visible if shapes theme group visible equals true then we know it we need to hide it so the first thing we want to do is hide it dot visible equals false hide theme group so now that we've hidden this theme group now what i.
Want to do is if the user makes a selection just count large is greater than one that exits them i should probably do this on another one too on the other one but we'll go ahead and i'll show you what i mean by that and that means basically i should probably that means when they select more than one cell nothing's going to happen that's what i want but if they select more than one cell here something happens so we may not want that right so create errors keep that in mind so continuing on now if the user makes a selection change on cell e3 only what i want to do i just want to display this group now keep in mind it's only going to display it i didn't add any of course you can add additional features that's going to actually make sure so in case it moves right we don't have a feature that could it's very easy to add you can challenge yourself or i can.
Put this on of course patreon but we may want to anchor it to this particular cell that's a nice little challenge do you know how to anchor this group to this particular cell right now look it's going to show up wherever we've dragged it over wouldn't it be nice if we can add code of course we can i've done it in my other videos no problem wouldn't it be nice if we can anchor it directly below even if the user moves it okay so keep that in mind that's a little challenge to you how do we anchor it to just right below cell e3 so moving on that's all we have to do so that's it for.
The selection change code now i've got some macros that are associated with the setup screen and i've placed them in the module setup macros just a few first thing what i want to do is i want to list all of the sheets inside the workbook first thing you want to do if you get something like this you want to list all the sheets if i delete this right those are really important we need that notice there's no more link we need those sheets so how do we do this well we just run a little bit of a macro listing all the sheets so if i run that macro those sheets i kind of put it on column b really we got to put them on column b so we want to make sure let's change that over we want to make sure to do that i think originally i had it on a so how do we do that well we're.
Going to first of all clear all the contents of the original row doing it like this clearing all the contents of the original range from b4 to b99 setting initial row sheet row for each weekly worksheet dimension the worksheet as a worksheet and the sheet row as long for each worksheet in this workbook worksheets what i want to do set up in column b now i've changed it sheet row value equals whatever name it is we're going to increment that row one more so when i run this macro a second time we now see it's now in column b i can delete column a we now see.
We have those work and they're now properly linked in because i simply linked them here how do i do that well if i know this is column 21 i know this column 20 and i know this is row 4. how do i link it and use a single formula to do that well i can do that using the indirect formula indirect b and the column which is what i want to show minus 17. so this column we know is 21. if i subtract 17 from that i'm going to get 4. 4 is the right row that i want b4 is where i want this would be b5 so how do we get b5 again all we need to do is take the column in this case.
It's 22 minus 17 is 5. so now we have to do is just simply drag it over and we do it and if we don't want those zeros maybe we don't want zeros to display all we need to do is go into the file right inside our options and go into the bring it over go into the advanced menu scrolling down here right and down here all we see is show zero in cells out of no value unclick that click ok and now we see that node zero is showing up so we can easily do that too okay so now we see how.
We get those sheets very very easily inside column b and that's how you get all the sheets all right now what about the themes right i want to be able to do two things i want to be able to select on this and when i select a specific theme i want the theme to change in this shape this shape is called sample back now how do we know the code for these well we can just simply run a macro to do that right if i don't know what the themes are we could do that with a macro so if i go into the developers and i turn on macros if i record macro or if i click here to record macro.