Create an Overdue Payment Reminder Letter for Research and Development Effortlessly

Overdue payment reminder letter for Research and Development

hello this is Randy with Excel for Freelancers  and welcome to the payment reminders with   penalties in this week's training I'm going  to show you how to create a fully automated   payment reminder system in Excel that  includes unlimited reminders automated   email reminders automated and customizable  penalties Dynamic variables with personalized   emails and best of all we're going to do  it all from scratch every function every   format formula right in front of your  eyes I cannot wait so let's get started all right thanks so much Jimmy I've got a  fantastic and unique training this week payment   reminders with penalties a fully automated  system to remind your customers of payments   that are overdue if that's not enough I have  packed this in to an incredible application   that we already created in the past few weeks  although this is a brand new training and you   will not have to know or have seen any other  trains before this however it is important to   know that there's already in this application  in which you can download for free a complete   invoice application fully customizable invoice  along with a reminder we have complete payments   here invoice and payments manager that was last  week so those trainings were completed this one's   going to be on payment reminders we're going to  be able to automate this payment and we're going   to do it from scratch so this is a sample I'll  be closing this and opening up a new one after I   show you the details of this so we're going to  be able to create this now you're going to be   able to take an invoicing application along with  payments I'm going to wrap this into automated   reminder so that your customers get an automatic  email so when we click check and send it'll let   you know if there's any reminders and of course  that means that there's no reminders going to be   sent out however if we delete something from  the log here and that means that payment can   go out so we're going to check and send an email  and gets automatically created now that email is   fully customizable lets them know that there's an  overdue notice for what invoice it lets them know   visit a customer name that there was an invoice  number six for 974 dollars how much the penalty   was it has been assessed and the totals that  they now owe how many days overdo that invoice   so that's fully customizable fully Dynamic emails  we'll be able to create unlimited emails and we'll   be able to create unlimited reminders now this is  all customized inside the admin screen so we're   going to have this brand new penalties now keep  in mind the other information the atom screen was   from previous trainings but if you catch those  I'll leave the links down below ones for the   invoice ones for the payments but we're focused  on penalty fees assess on over two so certainly   this is not a part two this is not a continuation  this is a brand new training so you won't have to   have seen those before so what we can do is we  can create additional penalties we can create as   many penalties as we want now I've got here a list  just about a table that we've set about 10 or so   and of course these are the types of penalties  I can create it for example we want to let the   customer know for five anything five days overdue  but there's no penalty on that maybe something   that's 30 days we do want us as a felony and that  penalty is going to be based on the percentage of   the balance it can also be assessed as a fixed  amount so we have a fixed amount here so twenty   dollars and fifty cents on this one however the  percentage of balance so we get to create a fully   customizable penalty type based on a percentage  or a fixed amount along with the number of days   overdue and then when we create a brand new  payment run add new we can then set a name   here and then we can set the penalty type here  so this is what we're we're going to be creating   we'll create an email subject and an email message  and those include these variables here and we can   let them know that we want to send this we want to  create this monitor let's say within 30 days after   the due date for balance is greater than whatever  you want to set maybe anything over greater than   zero balances so we can create that and save that  automatically so this is what we're going to be   creating this is how that works we'll be able to  save them add new delete and of course check and   send for reminders that's what we're going to be  building this is the sample I'll be closing this   right now and we are going to be saving it from  a blank worksheet now keep in mind this workbook   itself has that payment reminders that screen  you just saw which we're going to be building   from scratch however this particular workbook  does contain the admin and it does contain the   invoice so keep that in mind it does contain all  that so we don't need to necessarily recreate the   entire workbook we're going to focus on this fully  customizable payment reminders okay so the in this   particular sheet as with many we're going to  dedicate the first two calls columns A and B   to admin we'll be using just a few cells of those  but not too much so we're going to call that gray   we want the first row we can give that call we're  going to hit that the title payment reminders with   penalties and we'll also give it a little bit  of a format here all the way to let's say n or   o something large I want to format that nicely  with some blue colors just as you saw in the   sample so we're going to fill that and I'm going  to give it some fill effects we're going to use   this blue color many of you like to see these now  this is a relatively simple application there's   not a lot of macros therefore we can create  this from scratch however keep in mind that   larger applications with a lot of them will still  be it's probably a little bit big for this screen   22 we will have to create those and then I'll go  over the code so it really depends on the type of   training of course this training is for you so if  you want me to create types of training you want   to see more from scratch or you want to see bigger  applications in which I go over the code let me   know in the comments below this training is for  you so I always appreciate your feedback and it   is your feedback so don't forget to go ahead and  subscribe to this channel if you will please click   the like button that helps and go don't forget to  click the notification icon Bell that is going to   help us and help the algorithm so that you get  alerted to when I have brand new trainings all   right all I did was cover the background while I'm  talking away here I'm just giving it a nice color   that's going to give it a nice format though so  that's kind of the basis of it I'm gonna probably   save row two that's going to be for our button set  so our button set is going to go right here and   I want a list of reminders here so inside let's  say right here I'll drop it down to four before   let's go ahead and D4 I think GeForce we'll put  reminders in here and then our first one will be   in let's say row five we'll drop it down reminders  and then five will be our first row of reminders   and I've got this zoomed in a little bit so I want  to all italicize and bold that and I'm going to   Center that also what I want to do is I want to  have the reminder name here I guess two and three   is fine here we'll use reminder name here we've  got some the reminder name we'll put the name   I want the first field called the reminder name  and then I'm going to skip down I'm going to go   to row six we're going to call create reminder  I want to know how frequent how often should we   create it as you saw in the sample below that  I want to know what penalty type penalty type   so that is going to be a field we need to assess  that because there's different penalty types next   up in row 10 we'll do the email subject that'll be  a full row and then down below we will do a large   field for the email message customized message  that we want to send okay so for the reminder   name now next up I also want to have a status so  we'll put in column J here we'll put the status   here so that means only those that are active  status and there'll be something like active or   inactive or something like that okay next up I  want to do in this particular row I want to put   days after so here we'll have a long merge cell  here create a reminder let's see number of days   after so I'm going to merge and center this we're  going to put days after due date for balances   greater than and then the user put in a mount in  here so let's say 30 or whatever and then I want   the penalty type let's say we'll put a drop down  list of all the penalty types those are the same   penalty types that you saw in here we're going to  be putting a drop down list I've got a named range   for that if we go into the formulas name manager  and we look down here and we have something called   here under penalties this is an offset named range  let's move that over and zoom it in you see admin   using offset if I tab into that it's going to  surround those so penalties is the one that we   want to add as a data validation for that field  because we want the user to choose from one of   those penalties so when I click in here I'm going  to click in data and then data validation we can   also use F3 on the list to select it or we can  just do equals and then I've already copied that   so penalty so that's going to be our penalty so  we have a drop down list of penalties the email   subject is going to be the full row will go all  the way to K on that one I'm going to merge and   center that and left justify that email message  we can use is a larger field and perhaps we can   go all the way down to let's say 33 on something  like that so we want to be large so that's going   to cover that or just uh let's see yeah probably  33. so I'll extend this down a little bit and   we'll go to a large row so we get everything  covered that way and that way it allows the   user to put in larger emails so 33 all the way to  from column K 33 and then all the way up to this   one I'm going to merge and send to that I'm going  to left Justified and then I'm going to use the   upper and then what we're going to do is we're  going to give it a white so that's going to be   our email message also going to give this white  okay so for our Fields the penalty type and then   what we want to put is how many days overdue and  then what I'll do is I'll put the amount so I'll   do something like and I'll have VBA take care of  this 90 days overdue at and then how what is the   amount right so the amount would be something  like 30 or and then this will be either dollars   or percent so this will be taken by VBA so VBA so  as we change this five days or 15 days this will   change and this will change so what are we going  to base that that information on it's going to   be based on here so if it's five days over dude  that means 30 days overdue it's a percentage of   the balance and obviously zero in this case but  we could change it or for a fixed amount we could   change that to ten dollars or whatever so this is  the information when I select 15 days that I want   to apply here so how many days overdue and whether  it's 30 or whether it's a percent so in this field   VBA will say dollars or percentage based on what  I choose here the email subjects so this case we   will just put this is the only one that the user  is going to be entered in this one everything else   is going to be automated so what I'll do is I'll  hold down the control and just for those fields   that I want the user to be entering I am going to  color those and this one the number of days here   so which is actually this one here okay so that's  exactly what I want I'm going to give those color   white and I'm going to format those I'll continue  to hold down the control of this and this and what   I'll do is I'll drop it down here and we'll just  and right click on those fields and we're going   to do is we're going to go to format cells once we  format those cells I'll give it a border and we'll   use a darker color within our theme this one and  I'll go all the way around with the solid and then   on the left side I'll give the dotted line here  okay and then very something very very similar   on the label so what I'm going to do is I'm going  to hold down the control here on all the labels   here and then what I want to do is I want to right  justify it so I'm going to right justify it here   by just holding that down right here and then also  I need to actually merge and center these I want   to make these a little bit bigger and this one  too it should also Encompass this so let's shrink   it down a little bit so we can see everything and  then I'll merge instead of this twice that's going   to call for that coloring it back white and I want  to do the same thing with this to make sure that   we cover that okay that looks pretty good I like  the way that that looks now what we're going to do   is we're going to continue with this so we're  going to have the number of days the penalty   type also super versions that include that here as  well so that looks good that's the drop down list   here good so we've got our drop down list let's  reformat these particular borders giving them the   same borders I want that dark blue to go and then  we'll zoom out a little bit all the way around   left side dotted line looks good now on these  particular ones I want to bring that back to   that solid line that's for that larger field here  okay that's kind of the basis of our form here   we can add some more borderline to keep things  integrated here as we move across and then also we   want to add some borders here here and here that's  going to look really good with our same border so   formatting those cells now we can also of course  color the Border up here and use this instance   up here we're going to do it right with this one  here and then just surround your book borders okay   that's looking good our forms good let's go ahead  and save our work we want to add our button sets   and our icons and then we'll get into some VBA but  everything looks pretty good just the way we want   it I'm going to format these two Fields they're  going to be a mount so we're going to set those   two amounts and then also what I want to do is  this is the number of days we got 90 days after   due date for balances greater than and then the  690 days overdue at okay so it's looking really   good as we select these things we want to make  sure this is customized as we choose a different   one now the reminder nameless merge and center  that as well left Justified we want to put in that   name here so we're going to put in whatever name  let's say we want to put in 120 days overdue as we   as we add that a unique name for those reminders  here's a list of our reminders as I select a   reminder I want that particular reminder to load  up anything previously saved so what we want to   do is we want to add some conditional formatting  here all right so we're going to do is we're going   to select on a bunch of rows here we're going  to go into conditional formatting manage rules   we're going to add a new rule we're going to use  a formula which can be based on two conditions   the first condition is so we're going to use and  the first condition is we want to make sure that   there is actually a value in column D and any row  Associates we're going to put V and there does not   equal and then double quotes the second condition  is we want to make sure it's based on even rows   so even rows only so modular 2 equals zero zero  means that even row I'm gonna give it a format   of that it's going to have a border it's gonna  have a lower left we're going to use the same   color that we need so we're going to use a dotted  line on the left the lower and the right side here   clicking ok I'm going to copy that rule I also  want to give it actually a fill color so we're   going to fill it with a similar color that we've  used previously and that's going to be located   right here it's that light color so we're going to  use fill effects for that that light blue clicking   ok now I also want to create a similar so we're  going to copy that Ctrl C then what we're going to   do is we're going to go in and we're going to add  a brand new rule we use a formula again the only   time is difference we're going to make that change  to odd row so this is going to be odd rows equals   one you can give it a format here it's going  to give it that white color and then what we're   going to go into the Border we're going to give  it the same border that we did that dark color   here and then again on the left the lower and the  right clicking OK and clicking OK clicking apply   to make sure that Excel has the correct Fields  applies to Fields D starting D5 all the way to 29   clicking ok now what we need to do is just check  here as we add it we can see that automatically   the conditional formatting we're going to be  adding one more rule in there in just a moment   okay so what we want to do now is I also want  to highlight the selected row we're going to put   some information in here I want to know what that  selected reminder row is and I want to put that   row directly here so if it's 7 I want to color  that row I also want to put whatever selected   reminder ID each individual payment reminder  does have its own ID so I want to put that   ID I'm going to put it in B4 I also want to know  what the row associated with that reminder call it   database rows what is the row that's associated  these reminders are saved in this list here we   have the reminder ID the name the status the  days the balance threshold the penalty name   the subject and the message so we've got some  reminder IDs I want those IDs to know I want   to know the next reminder ID okay so the payment  reminder database row that's associated with that   I also want to know the next reminder IDs and the  penalty row what is the row that's associated with   the penalty the select penalty row okay I'll give  those a unique color and some borders so we're   going to do is we're going to call those yellow  and give those borders all the way around   then we'll start adding some information I will  left justify that and then what is the selected   reminder let's say we have a reminder ID 3. I  want to know the database associated with that   so that means 3 is on row five here so what I've  done is I've created a name brace called reminder   ID inside the formulas name manager we can locate  that under reminder ID so right here is where a   reminder idea is it's an offset formula here and  it's going to Encompass all the data so we can use   that to determine what row our first one starts  on row four so we need to compensate for that so   if we want to find out which one so we're going to  do equals if air and then I want to look at that I   want to use a mash to find the row so we're going  to match what are we looking up we're looking up   the reminder ID here we're looking it up inside  the particular name range called reminder ID and   we want an exact match once it's found I want  to add 3 because I want to know the row number   that's associated if it is not found it will  create an error we're going to trim that blank   so that means we know that number three is on row  five I also want to know the next available as we   add them equals if air in case there's no D there  could be an error Max of a minor ID I want to know   the maximum plus I want to add one available for  the next one and of course if there are none that   means there's no data we'll just set the default  to one and the penalty row what is the row that's   associated with this if we look in the admin we  see our first one is available on row 19. so if   I know the first ones on row 19 if it's found we  have a named range for that called penalties as   you saw and so what we're going to do is we're  going to make sure we know the row associated   with that so again we're going to use equals if  error we're going to run a match and again I want   to match what am I looking up I'm looking up this  value and I'm looking at based on the penalties   I want an exact match and I want to know what  roads Associated so I'm going to add 18. I know   our first one starts on row 19. so if I add let's  try to get that plus 18 here it's going to set up   that row once I know that if there's an arrow  I just want to return empty okay all right so   that means our first one our 9 90 days overdue  our first one five days is going to appear on   row 19 and that is correct 19. okay so that's it  that's all we need for that saving our work okay   let's give this a theme color and I also want to  add another one here called variable so both of   those are going to take on that theme color so  I'm going to format those cells here and then   we'll just do border we can do all the way around  border using our theme color here all the way   around with a full line and also I want to give  it a fill effects ing to our theme here and   just give it a colors because that's going to be  the header of both our reminders and our variables   here and here okay and then make sure the font  is bold and italic all right so that looks good   so now this one I'm going to put in variables and  I'm going to Center that also and so we can go in   Center that and our reminders will go here so I  like the way that that looks now I want to make   sure that this also has some conditioner I'm just  going to copy that and I'm going to paste it right   here because I want this to have a conditional  formatting as well and but I want to make sure   that it takes on the rules differently so we're  going to manage those rules and this one's going   to be based on column M so we just need to change  this D here to an m and it's going to apply to   that okay so that looks good for that one and I'll  change the other one for the even rows here and   I want our variables to take on that conditional  formatting as well we'll be adding some variables   and those are going to be used for our email so  what are those variables well the first thing I   want to have the customer name because I want to  make sure that's included inside the emails the   second variable that I want to have is located the  invoice number here so that invoice numbers can be   included in the emails and I also want to know  the invoice balance so we'll use brackets around   those so invoice balance will be our variable here  and we'll use those inside the code I'm going to   show you how to do that I want to know the penalty  amount so the customer knows the penalty amount so   we're going to put penalty amount as a variable in  here these will be replaced with the actual values   I also want to know how many days overdue so that  we can let the customer know how many days overdue   their invoice is and lastly we want to know the  total do that's simply going to be the balance   along with the penalty amount total due okay  so that's our last variable and you see how the   conditional formatting applies as we add those  as we add the reminders they will do that the   macro will take care of adding these reminders for  us so that looks pretty good I like the way that   that looks let's go ahead and add in our payments  and add on our buttons so that we can add it so   what I'm going to do is I'm going to insert here  and then we'll use a shape and I'll just use the   rounded rectangle here that's pretty standard  shape here and this one's going to be for save   reminder so I'm just going to add that right here  call that save reminder once I get that just the   way I like it I'll go ahead and duplicate that  giving it a format based on the theme here you   can use this theme here okay I'm going to put  it here and here okay I like the way that that   looks and then what I want to do is just go ahead  and give it a little bit darker color ing   to our theme the shape form I'm give it that Shea  fill and give it that theme color right here okay   so we've got that that looks pretty good and what  I'm going to do now and we got save reminder I'll   duplicate that this one's going to be for add new  so I'm going to place that here I'll duplicate it   one more time this will be for the delete and  one more time for the check and send reminders   so we'll call this add new this will be so we  can add brand new reminders with just a click   of a button and then I want to be able to delete  the reminder along with the warning letting the   user know that of course if we delete it it's  gone forever so we'll add that and then this   one we're going to call check and send reminders  as you may have seen from the sample if not no   worries okay so check and send reminders let's  take a look payment reminders with penalty save   reminder add new delete reminder check and Center  manage so this is the macro that we're going to   send I'm just going to check to see if there's any  reminders sent and if they have not been set for   particular over the invoices they will go ahead  and send them we can now add in our logo so what   I'm going to do is I'm going to insert picture and  our shapes and icons and I've got all these saved   in a folder if you do want to get all these along  with any resources along with all the updates that   are associated with these applications updates are  based on your feedback you would want to join our   patreon campaign patreon is a great way to get  more content everything for a very low cost and   of course it also helps support our channel so  I would definitely appreciate that if you can do   that I would appreciate it starts at just three  dollars a month so it's a great way to support   Excel for a freelancer and also make sure that you  get tons more content at a very low cost and it's   customized just for you all right I've added these  logos while I talk away here save reminders and   the icon here I should say that okay what we're  going to do is we're going to Center and group   that and I'm gonna do the same thing here this is  a little bit big for this particular icon so we'll   set that to 0.18 the rest of them are standing  0.12 I'm going to use the control I'm going to   group them together delete the reminders also here  Center and group and then we're going to do thing   for check and I'm going to make this a little  bit larger checks and check and send reminders   here so again doing the same thing and grouping  it now what I also want to do is I also want to   make sure they're all lined up horizontally and  vertically so I'm going to make sure they're in   line I'm going to distribute them so in the shape  format align I want distributed horizontally   so that's it then what we're going to do is going  to group them together as I possibly adjust these   column widths I certainly don't want to adjust  the size of those buttons so we want to make   sure that we're setting the shape options to move  but don't size so that will take care of that and   that's going to go for any shape okay very good  all right I don't think we're going to use row two   so we can just move that up a little bit and so  it's exactly the same as my sample that's always a   good idea all right so everything is looking good  we're ready so first thing what I want to do is   I want to do some automation stuff when I make a  change to here whether it's loading or not I want   to automate this so that means if it's five days  overdue I want to say nine to five days overdue at   whatever it is so I want to pull this information  however many days over there so this will be five   this would be 15 let's make sure it's accurate  so percent is balanced then I want to change   that so I want this information to be updated  automatically and how is that going to happen   what's going to happen on change event when I make  a change to this particular cell G8 that's when I   want to automate this information so this could  go to percent or dollars based on that so how   are we going to do that well it's going to happen  with the change events we're going to go into VBA   and start with that code developer visual Basics  it is their alt f11 pay reminders as you see this   is the sheet that we're focused on pay reminders  and we're going to look into change event so we're   going to go into worksheet not selection change at  least not yet and go into change events so what do   we want to focus on I want to focus just on that  cell g8's the one if that there's a change to that   something's going to happen so if not intersection  is nothing I want to make sure the ga contains a   value so this is going to be changed to G8 not  h12 that's just some automated I use Auto hotkey   to automate that and we also want to make sure  that G8 contains the value range G8 dot value is   not equal empty if that happens then we want to do  something well what do we want to do well I want   to make sure that we have a particular correct  item here how do we know if it's correct we   know because B6 is an M if they put in a correct  information of course our data valve station is   going to tell us but also we know that on empty or  anything like that B6 is going to be empty so as   long as B6 contains a row then we know that it's  correct so we can put if range B6 dot value does   not equal empty then we can proceed so what is it  that we want to do well what I would like to do is   I want to pull that row so we're going to mention  that I want the penalty row so we'll Dimension   penalty row as long I also want to know the type  Dimension the penalty type as long is it a percent   or is it based on a dollar amount penalty type  as string okay so what is that penalty row that   penalty row is based on B6 equals range B6 dot  value okay dot value that's the penalty the row   that's associated with that inside the admin  screen penalty row once we have the penalty   row I want to see what type is it how do I know  well I know that if this is the percentage of a   balance I'm going to copy this then I know it's a  percent based other otherwise it's a fixed amount   dollar amount so we can differentiate because the  penalty type is going to differentiate based on   that so to do that we're going to make an update  using a little if then statement they called if   that's going to be located in admin dot range and  where is that information located that we want to   extract it's located in column n and the penalty  row and and the penalty row dot value equals and   I've already copied it percentage about then we  know the penalty type we'll set that penalty type   goes percent penalty type equals percent that's  what I want to put in that field percent else   it's going to be a mount right so else we can  simply put in dollars else penalty type equals   dollars because that's the information that I want  to store it on Okay so we've got that there so now   what I want to do so basically what I want to do  is I want to take that penalty type whatever it is   percentage and I want to put it directly inside K8  so that's where it's going to go either percent or   is going to go directly there continuing on  now what I want to do is I want to automate   in information additional information Insider so  what I want to put I want to put days overdue so   this however many days overdue and then the text  days overdue and where's that going to come it's   going to come directly from M so we could say  something like this range I8 is the range that   I want to put that in let's double check that to  make sure that dot value that's where I want to   put it inside that's the cell that I want to put  it in inside the payment and reminders located   right here in I8 equals and it's a combination so  coming directly from the admin dot range anytime   you misspell something and you try to do that you  know but as soon as you add in the right spelling   and you hit the intellisense it's going to come  up so that's a great way you can tell that's how   I knew I spelled it incorrectly right away okay  so it's going to be the admin what's in column   M and the penalty row dot value and I want to add  something else in I want to add the text the other   text so and space days overdue at and that's  good enough then what I want to do is I want to   add inside whatever's in J so hey what is the ad  what is this inside J is it it's a percentage or   is it an amount it's going to go in j8 and where  is it going to come directly from it's going to   come directly from o right here so we're going  to add that in here so I'm just going to copy   this here and we can actually duplicate it and  make the adjustments so but it's not going to be   I8 this time it's going to be j8 so inside j8 what  do I want to put I want to put whatever's in O and   that is going to be the percent or amount percent  or amount so we'll put that in percent or amount   okay that looks good so now that's in K what do I  want to do I want to put in that type K8 is going   to take on the actual percent the text percent or  amount so equals the penalty type we've already   put it inside this particular named range right  here so this particular string here penalty type   so we're going to put equals set depend penalty  type percentage or amount okay so that's pretty   clear now the only thing I want to do is I want  to format that right so if we're putting in a   percentage or a format I want to format this based  on the number or an amount and now what do I want   to format I want to format this particular cell  that's located where the amount is located right   here inside here so j8 is going to be formatted  either it's going to be taken on the format or   it's going to take on a percentage so we want  to format it based on the type so range j8 dot   value so it's going to be probably an if then so  if penalty type is equal to percent then I want to   make that change then what's it j8 not the value  but the number format DOT number format equals   what we'll set that as a percent so it's going to  be something very similar to what we would find   in percent zero point let's say 0 0 and you could  put in as many decimals as you want so we'll do   one like 8.5 or something percent so that's the  formats can take on else the number format is   going to take on a dollar amount so copy be this  else the format is going to be equal to we can   put something like the dollar you can put whatever  currency or I'm putting dollars I put in a pound   sign here for the format Dot and then 0 0 for two  decimals okay so it's going to take on that format   for dollar sign so that way we differentiate  and then what we'll do is we'll just clear the   additional details we will save our work before  running our code and that's going to be based   on a change so we can check it now so if we make  changes to 60 days overdue we say 60 is already   20 50 so let's double check that for 60 days 2050  that looks pretty correct so let's change it to   50 and make sure that that works inside here  just double clicking that will automatically   okay so that's good looking good so it changed to  fifty dollars but what about if it's a percentage   let's take a look we have 120 days is five percent  a percentage of balance so when we change this to   120 days here we want to make sure that the  percentage update it's now in a percentage   format and this change to percent 100 overdue  okay at five percent okay so that's looking   really good I like the way that looks a little  code automatically and now when I load in these   reminders I want to load that in but we're going  to get to that inside a minute that's going to be   on selection change one other additional code I  want to add in here is basically the conditional   formatting for the selected row so we're going  to go into the home conditional formatting we're   going to manage those rules and probably just  copy the applies to here and then I'm going to   click a new rule let's use a formula and this can  be based on the row here so B2 equals the row I   want to color the selected row so row beating in  parentheses I'm going to format that I'm going   to do the fill effects here and then what we'll  do is we'll just do that darker color ing   to our theme that we're using here I also want to  give it a white font so that it stands out I want   to make sure it's bold so the font's going to be  bold and white and I'm going to click OK and click   OK and click ok so now you see that row 7 colored  row eight and so as we select it that row is going   to go in here so as we add them so now what I I  want to do is I want to take all the reminders   that we've saved that I want to load them here  I want to create a Mac that's going to put all   the reminders names so it's going to be here so I  want to take these reminder names here 30 90 and   60 days and I want to add them directly in here  but I don't want to just add those names I want   to add the IDS in here and this is going to be  hidden they'll be hidden but I want to put the IDS   in here and the idea is when I select a reminder  I want to know the name and the ID that's located   here I want to put directly inside B3 what that's  going to do is going to automatically calculate   the row that's associated and I can load that on  so that's going to happen so the first thing what   we want to do is we want to create a macro that's  going to actually load that list up and one unique   list and I want to put that list right here so  I want the reminder IDs and the days that I want   to determine how many there are and I want to  bring them directly over inside this screen okay   so let's write a macro that's going to do that so  we're going to go inside the module called payment   reminders here and you see I've got some macro  the types of macros that are loaded here here   that we're going to be covering today so we're  going to be doing these and this one I've just   mentioned some variables to make things a little  bit quicker last row last results row results Row   the reminder row and the reminder column these are  going to help us as we move through the macros so   the first one we're going to be doing is called  pay reminder load list this is the macro that's   going to determine all the reminders that we've  created and we're going to add them in so every   time we go ahead and save a reminder it's going  to automatically update this list the first thing   we want to do is clear any details out inside that  so this sheet is called payment reminders so we're   going to focus on that she pay reminders dot range  I'm going to clear any details out so what is it   D5 all the way through a large row but we're  actually going to use C5 because that's where   our IDs are located so C5 through D and then  just use a large row here dot clear content so   clear out any previous reminders and IDs so the  ID is located in columns C so once we have done   that we can then add in the additional information  so first thing I want to focus on is the reminder   row so with the reminders list that's the list of  reminders that we have that's the sheet reminders   list is this sheet right here I'm going to run  an advanced filter really focused on these two   columns here that's all I really need and I want  to determine the last rough the last row is less   than four we can exit the sub there's no data if  there is data we're going to run Advanced filter   no criteria all I just want is unique values here  reason we want to do this is we can then sort them   and work with this data if we as opposed to just  copying them from here so it's kind of a nice   way to do it it gives you the ability to sort  them maybe we want to sort them alphabetically   or numerically or something like that so I like  to bring them in a different area Okay so we're   going to do that with reminders list I'm going to  determine the last row what is the last row well   the last row is going to be based on column A so  if the last row is less than four we can exit this   up if it's not then we can move on to the advanced  filter okay so what I'm like to do is I want to   get the unique names now when we run an advanced  filter without any criteria meaning we're just   bringing the data over and I just want the unique  data I want to make sure that any criteria that   got created previously or maybe in another macro  gets deleted that's very important so the first   thing what we want to do is run on air resume next  and delete any criteria that may have been created   before so we can do names criteria criteria is  the named range that gets created automatically   when we create an advanced filter that contains  criteria and on air goes to zero if we look in   the name manager formulas name as you will see  several name range based on criteria so these   are all get created automatically when we use  Advanced filters this is what I'm deleting and   usually there's one per sheet any sheet that we  create an advanced filter that contains criteria   VBA will create this name range automatically  so it is this what we are deleting because when   we run an advanced filter that has no criteria  it is used and it's hidden which we don't want   to use it so we basically just have to delete  it okay so that's going to delete any criteria   if it exists if it doesn't exist it would create  an error so therefore we've wrapped in an honor   Zoom next and on our go to zero once we have that  what we can do is we can run an advanced filter   now this Advanced filter is not going to have  any criteria here so I'm going to take this and   I'm just going to clear it out no criteria what is  the range that we're going to be focusing on we're   focusing on just here reminder ID A3 through B3  so A3 through B3 because that's the only one and   the last row whatever the last row is and what do  I want that I want to bring that information over   we can braise this up now we're in like a museum I  want to bring that unique over to both M2 through   N2 so M23 and 2 is where our results are going  to go so I'm going to change this to N2 okay so   now what we want to do is we want to determine the  last results row so the last results row is equal   to based on column M so Amazon last if the last  results less than four we have no data so last   result so is less than three we have no data right  so if it's less than and three we got nothing if   it's not then what we would do is bring it over  now to notice our first results row starts in   row three and our first results row starts in row  five so we need to compensate for that difference   when we bring it over assuming that we do have  data we can then bring it all the way over but   maybe we're going to do it sort so why don't we  sort that first if it's just one row of data we   don't need to sort it it would be nice to sort  it alphabetically so other things look in order   if last result row is less than four that means  we have only one row of data then go to no sort   we'll skip down here and go to no sort so that's  what's going to happen as long as there's just one   row of data if there is data whoops let's put a  colon there I want a colon there here okay so if   there is data we're going to run a sort so we'll  sort I got that automated a little bit to make   things faster we're going to update the sheet this  sheet is reminder list so I'm going to copy that   here instead of tasks I'm going to put it here and  I'm going to put it here so focus on that so we're   going to clear the fields out do the reminder list  now what field are we starting we are sorting it   based on let's use the name okay so that name  is going to be located right here in N3 n3's   word name so I'm going to put N3 that is the field  that we're going to be here that's our key field   so and three now what is the range is going to be  based on M through n so m 3 through n and I want   it sorted ascending so that's nice okay so that's  going to sort them automatically I'm going to save   our work and then what we want to do is once we  have it I want to bring that range over go down   here I'm just simply going to bring it over into  our payment reminder Fields okay so it's going   to be pay reminders dot range we know it starts  in C is where we want to put that reminder ID C5   through D and the last result row plus two because  we need to compensate for that different dot   value equals dot range M3 through n and the last  results row dot value so bring over unique remote   okay very good what we're gonna do is we're going  to save our work I'm going to run that code and   then what we're going to do is we're going to  check for this error so Advanced filter copy   to range M that's not going to work and two okay  so continuing on okay that looks pretty good our   ID right here but I don't want to show this ID I  know it exists but I don't want to show I want to   hide it so I'm going to go into home I'm going  to go into the formats more number formats I'm   going to choose custom I'm going to choose two or  three semicolons two for numbers three for Alpha   in America so what that's going to do is hide it  now if I select on the cell we still see it it's   located but it's not there okay great so I like  that next up what I want is I want to macro when   I select something I want all the details of that  particular reminder to load up into these fields   now I've created something called Data mapping  already to help us move things along and it's   data mapped here so that means that reminder  name is located in G4 if you've seen any of my   videos before this is familiar but repetition will  help status is located in K4 so K4 you see is the   status here k6 is going to be located the greater  than amount here so it's all mapped to this k6 G8   and G10 so all I need to do is loop from 2 to  the last column what is that last column that   last column is eight I'm going to loop from 2 to  2 8 and as we then bring it so that's all we're   going to do on load and simply look determine the  row bring whatever is found in that row and put it   into G4 whatever is found in the row and put it in  Decay for and just Loop through that automatically   so that's what we're going to do on loaded the mix  things a lot quicker okay so the first thing what   we want to do is write the macro before we run it  save it and load it so the first thing we can do   is using save and the second we'll do is load it  so when we save it we need to make sure that it   has a few things so focus on with pay reminders  that's the sheet that we're focused on notice I   always put that dot just to make sure I spell it  right when I put that in the cell intellisense   comes up I know that I've spelled it right if I  spell it wrong and nothing's going to happen Okay   so to do that now what I want to do is I want to  just make sure on the first one when we add that   save I want to make sure that it contains at least  a name so I want to make sure that G4 is not empty   if G4 is empty I want to let the user know if dot  range G4 dot value equals empty then prompt the   user then prompt the user to let the user know  message box please make sure to add a reminder   name okay exit sub okay so now what I want to do  is I want to determine is it a brand new payment   or is an existing one how are we going to know the  difference we're going to look directly to before   we know that if B4 contains a value we know is  existing however as soon as we clear that reminder   ID we know before is empty so we're going to  determine that okay so B4 is going to let us know   if it's new or not so to do that we are going to  make the update ingly based on that pay row   okay so let's write that up right now if dot range  B4 dot value equals empty then it's a brand new   one else it's an existing else existing so we know  if it's a new one we need to assign a brand new   row new payment reminder so we need to sign a row  so what is that row going to be so the reminder   row that reminder row it's gonna be the first one  available on the payment list equals reminder list   dot range a 999 dot end Excel up dot row plus one  first available row once we have assigned that   row I want to then add in that reminder I need the  next available reminder ID so that next available   one is going to be located right here in B5 I want  to place that directly inside B3 so dot range B3   dot value equals dot range B5 dot value okay so  once we have added that we also want to add that   same one inside column A so that's going to be  next reminder ID I want to take that same one and   I'm going to put it directly inside here reminders  column A and the reminder row and the reminder   row dot value equals whatever's in B3 B3 okay so  we have that available to us that's going to be   the next reminder ID same item okay once we have  that what else do we want to do well that's it for   the new ones what if it's an existing if it's an  existing all I want to do is extract that reminder   wrote directly from B4 so the remind finger Roots  the only thing we need to do for example render   row is equal to whatever's in B4 I'm going to call  that existing reminder row so that's it everything   else is going to be regardless if it's a new or an  existing so what I want to do is I want to save it   for using a loop for a reminder column which is a  long variable equals two to eight this is what I   was discussing before next reminder call so what  do we want to do well basically I want to take   when we're saving it I want to take whatever's  in the cells dot range and I want to place them   directly inside the database so where are those  cells located it's located inside the reminder   list that dot cells we're using cells because  both calm and the row are variable Row one and   the comminder column dot value so that's the cell  right there dot value so that's cell G8 whatever   it is K8 is going to be the value so whatever  that is going to equal we're going to put that   directly inside the database because we're saving  it so where's that database it's the reminders   list dot cells whatever row so we've already set  them reminder row what is the column reminder calm   dot value equals so that's it so basically  whatever's located in this cell it's going   to be put directly inside the database add data  to database so that's all we have to do for the   loop relatively easy there fix this here okay so  I also want to make sure that when we added we're   using large amounts of data and we're saving it  let's see what happens I want to wrap the text   right if I have a large amount of data I don't  want it to do this what I want to do is I want to   wrap the text so basically inside VBA I just want  to do that so we can do that with the entire row   so we can do that inside VBA simply something  like this reminder list dot range then we can   just add the row and reminder row and colon and  the reminder row dot wrap text equals false okay   so that's going to just keep that from growing we  don't need that in the database it's not important   okay so very good so we've added that in and now  what I want to do is right after we save it I want   to run this macro in case it's a brand new one or  in case we've made any change I want to run this   and the reason that's important is if we add a new  one we want to make sure it's included inside the   list so we're going to do end with okay so what  I'm going to do is I'm going to save that okay so   very good now let's take a look at what we have  here we already have data 1 3 and 4 30 90 and 60   day okay so right now we're on 120 days if it's a  new one or an existing one so let's go ahead and   make this change this to let's say 120 days I like  that 120 days so we're just making the updates   here and then let's change this to 50. so this is  going to be the existing one we're changing the   existing one and let's add in I'm just going to  copy whatever we have over here into the subject   a little bit quicker so I'm going to take that  subject and I'm going to paste it directly in   here I'm also going to take whatever's reminder  located here Ctrl a control C back inside here   I'm just going to paste it in here okay so what I  want to do is I want to left Justify both of these   holding down the control and I also want to make  sure that this particular one is upper all right   so vertically up okay so I like that now what I'm  going to do this is an existing reminder here on   row five I'm going to save that so I want to take  this button here hold the control and assign that   brand new macro and we're going to go all the way  down here to payment reminder and then save and   update and select that that's going to be the one  we're safe clicking save is going to update that   okay so let's take a look and see how we did there  120 120 everything got saved inside the reminders   list okay we do need to wrap the text up I'll make  sure that that's right that looks good so this is   the one we saved and then we'll just update that  and then change that I like that okay good all   right so that's looking good it's saved 120 120  50. everything got saved into row five just as we   thought perfect so I like the way that though it's  now saving but what I want to do is I want to load   it now I want to bring the information back into  this form when I make a selection on one of these   I want to take whatever's there and I want to  bring it back into this form so how are we that's   going to be on a macro called sub payment reminder  load so when I make a selection I'm going to take   whatever values here that's the idea I'm going to  place it directly inside B3 I'm also going to take   whatever row selected I'm going to put it in B2  then I'm going to run the macro to load it so the   first thing we want to make sure that we have  an accurate row located here in B4 so first of   all we're going to do with pay reminders I want  to clear out some cells that's very important   that we're going to clear out some cells and with  okay so when we have that I want to make sure that   we are going to clear so what are the cells that  we need to clear out first thing would before we   clear out we want to make sure that we have the  correct row Associated first thing I want to do   is clear out all the cells except I don't want  to clear out anything Associated here I don't   want to clear out B3 and I don't want to clear out  the selector of B2 so but I want to clear out all   these cells so what are those cells so the first  thing I want to do is dot range and then we're   going to Cloud Associates those G4 through H4 I  also want to clear out K4 I also want to clear   out G6 and k6 as well as G8 through and I'll bring  this down here H H okay so let's take a look at   that so we can get the right cells bringing that  down here so we can take a look at both okay so G8   through h8 and then I also want to do K so we'll  take a look here K8 we also want to do so comma   K8 also we want to make sure that we're going  to bring inside G10 through K10 G10 through K10   that's going to be for our email subject and then  lastly the message here so with that message is   going to be g12 all the way through k33 so we  need to clear out those through k33 dot clear   content so clearing out those fields clear out  form Fields okay once they're cleared out we can   then determine the row so we can check that if  dot range B4 dot value equals MD then message   box please select a correct reminder although it  should never happen exit sub we certainly need   before the value so that reminder row is going to  be based on B4 equals dot range B4 dot value once   we have the row all we need to do is just do the  data mapping just like we did here except it's   going to be in Reverse so I'm simply going to copy  that and then reverse it up I'm going to paste it   down here except this time we're taking data from  the database and bringing it into that so taking   the data from the data is bringing it into the  form here so all we need to do is reverse this and   put that equals here so bring data from database  so add data to form field from database okay so I   like that there so basically it's coming from the  database here into the fields here so once we do   that that's pretty much it that's all we have to  do for the load I'm going to save our work as we   should and I want to add this macro so I'm going  to copy this macro and then we're going to write   a little code when do we want to happen when we  make a selection change on D5 all the way down and   I want to make sure that there's actually a value  in from D so that's going to happen on selection   change when we go into the payments right so pay  reminders this is the worksheet selection change   this is where we're going to write our code first  of all if Target dot count large I'm just going to   write this code it helps keep errors is greater  than let's say one then exit sub that means if   the user selects a large number of cells it's just  going to exit Okay so if not I want to make sure   again it's going to focus on D5 starting at D5  all the way down D5 through a large number d99   that one comes to all ones is nothing and range  D and the target dot row dot value does not equal   empty then we want to do something what is it  that we want to do I want to make sure do a few   things first thing I want to put that row whatever  row we've selected I'm going to put that directly   inside B2 so taking that Target that's going to  trigger a conditional formatting so range B2 dot   value equals the target row okay that's going  to trigger trigger conditional formatting next   up I want to take whatever ID and I want to  put that into B3 so range B3 dot value equals   where is it located it's located range C and the  target dot row dot value that is our reminder ID   reminder ID once I have the reminder ID I can  then run the macro run macro to load reminder   that's all we need for that relatively simple  that okay again always saving our work before   we move forward especially when we're clearing our  contents selecting on sale o