How To Create A Fully Dynamic Userform In Excel With A Single Macro [Full Training + Free Download]

hello this is randy with excel for freelancers  and welcome to the dynamic user forms   in this video i'm going to show you how to take  this incredibly simple user form with vba and   automatically customize it to any type of table  you want with a single macro and a single form   that's going to be with any sheet any tip and  include all different types of sub tables as   well it's going to be an incredible training i  cannot wait to share with you so let's get started all right thanks so much for joining me just as  the title said dynamic user forms today i've got   a really incredible training what i'm going to do  is i'm going to take this user form basic in its   sense as just as you see it here vba is going to  take on the rest vba will automatically create   that user form and make it fully editable and  customizable and you're going to be able to   save any data and changes in there simply with  a one click it's automatically going to be just   that simple not only that it's going to be able to  use for any single table you'll use the same macro   the same form for any table you want it's fully  customizable to be able to handle pictures we're   going to do it all from you every step of  the way with you right here so i got a lot to   do now if this looks a little bit familiar  just a few weeks ago we created the one click   invoice pop-up here this quick view here was  really cool we did it with the same data table and   this allowed us to use shapes and it was really  cool it basically was one directional meaning you   could view the data but you couldn't change any  of the data there and i thought well why don't   we do this in a user form sense so that's just  what we're going to do today we're going to take   that same data but now we're going to make it  into a user form we're going to be able to edit it   change any field we want tab through just as we  use any field and we're going to be able to also   view customer invoices and make save any changes  we want to that data which is to click and that   one single view here is going to be to be able to  be used in any type of form will be it'll be fully   customizable that means if you want to change the  row limits maybe you want six rows available it's   automatically going to be able to change just like  that you can also change different things such as   font and things we're going to get into that if  we want to do that and also we've got a whole lot   more so notice now we have six rows of data here  so fully customizable form we'll also be able to   customize a background picture onto that form if  i were to change it to this darker blue background   form it would automatically change within that  so we'd now see that darker blue background   really really cool custom user form that we're  going to be able to share with you today so i'm   really happy to share that with you there's so  much i want to bring you today so we're going   to get started right away if you do like these  trainings i hope you'll stay with us for each and   every one as we bring these to you every tuesday  absolutely free in fact you can download this   workbook using the links down in the description  either with your email or with facebook   messenger of course if you'd like to support  us i always appreciate that so many ways to do   that one of the ways is to grab yourself one of  our 200 pack of workbooks that's 200 of my best   templates available for just 77 i'll include  the link down below and that's gonna have some   great templates too and you can get started on  that right away also another way is our patreon   platform i take these applications each and every  week and i update them based on any features you   might want to request or any fixes that you have  or maybe you want me to focus on a certain area   i do that all on patreon along with downloadable  pdf code books which help you sort through the   code and organize the code in these workbooks  also i have early bird specials downloadable   videos and a whole lot there on our patreon so  i hope you'll join us there okay great let's get   started on this training so basically what we want  to do is i want to have a single very basic user   form i want to create this user form dynamically  through vba so that it's customizable based on   the data that we selected so this row will capture  all of the data inside here including the pictures   and i want to place them accordingly if we decide  we want to show customer invoice tables then i   want to show that if we decide we're not we're  going to remove it and then only the customer   information will show up so how do we do that so  i'll show you a few ways to do that okay so some   of this if you saw one of the videos earlier that  quick click up some of this very very familiar   basically we have a list of worksheets so we're  not going to go over too much of this setup screen   i've got a list of worksheets i have this has been  changed we've got the background picture we can   browse for the background picture of our form make  sure that that's a jpg so that way this browse   right we only want jpgs i don't want png pictures  there okay we also have our row limit in other   words row limit is how many fields do we want how  many rows in a single column so if i put this to   20 right a very very high number and then i decide  to click in here that means all of my fields are   going to be in a single column right all of them  here are going to be there so but how if i want   2 or something i can do that so let's say we set  this back to 5 something more reasonable and then   we're going to get we've got 10 fields so we'll  get about two columns and that's what we want   okay so we have that that's fully customizable  if i want the font size i think i need to set   this the font size i'm going to put that in a  variable here because i didn't set that up i   want to do that live time with you so we'll call  this font size okay we'll create a named range   for that and then we're going to create that and  then all right so what i want to do now is i want   to also say okay so for different sheets we've  got a listing of different sheets we've got the   header row i want to know where the headers are  located on row 2 here in this case or in this one   row 2 here invoice item so we've got the header  row in this row 2. and i also want to the start   column right if i let's say i decide on these  customers here i don't want to include that   customer id right so if i decide i want to start  it off in column two so maybe i want to do that   so once we do that id is going to be excluded  it's going to start notice it started with   customer now so it starts so we can then we can  include or exclude the data that we have so right   so that's a really nice feature that we can do  that we may not want to do that so we also want to   know the end column number what is the last column  number of the data we want we're going to use 10   for our customers and invoices is there a picture  and that means in this case there is a picture   right if we take a look at our customers here our  last column column 10 contains a picture so if   it's a picture i need to make sure that we notify  vba that this column is a picture so that we can   then differentiate the code based on that because  it is a different field type right it's not a text   field it is now a picture field so we want to  say yes this is a picture and we have a list of   all of our headers based on that and there's a  picture here if it is where are those pictures   located right what folder are those pictures  i have those pictures right here located here   inside this field and so all the pictures are  located in this so we want to put make sure we   put the location the file path of that picture  and i want to place that directly inside here   also are we going to link a table if we're going  to link a table in other words our customers we   have another table i want to link our customers to  invoices so i'm going to put invoices here and now   what is that linked id in other words what is it  that is going to link our customer sheet to our   invoices sheet so if we take a look here we have  our customers here we have a customer id here in   our invoices we also have a customer id so that  is our linked id so that's what we need to want to   link the two up so inside our setup screen we just  make sure to put in that that is that customer id   whereas in our invoices sheet right if our  invoices are here we have an invoice id and then   we have specific items that belong in each invoice  what is that linked id that linked id is the   invoice id that way when i select on a specific  invoice and i click the mail i want to show   all the invoice items that are on that specific  invoice here so that is that invoice number that's   that linked id so we understand that so we want  to do that and i've done that just for each of the   tables now there's some information here basically  these are the sheet headers and this allows us to   have drop down list of all the customer fields  drop down list of all the invoices or drop down   list of the invoice items notice that we have  those drop down lists when we put them here we   want to know which fields are which right so here  i need a drop down list of all the fields here   customer so that drop-down list has to come from  somewhere and it's coming from here so all of this   is basically allows the setup now if you want a  little bit more detail on how this is inside our   one click form there that we did a few weeks ago  i've created more detail but this time i really   want to focus on that particular user form that  we got that editable user form that we can now   edit records on it now another reason why would  you want to do that if you can make changes here   why would you want a user form and there's a great  reason why you when you release these applications   or you send them to your clients your customers  your co-workers right you may not want them to   change this data you may only want to change it  through a user form because when they change it   for user form one you can make sure that they  don't clear any rows out right if we decide to   delete customer and they try to save it you can  say hey you make sure the customer name is open   so that way they cannot change the original  data everything happens through a lens of   that vba it is vba that checks the validity of  your data or checks that the dates are accurate   or it checks everything before it actually saves  it to the vba so you've got a really great why why   you would want to do this so then what you do is  you simply protect all this data you allow them to   make selection changes but you don't allow them  to make changes you protect this sheet so they   cannot make any changes to this data unless they  go through this then when they click save you run   all of your checks to make sure that the data is  accurate that everything is accurate and then you   can then simply offer that to them now maybe  inside our patreon what i'm going to do is i'm   going to put a little browse button here so we can  update the picture i think i'll do that so that   would be a nice feature i'll put that on patreon  so we can actually change the picture notice   it's just a static picture we can't actually make  those changes so maybe i'm going to add that in   all right so we have that here and we're going  to be able to do that so we understand why it's   important for us to separate the original data  from the end users so that we can protect them   and keep that data integrity intact okay so we  have that so how do we go about doing that well   the first thing what we want to do and also notice  something on a user form one of the reasons i   often don't use user forms is simply because i  want to control the placement of that user form   and it is tricky and notice that the placement  of this user form is always exactly on the first   column and it is always exactly on the row below  and that's really important it was important for   me because i want to do that so regardless of it  so i was able to actually locate some code and   i'll share the link with you that's going to help  us place that so i was not smart enough to figure   out that's pretty tricky quite tricky because  it's based on every screen is different so but i   was able to find a little bit of code that helped  me and i included the link for that all right so   that's one of the reasons so now we can use  uniforms and we can place them wherever we want to   very very helpful also continuing on so we have  the user form so all we need to do is basically   start out with a basic user form in fact we  probably don't even need this header here   but we have it here available to us in other words  we could add vba but i wanted to start out our   user form with three components that's all we have  we have a title we have a save button here and we   have a cancel the size of this form means nothing  okay it doesn't mean anything it is vba that will   make the form size dynamic so we don't have to  worry about that so all we have to do so we just   have some code that we're going to help us do that  so we're going to get into the code now but the   first thing we do with that code is when we select  it notice we've got this little pencil icon it is   when we click this pencil icon that it launched  the macro so the first thing we want to do   is on selection change is display that pencil icon  so we have here now notice the name of this icon   on each sheet is called edit button edit button  and i have the same one on whether it's invoices   or customers it is the same one called edit button  is this edit button that i want to display in the   first column of the selected row so how do we  do that well that's based on selection change   going into vba alt f11 is a shortcut will get  you there and we have some just a little bit of   on sheet code here um the first thing i want to do  is when somebody deactivates the worksheet or when   they activate another worksheet i just want to  hide that edit button what that does is it forces   them to select another row when they come back so  that's kind of important so it's really simple so   in the same code that we're going to  use pretty much for any sheet you know   you if your columns go greater than z right if you  have larger columns you want to increase this but   a3 to 899 and we want to make sure that a  contains a value right if there's if there's   no value certainly we don't want anything to  happen so if we scroll down here certainly we   don't want anything to happen only when we have  a containing value do we want something to happen   so once we have that then what we're going to do  is we're going to first thing is i want to take   whatever row that they've selected and i want  to put it in the setup e5 and what that's going   to do is trigger some conditional formatting that  is so first we're going to take that row and put   put it directly inside e5 here next up i want to  contribute conditional formatting trigger that   so if we look in the conditional formatting and  manage rules basically the rule here is edit if   the setup e5 equals the row we're going to give  it that dark blue and white bold font relatively   simple here so that's going to trigger the  conditional formatting next up what we're going   to do is run this macro show edit icon this macro  is located in our quick view actually it should be   save data quick view form we're going to call it  quickview form probably should call it dynamic but   basically it's a quick view form and what we're  going to do is we're going to run this macro   called show edit all we're going to be doing is  with the active sheet notice that we can display   this on any sheet it is this same macro that  can work on any sheet and therefore we're using   active sheet shapes edit button make sure the  name of your edit button is the same i'm going   to place the left position at one i want that  in the first position right of our sheet so i'm   going to put it all the way on the left and the  top is going to be the active cell top and then   visible equals true basically we're going to show  that icon that's all we have to do i've assigned   a macro to that icon and it is this macro called  quick view form show so we only have three macros   that we're going to be focusing on today really  three essential macros one is going to be showing   this we've already gone over that one is going  to be not showing that form and building that   form and the other is going to be saving the data  in that form that is it relatively easy training   but we're going to go into detail of that okay  so on this macro the macro that we're going for   we have some private declare functions here now  this is part of the code that i did get on this   website and i'll go over this website right here  basically i found some code here right that we're   going to actually place it and it is located right  here in this form here this stack overflow this is   going to help us position so it's like a three  or four lines of code that i pulled up it helps   us position that form very very important so  private declare we're going to get the dc in   the library basically this is going to get us  some user information because it's based on that   user screen settings every user screen settings  are different all right different dimensions   different pixels different things so what we  want to do is make sure get the user information   so we're going to do that along with the device  caps right this is going to give us that device   information it's very specific to their own device  because it is that position that's going to help   us the reason we have to do that is because when  we use user forms user forms are always positioned   based on the screen right it's either the center  of the screen or the top or the left or the bottom   whether it's minimized or maximized so what  we want to do is really we want to focus we   want to start when we're positioning it's based on  that user screen settings whether it's minimized   and things like that and so this was the easiest  code that i could find that could help us to do   just that and it works really great i was really  happy with it okay but this is going to help us   get that all right and so continuing on so we'll  we'll come back to this just keep that in mind   that we've gotten those functions here now so  this we need to define the sheet name i want   to know the current sheet name i want to know the  back picture right that back picture as a string   if it's a picture field as a string the picture  name of that picture we're going to be using what   folder that's located in and then the full path  of that picture i also want to know what the   linked sheet is right that linked sheet if it  customers our link sheet could be invoices if   we're on invoices our link sheet could be invoice  items so that link sheet is going to be found up   right here there's linked tables here this is our  linked sheet so we're going to need that inside a   variable as well okay also what i want to do is i  want to know the linked id remember the customer   id or the invoice id what is that id and i want to  know the column list we're going to be running the   column list right i need to know that and i'll  explain what the column widths are going to be   for the x position this is going to help us with  the position of our form as far as the horizontal   the vertical position we're going to use those  as double that's going to help us with the form   positioning next up we have the text field right  i want you to be creating multiple fields right   we're going to be getting text labels images and  a list box so there's four different fields that   we're going to be typing in our user form here in  our user form we have here our labels which are   these labels here customer phone emails those are  labels we have our text fields so these are the   actual fields that we're going to be changing we  have our picture field here and then we have our   list box here so there's four different types of  fields that we need to create so we need to define   those right inside our dimensions and they're  going to be their ms forms text box label image or   list box and then what i need to do is i'm going  to loop through all the forms each time we create   those fields just like we do inside shapes in a  sheet each time we create them i actually have   to delete them right otherwise they're going to be  duplicated every time i create them they're going   to be right on top of each other and we're going  to be creating duplicates and triplicates and all   that so the first thing we want to do just like we  do shapes on a sheet when we're sketching we need   to delete basically i'm deleting everything other  than the title and these two buttons i'm going to   delete everything else so we're going to need to  loop through our form to do that we're going to   use this dim control to do just that all right so  then what we're going to be doing we're going to   be using a lot of positioning right so we need the  top position as a long variable because i need to   that top position is going to be changed as we  move down or if we change columns it's going to   have to go back up the left position is going to  move from left to right the bottom position that's   going to help us position our buttons at the  bottom and it's also going to help us identify   the entire height of the form i need to know the  row limit right how many rows in a single column   what's that limit that we're setting up inside our  setup screen what is that row limit located in e4   i also need to know the column count how many  right columns are we going to do and the row   count how many rows in there also i want to know  the active row what's the current selected row   that we've selected and what column as we move  through all the columns in a single row we need to   loop through those columns i also want to know the  detail row what is the detail row that detail row   is located right here is it the customer detail  row or the invoice right it's going to be in this   case five or six but if you have a lot of tables  it goes down here so we're only dealing with   those two rows right here the header row what's  in the header row we're going to need to pull   those labels when i create them those labels are  going to be dynamic notice that these labels here   customer phone email address city state zip notes  picture those are going to come directly from   our header so i need to know what row we have our  header on as we extract those values directly from   the header all right so we've got the header row  and also we want the data column right as we move   through and the start column and the end column  right it needs to know as we move through the   columns where we're going to be starting because  we're looping through those columns i need to know   the linked id is a column what i what column is  that linked id located on the last row we're going   to need that because we're going to run advanced  filters if i want to know all the invoices for a   single customer i need to know the last row of the  data we're going to run that advanced filter based   on some criteria we want to get the results of  that and get that last results row so that we can   bring that over so when i select a customer here  it is that information that gets displayed inside   our list box right here so we have that here and  then we want the last column last results row last   result column and last data column right i need to  know the last results and i want those results put   into a range those that list box results i want  to put it into range this range result range is   going to do just that okay so that's it for the  dimensions now we can focus on actually building   it up so the first thing we're going to focus on  the active sheet we're going to get that sheet   name and we're going to put it into a variable so  whatever that sheet name is customers or invoices   it's going to go in that variable i want to make  sure that they're not on setup screen right if for   some reason this macro is running while they're  on the setup screen just let them know that this   has to be run one of the other something other  than the setup sheet and we're going to exit out   the stuff i want to put that active row inside  a variable that's going to be the active cell   row i also want to know probably not for this  training the active column i don't think that's   going to be helpful i'm going to comment that  out i don't think we need that but i'll move on   this we needed this for the other training  that's not so important because we're going   to loop through all the columns so the detail  row is very very important what i want to do is   i want to take this sheet name and i want to find  it where is that found inside the setup screen   we're going to pull up all the information i  need to know where the header row is i need to   know what column we're starting on i need to know  what column we're finishing on so the row of this   detail is very important what row is the customer  so i'm going to look for that sheet name here   inside all of these things i'm going to find what  row using the find command i'm going to find what   row if it's invoices it's going to be row 6 if  it's customers it's going to be row 5. so we can   put that into a variable called detail row and to  extract that we're going to use the setup range   g5 through g34 notice that that's all here all the  way down here we're going to go all the way to 34   and just basically look for that sheet and extract  that using the fine column and then we're going   to base it on the sheet name we want the values  and we want the row i want to extract that row   once i put that wrong and put that into a variable  if for some reason it's zero please let the user   know that this sheet is not inside right if  they're on another sheet and they try to use it   to please add that sheet inside here right they  may need to add that new sheet maybe they want to   put invoice items or something inside this table  make sure that it is found it has to be found   once we know it's found then what we can do is  we can set some information first of all i want   to know the row limit remember we put that in e4  that's the limit of our rows right here located   in e4 that's the number of rows we have per column  per column right if we set that to just three rows   if i set it to three we're gonna get a very long  form which is fine and so you'll see here we've   got a very long way way too long form but you get  the idea it's perfectly dynamic you wouldn't want   to do that but you can so put it back to five so  this is a row limit i need to know the row limit   so it's the limit number of rows inside so we set  it back to five so we can count exactly one two   three four now we have four and then five here all  right so we have that limit here and now what we   want to do is we want the header row the start  column the n column are all required so we're   going to get that if making sure that we want to  extract all of that inside a variable so we're   going to be looking for that header row the start  column and the end column making sure that h i   and j of that detail row contains values so we can  do that here if the detail row h and d several is   empty or i or j in the detail or if any of those  are empty let the user know to make sure they   put in that information and exiting out of the sub  once we know that they have those we can put those   into variables the header row the start column and  then column all go into variables i also want to   know the background picture of that form and i  put that into a name branch called form backpick   in the setup screen so when you look here inside  the setup screen we see that we have a named range   called form back picture it is that we're going to  put that of course we can browse for that as well   all right so continuing on we have that inside a  string variable it's going to be that full file   path of that picture what i do want to do is i  want to check if the director of the back picture   is empty meaning it's not found or maybe just the  variable is blank in itself if that is the case   then what i'm going to do is set the setup then i  also want to make sure that k if k does not equal   empty we want the picture file name so in this so  let's go over that again if for some reason this   is an inaccurate picture path then what i want to  do is i want to clear this variable all right good   now what we want to do is we want to focus on  k so let's call this uh the picture path this   is going to be the picture path for a picture file  now k is where we're going to have a picture if we   decide we're going to have pictures inside our  notice that where is that picture located here   we're going to find that and i want to know  okay if it's found then i want to check   l is l an accurate folder right so we can do  that here if k does not equal empty then we   have set up then what we're going to do is we're  going to set the picture field to whatever is in   k and i also want to set the l i want to check  l set up l is going to be that picture folder   so the picture folder it's going to equal setup  and l so now we're placing the picture file name   right that at least the picture field name what we  have the field name this should be field not file   field a little more specific field name there  and we have the picture folder we're adding a   backslash onto that folder and then all we need to  do is add the picture file name to this and then   we've completed a full file path great so what  we want to do now is we want to set the initial   left and this initial top positions right on our  user form we want to set that left position to   10 a very you know basically just a little bit  off the border right so what that's going to   do is when we create our first label here you know  label is going to be 10 pixels off the left notice   the customer they're slightly off the left i want  them text pixels and i also want to push them down   how many pixels from the top are we going to have  that first our initial top position is going to be   30 30 pixels from the top of that form also what  i want to do is if there's any existing remember   i said we need to clear all the labels and the  text boxes and the list box list boxes and the   pictures and pictures okay so we want to clear  everything out of the existing form just like we   do on a sheet and we're clearing out we want to  clear everything else so we're going to focus on   this dynamic form dynamic form is the name of our  form that's the name of our form that we sign if   we want to assign a name all we just need to do  is double click here and go into the properties   and we see that this is the name of the form here  so once we have the name now we're going to focus   on again using this control to find this controls  so for each control in dot controls meaning all   the controls on that form we're going to check  i want to know i've signed the specific names   and i think a form contains the name we've done  the same thing with shape but now we're doing it   with our form controls so we're going to  check if the name of this form contains txt   is greater than 0 then we're going to control  remove we're going to delete all the text boxes   we're going to check for labels if it contains  the word lbl the string lbl we're going to   remove the labels and what this is going to do is  going to allow us to remove everything but that   header remember we saw that header inside here  notice our header here is named form header so   it doesn't follow any of those so this will not  be removed because it doesn't follow that pattern   right the picture picture if there's anything  that contains pic those are going to be removed   and also anything that contains list box is  going to be removed we're going to remove   all that so basically at this point we just have  a blank form right so we've done that we're going   to go through all that now what we're going to  do is we're going to set those initial fields   for the data column so what i want to do now is  i want to loop from our start column whatever   is 1 or 2 or whatever it is all the way to our  end column this case 10 right i'm going to loop   through that so we're starting that and what we're  going to do is we're going to extract the header   we know the header column the header row header  row is located right here so we know that we've   already put that into a variable so the first  thing what i want to do is grab the label of that   so we start our loop for the data column equals  from the start column to the end column and then   what we're going to do is we want to know is it  a picture field if it's a picture field do alt   do something like here if it's not a picture field  do something here so what if it is a picture field   well if it's a picture field it would be a  picture field if we're on the 10th column here   this is the picture field we know it's a picture  field because j our header is the same as here   our picture field here picture that's how we know  so we're going to match that if the picture field   name equals active sheet cells the header row and  the data column meaning this says picture and this   says picture we've defined picture field all the  way up here right up here this picture field right   here we've divided in k right so we know that  there's a match so now what we're going to do   it is a picture field so we're going to set the  picture name we're going to pet picture name to   whatever is in the active sheet the active row and  the data column this is the name of the picture   so it is in this case uh fred dave or peter  whatever mary that is the name so all we need to   do is combine that name with our picture folder to  get that full file path that's what we're going to   do if the picture name does not equal empty then  we're going to set the picture path equal to the   picture folder and the picture name we've combined  the folder in the name to create the full picture   path all we need to do is check to make sure that  it is an accurate path and that that picture does   inside that path if the directory picture path  vbdirector equals empty that means it's inaccurate   then go to next field we're going to skip  everything and we're going to go all the way down   to here next field going to go to that next field  if it is accurate then we can do the following set   the label field equal to i want a label for that  picture so i want one i want to create a label and   then i want to create an image so the first thing  what i want to do what i mean by label let's just   take a quick look just says the word picture  on it very simple if we take a look here it   says picture here i want that picture to show  up whatever's inside this header right here i   want to place it into a label so we're going to  do that with following codes we're going to set   the label field equal to controls right we're  already in the dynamic form add we're adding a   brand new control we're going to call the type is  going to be a forms label one this is the type of   of control that we're adding this is the name  of the control that we're going to create we're   going to call it label box remember we're moving  everything that says lbl so we know it's going   to be removed we're going to call this label box  and then we're going to give it a unique going to   give it that column number that gives it a unique  name making sure we have a nice unique name for it   once we've done that i've created it now what we  need to do is simply place it so the label field   equals the top top position font size is dynamic  because i'm going to make this probably dynamic i   probably should do that let's do that now how do  we make that font size dynamic let's take a look   inside here we'll add that variable up here we'll  call this font size and then as long and what is   that font size let's set that up because i  didn't do that yet it's kind of a nice idea   want to put it down here anywhere anywhere about  here is fine it's right here font size it's going   to equal what it's going to equal set up somewhere  about here setup dot range and where is that font   side dot value let's set that set font size so  what is that font size what field is it located   cell located right here font size e6 e6 is where  it's located so we can place that directly inside   here e6 so now we have the font size so now  we can set that font size up if we want that   to become variable we can now change that over  to down here so where are we here right here font   size 11. so now we can paste that in right here so  now it's going to dynamic font size okay make sure   that that works and there's no issues at all here  and then we can set that up so we see now it's   perfect looks good if we want to change it to 12  or whatever we can do that okay so that's just for   the picture though so we're setting that up and  so the first thing i want the top position to be   the top position the width is going to be a we're  sitting at standard width for that the font size   we saw already the left position we're setting a  specific left position here so we have that the   based on the left position this left position will  change as we add more fields the back style i want   that back style to be transparent of that form  transparent no color and i want to set a specific   caption what is that caption going to be well it's  going to be based on that header row value caption   is going to be the data column set the header  label this will set that picture text that's   it now we've set the picture now all we need to  do is actually create that picture and set it up   okay so we can do that with an image field right  image field we're also adding a control but this   time we're adding control called forms image  one and we're giving it a name called picture   field data column again we have that picture pick  here that will be removed we're going to make sure   to include the words pic here that string and  we're adding the data column giving it a unique   name this is going to create that image but it's  not going to load it with anything until we do   this now we can focus on that so with that image  field we're going to add a picture dot pictures   equal we're going to load that picture what  are we loading we're loading the picture   path right and we can also add more features to  that so we're going to separate it by a colon   something i haven't done before and then we're  going to add that picture alignment it's going   to equal i want to center that picture another  colon here we're going to put in the size modes   equal we're going to put in the stretch right i  want that picture stretched so that's it so i also   want to add in the auto size equals false giving  a width and a height of 60 just giving it a square   making that top position the top position and the  left position is going to be the left position   plus 65. notice we have our label based on the  left position so we want to move it over to the   right so that left position is going to be moved  over to the right and that top position now what   i want to do is i want to increment that top  position right i've already added the label   i've already added the picture i want to update  that top position in for the next field increment   top position right for the next field get it  ready for the next field else what does it mean   else it's not a picture file else it's just a  normal file let's say non-picture field then   what we're going to do is we're going to set  the label for the non-picture label field is   going to equal again we're adding a label here  writing a label box and the data column and now   all we're going to be doing is that label field  setting that top position setting a standard width   giving that left position giving it that dynamic  font size i'll be updating this accordingly   font size the caption again is going to be based  on whatever's in the header row very very simple   relative we're just setting that label based on  whatever's in the value in that header row and   then also we have to create the actual text field  right that actual value of the text right as we   add things we need to create those text files here  so how do we do that well that's with a text field   control we're looking at forms text box dot one  and giving it a unique name text box and the data   column we add that and gives it that unique name  then all we need to do is simply place it and fill   it in so the text field the value is going to be  with active sheet active row and the data column   giving it that specific data as it loops through  all the columns we're setting the top position to   be on top and of course the left position is going  to be just off the left position of its label 65   pixels to the right we've got a width of 120 and  of course a dynamic font size here so we have that   there all right so we got the font size there now  what we want to do is we've got the top position   now i want to do as we add this brand new field  here we've added the label we've out of the field   i want to increment the top position but i also  want to know i want to set the bottom position   right that bottom position as we keep going  moving down what i want to do is i want to set the   lowest bottom position i need to know let's go  ahead and take a look at this form here as we   keep moving down what i want to do is i want to  know the bottom form right the lowest point lowest   field this picture is the lowest field that way  i know where to place the save and cancel buttons   and i also know how high to make that form i know  where to set the height so what's important for us   as because the top position is going to come down  then the top is going to go back up and come down   again so the top position is going to be changed  but what i want to do is i want to set a variable   that's going to be the lowest speed basically  what is the lowest field that at that lowest field   is going to be our bottom position that will  help us so bottom position we're going to check   that now if the top position is greater than the  bottom position then the bottom position equals   the top position basically what we do is we want  the largest possible value as we go through all   the columns this bottom position is going to be  that greatest value it's basically going to be the   lowest field the most bottom field that position  i want to know that so this is going to keep that   bottom position variable is going to keep track  of that for us okay now what i want to know is   if we get to our data columns right the row  limit i want to know the row limit as we   add it our first our row limit is going to  be what our first row limit is going to be 5.   as we move down let's take a look at this one  here as we move down here what happens when we get   beyond one two three four five i need to go to the  top next one right i want to go to six and then   seven so what i want to do is i wanna know are  we at five yet do we equal if we're equal to five   then what we need to do is reset that e to double  means the next one is going to be 10 and let's see   if this field had 15 fields here we would go down  five then we go to 10 then we get to the 11. so   i want to keep track of that limit that limit's  going to let us know when we need to start a new   column so it's going to be called we're going  to use this the data column if the data column   is equal to the row limit in other words if  we've reached 5 here and our row limit is 5   then and i also want to make sure the data column  doesn't equal the n column if it's the last column   then there's nothing else to do we don't need to  start a new column if we're already on the last   column but if we're not on the last column we do  need to start a new column right what do we need   to do to start a new column we need to reset that  top position all the way back up right i want to   reset that top position although that top position  is going to move down down down down it's the need   to go back up to the original for this job set we  need to go back up to the original top position   and we also need to move the left position  over to the right so there's a few things we   need to do so that top position is going to  be let's go to reset original top position   and also i want to move the left position  increment the left position to the right   increment left position to the right so for full  column right we want to make sure that the current   left position plus 210 210 is enough pixels to  accommodate both the labels and the text fields   and i also want to reset the row limit right  we've reached the row limit so that row limit   is going to be the row limit plus whatever  is in set of e14 so if the row limit was 5   before and this is 5 that new row limit is going  to be 10 so that when i get to 10 we're going to   reset it to 15 and so on however many columns  you have so it's going to keep setting it there   all right that's how we can have dynamic rows we  can just change it okay great so now what we want   to do the last data columns can equal to the data  column right so i just want to make sure that we   know i want to keep track of the last data column  this may come in handy a little bit later i'm not   sure if we need that on that so that's it so we're  looping through all the columns once we've done   that we've added all of our tables so as we do  that we've basically in this one we've added all   in this seven columns or seven rows whatever we  have here so we've added all that in here but now   what i want to do is i want to check are we going  to add anything else are we adding these invoice   tables so how do we know that well here if this  is not empty m if we know we've got a linked table   then we may want to add that linked table so  in customers we can add in that link table here   i want to know if we're going to be adding  in that linked table so how do we know that   well we can check on there so if let's go back  here check for the linked tables if the setup   m in the detail row does not equal empty we're  looking inside the setup we're checking for m   it contains a value i want to make sure that we  have that and n does not equal empty there's a   linked id here there's a linked table here then  we know we're going to be adding that lead sheet   we're going to be adding that links table so we're  going to set the link sheet to whatever is located   in m and we're going to set that linked id to what  is located in n so we've got those into variables   and now what i want to do is i want to locate that  i want to know where that linked data column is   right i wonder what that column is that linked  id so for example if our link sheet is invoice   and our linked id is customer id when we look on  invoice where are we going to find that customer   id we're going to find it on column 3. i want to  know that column and i want to put that column   inside a variable to extract that column we can  use this range sheet name right we're looking   at that sheet name header row 1 all the way  to the sheeting and the end column right the   header row starting in column one all over the  last column we're going to look for that linked   id and if it is found we're going to look for  it it's going to put that column in this case   three so now we have the linked id so we put  that linked sheet we want that linked sheet here   the sheet here and we just want to make sure that  we've actually found it so the linkedin it's going   to found in our original sheet to make sure that  we actually have that okay so once we have that   now we're going to focus on the linked sheet right  so if we're adding invoices for specific customers   here's our customers and our link sheet is this  invoices so what i want to do is i want to place   directly in y 3 i want to place that id so the  last row of data of that link sheet focus on   the invoices you're going to get that last row  if it's less than three we're going to skip all   this and go to node data because we have no  data if we do have data y2 i'm going to place   that criteria header what is the name that  linked data column and that header row what   is the name of that row i want to put basically  we're looking we've actually found it here this   customer id here or inside here either one i want  to take it and i want to place it directly into y2   once that criteria is here i want to place  the value in y3 so that's what we're doing   first we're placing the criteria inside y2 then  we're placing the value of that inside y3 that's   going to come from the active row the linked  data column i want to know what column it is   actually this fine sorry kind of messed up this  here the find right we can find it on two sheets   this one right here this sheet name we're actually  finding it here finding customer id here pulling   out the number five and placing this five directly  inside here okay so that's all so we're looking   it's we can find it here yes but we're actually  looking inside here so it's found on column one   so we know that the linked id here and it's we're  going to place whatever id is located inside that   column we're going to place that directly inside  here that's going to be our criteria so that's   what we do down here we're placing our criteria  here once we have our criteria and our header   we can then run our advanced filter so we're  going to start out at column a in the header row   through all the way through x in the last row if  you have larger data you we're just mom assuming   data ends at let's say x right but remember  you want to then increase this and reset your   data if you have a large data set you know change  this to different things but for our purposes and   training we just set it for simple data so we're  going to go all the way to x as the potential data   potential data goes only to x because our criteria  is going to go in y so we kind of run out of space   so potential data goes to x our criteria is  in y2 through y3 our results are going to come   to a2 through ax2 again our potential results are  going a a2 through ax2 make sure these headers   match right now we can include blanks fine blanks  is ok as long as we have blanks here too right so   make sure we have those so our results just start  in a and go all the way to ax but it's just blanks   so that's okay too so that's where our results  are going to come now what we do is we want to   check for the last results row our last results  row is going to be based on whatever's in column   a a that's going to give us the last results row  so in this case our last rules row are 8. so we're   going to check to make sure if those last results  are less than 3 we're going to skip and go to   node data right there's no data here if there is  data what i want to do is i want to get the last   results column i want to know what column this  is how many columns over so this is five columns   of data i want to know that column this is ae that  last column to get that last column of data we can   use dot range a72 and to the left column last  column of results so it's very dynamic right   we're putting these into variables so now we have  the last result column we know it's in first row   so i want to set now i want to set the results i  want to put that into a range this is a range that   result range is a range i want to take all those  results and i want to put them into a range all   the way from a a 3 all the way through a e so  starting on cells right we're already focused   on the link sheet so cells row three that's our  first row data all the way to potentially 27 here   27 is our first column of data aa is 27 so our  first column is 27 our last column is dynamic so   we're going to put that in there so that means  that both for invoices and then invoice items   the same thing notice our criterias also in y  our first results are always a so we've kept   consistency with our data so we have that so  we know the last row so what i want to do is i   want to then put that results into a range so our  starting cell is here range three row 3 column 27   our last one is the last results row and our last  results column this is going to set our results in   a range now we have that range range is going to  be very helpful i want to give that name a range   and name so the result range that name is called  linked table we're giving it a specific name for   that range so we can refer to that now what i want  to do is i want to know how many columns how many   columns of our results so it's simply the last  result column minus 26 this is going to give us   the number of columns in our result table  this will help us when we build that list box   i also want to know the row count the row count  is basically equal to the last results row minus   1. i want to know the number of rows of data so  if the row count is greater than 50 then the row   count equals 50. basically we're sitting just the  limit we're setting a limit you can set your own   limits but what we don't want is i want to set  that dynamic that list box i want that to show   only the results so we're just setting a  limit of all the data all the possible data   but keep that in mind that you may want to  increase or decrease your own limits i've set it   to 50. so if the row count the total results row  is greater than 50 then set the row count to 50.   all this is going to help us build that list  box now we've got all the data in our results   and we're ready to build that list box so create  that list box the first thing we want to do is set   the list box already dimension as controls based  on our form form we're adding a list box called   form form.listbox.one and we're giving it a name  called listbox remember we're removing any names   called listbox right here so we've already  removed it here for many previous creations   once we then create it then what we want to do is  then set the top position so if the top position   it does not equal 30 that means it's not at the  top right then the left position equals basically   this is going to add the left position unless it's  a new column right so what i want to do is i want   to make sure that that list box is always at the  top right i want to put it so we with the left   position so i want to make sure it's always at the  top and i want to make sure it's always moved over   to the next available column whatever that column  is so if the top position does not equal 30 then   the left position equals left position plus  210 we're adding the left position unless it's   already a new column if we've already added a new  column if it's already a new column why would it   already be a new column let's say let's take a  look inside our customers here let's say we had   invoices and we've exactly have our call let's  say our column limit here was five and we have   10 fields so we're gonna add five here we're gonna  add five here and then it's exactly the next field   automatically so we know that the top would be 30  already we're already on a new column so it just   checks to make sure we're on a new column now we  can focus on that list box the row source what is   the source of the data it's going to be that named  range that we just created up here the one we said   set name range up here now we're ready to result  here's the name that we set now we're just going   to create that row source this is our data source  of the list box data source okay we also want to   know the column count how many columns do we have  column count is going to be equal the number of   columns number of columns so how many columns are  we going to put in that list box it's going to be   based on our column count do we want to show those  column headers yes true that's the row before so   we're going to show what column widths are we  going to find now what i want to do is a column   width so i want to set it to fall width now the  column widths are basically a string of variables   how many it's like it looks something like a 70  point 70 point 70 point but all we need to do   is really add the 70. how many times do i want  to add the 70 in the semicolon i want to add it   basically the number of columns we can use  the repeat function in excel to do that i   want to repeat 70 and call in for every single  column count so this repeats let's see repeats   column pixel for number of columns so that way  every column is going to be the same they're   all going to be set to 70. and then the column  widths what are the widths of the column okay   so basically the call i put this into a string  variable once i have that string variable i'm   going to put it right in here and actually we  don't have the point so so it's going to look   like this without the points would work too but  i found that works without the points so it looks   something like this 70 70 70 it just keeps going  on so that's how we set all the columns to 70.   then what i want to do is i want to set the font  size i'm going to set this a little bit under we   could do font size -2 or something like that font  because i wanted a little bit smaller font size   minus two that might work too if it's if you want  to make it dynamic and then we want to set the   height what is the height it's going to be based  on the row count times 12 12 is basically that you   know the font and the row size that's going to set  a dynamic height for our list box that is exactly   the number of rows 12 is basically the number  you know the size that i've determined you know   played with a little bit and figured out 12 is the  right one the number of rows count how many rows   times 12 is going to give us that exact height  the left position of that list box is going to   be simply the left dot left position and the top  we're going to set the initial top i want to reset   the initial now the top is going to equal to 40  right that top position is going to equal to 40.   why is it 40 and not 30 well the top is because i  want to put in a little bit of title there above   that so now what we're going to do this is kind  of a strange thing this list box took a little   bit of you know like a split second to build  if i don't put in a weight now the width won't   be reset the width was basically just not it was  just kind of defaulting but i noticed that if i   waited just a little bit allowed time to build it  would just be perfect so we're going to put in a   little weight now one second and now that's going  to set the width properly so what is the width   of that it's going to be based on the number of  columns it's going to be 75 times the number of   columns we don't need this so that's going to  set the width so we've got the width the height   everything in the left position now what i want to  do is i want to increase the left position if we   have to do that the left position is now the left  position plus the width minus 180 this increases   the left position of the list box and this is  really important because i want us we're going   to eventually set the entire width of the form  what is the entire form with so the best way to   do is use whatever this left position is going to  help us set the entire width of the form a little   bit down on the code and also what i want to set  the width the width is going to equal to so we did   this already we did i did it twice just in case it  wasn't working we only need it once that's it fair   enough okay so let's check that make sure we're  all working good we set up so if you let me just   show you what happened let's see if it comes up so  because it's kind of interesting strange a little   bit frustrating but so i'm going to comment out  this let's take a look see if it works sometimes   it wouldn't sometimes it didn't so look at  this you see that that's isn't that strange   this width of this list box wasn't proper right  the oh that's kind of weird huh maybe one of you   smart people can tell me why this happens why is  it that a way now maybe it needs time to build   i don't know but the code is exactly the same  right but the width is not being set even though   the width property set but as soon as i take as  soon as i add just a split second of time for   that it works just perfectly so sometimes you  got to experiment you got to get creative here   and we have to know and how did i figure that out  how did i know that well what i did you know i   was having that problem and i just stopped the  code so i would stop the code here and i'd use   f8 in step and every time i stop the code and i  would go f8 it would work perfect so how is it   that it can work perfect when you're stepping  through the code but it doesn't work perfect   when you run it well usually that's a time issue  right that means something's happening too fast   so if i put in that little way now i noticed  it worked perfect so that's how i got to the   assumption that a little bit of extra time could  help so you see it works great so now what we want   to do is i want to add a list box title notice  that there's a title up on top of that list box   called invoice items so i want to put that title  right above that's why we're setting this at   40 not 30 because i want that title to go a little  bit above it so we're going to add that list box   title so we need a label for that so we're going  to set that label controls we're going to add that   forms label one we're gonna call it label list  box again it has that lbl so it can be removed   and with the label i want to set the top just  above 20. you know 30 is usually our top position   we're going to set this one a little bit higher to  20. the width is going to be the same width of the   list box right exactly the same the left positions  going to be exactly the left position of the width   so we're setting the width and left exactly  and we're going to text i want it in the center   so this is going to be centered directly above  that list box i'm going to give it a bold font   we can do font we can do this font size plus one  font so make it dynamic size plus one if you want   to and then the font size and then the caption  what is the captions going to be based on that   linked sheet what is the value set that header  label basically we're just going to put whatever's   in here so you know whatever's in your customers  customer invoices or whatever we have here you're   going to put that in so this would be invoice  items invoice items so whatever the title of this   that you put it's going to list that title there  so that's going to be the title okay great so   we've created our list box we've created our title  we've added in all of our labels and all of our   data all that's left is add the form now we just  need to build that form accordingly put the size   put the background and all of it so here's all  of our form details for the form specific all the   contents of the form are now complete first thing  what i want to do is add a caption this is the   active sheet name now what is the caption of that  form if we click on one of the data files here we   can see that the caption is that that update says  customers right here customers up here this is the   header this is the caption right here so we want  to give that form a caption and give it the same   it's going to be the active sheet name and i  want to set the original startup position equal   to zero this is the start position of that form no  initial settings specified forms can have middle   center you know upper lower but i want to set  this one to zero this form very important this   is where the code this is the code that i got off  that website right here this that this little bit   and this so basically this question on stack  overflow they got it from somewhere else some   french website they said and basically very very  helpful so it's going to turn basically pixels you   know here points to screen pixels and the first  thing we need to do is determine how many points   are there so that says one inch is going to be  about 72 points usually around 96 to 120 dpi   so we want to set that initial x position what is  that leftmost horizontal position of the screen   it's very important because if there is minimize  or the screen size it's all dynamic based on   the different user screen so this is going to  basically get the device get that left position   and divide it by 72 based on the number of pixels  there number of points in a in a width in an inch   so this is going to set up the leftmost position  we're going to do the same thing with the vertical   position that top portion and we're going to put  that into a variable called y position that's   going to set that upper vertical screen position  right that upper vertical then all we need to   do is measure so basically it's going to get that  upper left portion here so once we do that then we   need to just differentiate between that selected  i'm going to i want that thing to to this to   put just one point over to the right so  notice it's always going to be on the left   but i also wanted the top position to be right  under the selected cell so i've got to measure the   difference between that top portion and the top of  the screen so that's the differentiator on that so   to do that that left position this is going to  be the active window points to screen pixels x   one what was that one means i want that one pixel  on the left right the left position is simply   going to be one there's no column if you wanted  it based on the active column you could put active   cell column or active cell left would be okay but  i just wanted one pixel another so i want it all   the way on the left and we're going to multiply  that times the x position so basically this is   going to stay as 1 and multiply that times 1 and  then divide it by x position and that's going to   set that left position i just have it default to  the first column however the top position i wanted   to default based on the selected row so this is  where the top position again same thing here this   time the active cell top this here right here is  where we get the top position of the active cell   this right here will determine it when we multiply  that times the y position times y and then divided   by y position please don't ask me to detail that  because i'm not that smart something like that so   basically it's just differentiate what what  this formula does is the difference between   the top position of this and the top position of  the screen so the measurement between those two   is where we get the top position of the form so  that's basically all it's doing the top position   of the screen to the top position we need to get  the dimensions for that how it works beyond me but   it works fantastic so that's all i'm happy about  and so basically what we do i also want to add   to the active cell height this would be one row  down right i don't want it on the exact row that   we've selected i want it one row down that's why  we're adding the active cell height so now that we   have that we've set the exact left position we've  set the exact top position now all we need to do   is set the width and the height of our form to do  that i want to make sure if the top position does   not equal 30 then the left position equals left  basically we want to add the left position i want   to add additional if it's not already a new column  basically we just want to increase it to make sure   we're going to set that height of that form to  the bottom position remember that bottom position   very important plus 70. i need to add additional  pixels because i want to have space for both the   save and the cancel button if the left position  is less i also want to set a minimum width what is   the minimum width right if we just have one column  i need to have room for the save and cancel so we   do have to have a minimum width so if the left  position is less than 300 then set the width to   300 setting that minimum otherwise set the width  of that form based on that left position that's   why that left position was so important that we  keep updating it that's why when we add that list   box we increase that left position because that  left position that's going to determine the width   of that form okay so now what we want to do is to  set that form header the form header is one of the   few items that we already have we don't have to  create that and the name of that is called form   header so that's one of the few things that we  have and so with that we're already created it so   dot form header it's already created we're putting  a caption it's going to be the same as the actor   sheet name i want the back styles transparent the  left is going to be the dynamic form left and i   want the width to be the form the entire width of  the form and the text align here to the left so   we're just designing it to the left and then it'll  add a little pixels onto that great so now we've   we've created the form we've sized the form now  we just need to position the save and the cancel   buttons the save and the cancel have already  also been created and that's named basically   save button and it's called cancel button so  with those we can now set those up accordingly   so the save button the left position of that's  going to be the width of the entire form   divided by 2 right that's our center mark of the  form but i want to move it over a little bit to   the left minus 130. so i want them centered in the  form this is the center but obviously i have two   buttons so i don't want it exactly in the middle  so i want to bring it a little bit to the left   the save button what is about the top position  of the save button the top position is going to   be that based on that bottom position plus 10  right so we want to create that bottom position   because our form of course are the height of  the form is bottom 6 plus 70 but i don't want   them that far down so bottom position plus 10.  the cancel button is also going to be based on   the entire width of the form divided by 2 but this  time we're adding 60 because we want that cancel   button to the right it's going to have the exact  same top position as the save button do based on   the bottom position plus 10. so this is going to  set both the width and the top position of the   to the left excuse me it's going to set the left  and the top position of the save button and the   left and the top position of the cancel button  now what we want to do is we want to add that   background picture if it's available if the back  picture does not equal empty then we're going to   do is we're going to do picture we're going  to load that picture dot picture dot picture   because we're already inside the forms right we're  already in that form that dynamic form we're going   to load a picture based on the back picture and  then we'll set two different properties for that   picture i want to set that alignment to be center  right picture alignment to be the center alignment   i don't want to stretch that picture i want to  use the stretcher size mode that's it that's all   we have to do to set that picture so now what we  want to do is i just want to put the focus on the   first field when i load a field i want to make  sure that that first field gets loaded right so   so notice that first field gets the tab i want to  set the focus on that first field so they can tab   through it new los angeles there's no city here  let's add a city okay and save that okay so now we   have a los angeles so that works just if we select  it again it's there okay so we're going to set   that position there so let's continue back on  with the code here inside there so all we've   done here is set the focus to that first field  so basically that text box and what is that first   field it's whatever that start column is so we  can refer to the first field that we created here   based on the start column we're going to set the  focus of that sets the focus on the first field   always the last thing to do is show the form  show that form display that form it's about show   form that is it that's all we have to do we  don't need application i can get rid of that   not necessary all right very very cool so now all  we need to do is click this we don't need here   clear the contents of this shapes this is old we  don't need this and we don't need the super simple   text that's it so that's how we create it but how  do we actually save the data right notice that i   did it a few times when i click the save button it  saves any changes that we had so how do we do that   well it also cancels so let's take a look inside  the dynamic form we can view the code inside very   simple the cancel button is going to do what the  cancel button when click is going to hide that   form and it's going to unload all the data in  that form with just those two the save button   is going to do something it's going to run a  macro called save data it's going to hide the   form and it's going to unload the form so it is  this macro save data relatively simple that's   located inside this module and it's the last and  final macro that we're going to go over relatively   simple one so i need to know the active role and  i also need to know the data column the detail row   the start column the end column just as we did  before text field as ms form text box and control   as control so the sheet name is going to be a  string we also want to put that into a variable   okay so if the setup right i want to make sure  that we actually have a value in e5 what is going   to be e5 of course e5 is going to be located that  selected row without the selected row i don't know   what data to save that select bro very important  based on the row that we've selected okay once we   have that we're going to also want to know the  sheet name is going to be based on the active   sheet name and the active row what is the active  row that they've selected that's the selected row   it's going to be based on the active cell row  also going to be set up here sheet drive that's   fine too either one is fine so on air resume next  in case we have that the detail row is going to   be again we're going to look for that sheet name  we're going to find it this way if it's not found   we can escape out of there if that detail roof is  not found please make sure to set this up dynamic   that means that sheet name again that sheet name  is not found here so we need that sheet name here   if it's not found we can then exit out of the  sub okay we're going to set that star column   also based on i in the detail row and the end  column based on j right i need to extract those   values i need to know what that start column is  and i need to know what that end column is because   i've got a loop all the way from 2 to 10 saving  that data any changes to that data should be saved   to the table so we can do that so if the start  column equals zero or the end column equals zero   please let the user know to add vote to start just  letting them know we need both start column and   the end column now the rest is super simple for  the data column we're setting this is our variable   data column equals the start column to the end  column so we're looping through all the columns   and all i want to do is check based on the fields  that's where our unique fields are going to come   in handy right if it's not found why would it not  be found on air go to next if it's a picture field   a picture field would be different so there could  be an error here if it's not found right text box   data column 10 it doesn't exist tens of picture so  this would create an error so we're just going to   skip to next column if there's an error so all i  want to do is check to see if there's a change if   the value of that text box is different than  the value of this data then make the change   right so for example if i make a change here and i  change this to city mobile let's just say atlanta   right it's different there so then we know that  that this mobile and a different so we could   save those changes because we're saving them so  we're going to check for that first and so how   do we do that well we use an if statement if sheet  names sells the active row the data call value is   different than the text field value the value  of that text field if they're different then   we make the change then sheets cells act row data  column value equals the text field make the update   if it's different then we're just going to loop  through every column that is it that's all we have   to do very very cool this is a quicker training  but a very very powerful training i'd love to see   how you can use these let me know what ideas you  have i'll be adding more to patreon for sure and   that'll be coming up in just a week so let me know  what you got on there if you're not on patreon   please go ahead and sign up it's a fantastic  platform tons of great stuff going on i answer   all questions and we've got posts all the time new  content old content pdf code books early discounts   and tons of great things thank you so much for  joining us this automatic dynamic user forms how   you can create for any table amazing training  thanks so much and we'll see you next week you

Channel: Excel For Freelancers

Views: 154,159

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Excel Userform, Dynamic Userform, VBA Userform, Excel User Form, VBA User Form, Dynamic User Form, Create Userform With VBA, Customize Userform with VBA, VBA to make userform, VBA to Add to Userform, Custom Userforms in Excel, Excel VBA user Forms, UserForms in Excel, Make userforms

Id: Oa480YUaJpw

Length: 68min 1sec (4081 seconds)

Published: Tue Jan 04 2022