Simplify Your Gym Bill Format for Shipping Effortlessly

Gym bill format for Shipping

hello this is randy with excel for freelancers  and welcome to the membership manager in this   week's training i'm going to show you how to  create this incredible membership managing   application complete with horizontal tabs  printing id cards sign in sign out completely   with automated pictures and we're gonna have  automated recurring invoices connected to that for   your members i'm gonna show you how to do every  single step so i cannot wait let's get started all right thanks so much for joining me today  on this membership manager i've got this   incredible program to share with you so much  to show you on this including search by name   search by id and a whole lot more so we're going  to show you that including this really fantastic   horizontal tab mechanism here that you can use on  any type of application of course signing in and   signing out we're going to be able to do that  they're going to be using barcodes or ids or   anything like that with this really cool fade out  message and reoccurring invoices that make it look   very familiar to just two weeks ago so i've  got that to show you and a whole lot more i   hope you'll stick with me for the entire training  i do bring these to you each and every tuesday   absolutely free in fact this workbook is also  absolutely free using the links down below in   the description you can pick that up with either  your email or facebook messenger of course if   you'd like 200 of these applications i've got that  all wrapped up into a single zip file available   for you for just 77 it's an incredible deal i hope  you'll pick it up that also includes an incredible   application library and that means a single  click to get open that workbook and another   click to view the training so very convenient  and a great way to help us out here all right   don't forget to subscribe go ahead and click the  subscription button there and the notification   icon bell that'll get you alerted when i do create  these trainings i cannot wait let's get started on   this so i'm going to show you every single step  of how i did this this is the membership manager   this is a great application whether you have  a gym membership whether you have a sauna spa   membership or any type of membership where  members sign in and have reoccurring fees   you're going to be able to keep track of it  and i'm going to show you how to create your   own application whether you want to download this  application customize it yourself or just want to   learn some of the tricks i'm going to show  you every step on this so this says general   information right we can keep track of any general  information manager we're going to be able to add   and update a picture print the id or clear  the picture and we're going to be able to do   lots of that just like this very with a few clicks  of a button including a barcode scanner and also   we're going to be able to print the id to the  default printer so that's just a single click   and also we're being able to add new members save  members delete and automatically create an invoice   for that just on one click so it'll create an  invoice and then that invoice can be saved and   will automatically be reoccurring each and every  month based on the frequency now those frequencies   can be relative we've got member plans so if we  want to create membership plans we can do that   with this so we'll be able to create any number of  membership plans i'm going to show you how to do   that of course and the membership plans can have  any type of frequency whether we're on a weekly   day or anything like that we can create them on  the last our first day of every month on the first   monday of every month lots of frequency options  which may look very very familiar as we just   covered this last week if you don't if not no  problem i'm going to put links down below and   last week we covered this recurring invoice where  we were able to create these recurring invoices   i'm gonna include the link down below for this  training because everything in this screen here   was covered in the last training and right now i'm  gonna show you how you can apply this to another   program so we can put them together to build these  really incredible programs so these are step by   step however this week we're going to focus on  this membership manager with these horizontal tabs   multiple search features here that we have and  a whole lot more so we're going to get started   right away on that okay so we've got a lot to show  you so we're coming this membership manager how   do we create these fields and where do they get  saved and then and then how do we get this sign   in and sign out database how do we scan these if  you have a barcode if you have that id that i just   showed you on this you print it out you take this  barcode you put it into any usb keyboard scanner   barcode scanner and it'll automatically scan it  right here and it will automatically display that   and their picture associated with that we've also  got um these invoices and we've got a setup screen   that we've added onto now we had a few setup  screens we now have item types we now let's go   ahead and copy this let's paste the format and  i'm going to paste that format here just where   it might have additional let's add one more row on  that one and i'm going to paste special and paste   format so it looks pretty much the same and then  we've got some nice we've got item types recurring   and one time so we have also got weekly options we  have those in the reoccurring so we've got these   are helpful for the reoccurring and also some new  things for this on this we have member status now   so we can have different member statuses member  can be active they can be cancelled they can be   on hold we also have some application folders  where are those members going to be stored those   member pictures need to be stored somewhere so  when you download this file and those pictures   aren't loading up for you you wonder why well  that is because we have to map those pictures if   you want to use the same pictures that i have all  you have to do is simply join our patreon where   i'm going to supply all the resources associated  with this training this is where our members are   located we also have a default member picture  in case there are no member pictures and we can   map that to a specific cell so if you want to get  these pictures and the icons and all the resources   go ahead and join us on patreon and i'll of course  all have updates there so if you've got any ideas   or any feature requests or you want me to fix  something i'm going to have that also available   on our patreon platform i'll include the links  down below so we have a member pictures what   folder are member pictures located i've got  mine located directly inside here something   called member pictures so they're all in here  and of course inside that membership folder i   also have that default picture so i've attached  these that browse feature attached so those   that information is going to be put in this  cell our default picture is going to be located   in this folder this folder and our and all of our  member pictures are going to be located right here   everything else is relatively the same we're not  going to focus on some of the other issues this   has to do with recurring invoices and remember  i'm going to include the link down below so if you   did not see that reoccurring invoices want to look  up this information i will include that link down   below that is reoccurring invoices that's got a  training that's independent and unique to this one   so but i want to show you how we build upon these  right so you can have both of them in a single   download okay so inside the members we've got a  few features i'm going to start getting into this   first of all how do we create this really cool  tab feature if you haven't seen this yet i've   got a unique training on this but let's go over  and basically what happens is on selection change   of m when i make a selection change to e5 when i  make a selection change to f5 or g5 something's   going to happen and all basically we're doing is  hiding and showing rows and of course this group   are also hiding and showing so as you can see rows  7 through 26 are displayed here when i click the   billing history we see that 27 all the way down  through whoa way down through 59 is displayed and   when i click on the scan out history all the  other rows are hidden and now we have rows 60   through 92 displayed so basically we're hiding  and showing rows associated with the tab so   when i click on general info when i make that  selection change i also want to do one other thing   and that is i've got a group of shapes here this  is for the id card that group of shapes is going   to be displayed only on the general info and  that group is called id group id group and   that's display so we put all those into a group  that makes it really convenient we only want   that group displayed when we click on the general  info tab and also how do we get this look and feel   take a look notice that that the look of  this tab is different than the look of this   tab how do we get that look different notice that  the borders go up and around but there's no border   underneath the existing tab regardless of the  tab that we select it is a thick border all the   way around and nothing under there so how do we do  that well we use conditional formatting on that if   i were to highlight all the cells and go into home  and then conditional formatting and manage rules   we see that we have just a little some  information on it now it's a little bit   unique because we've got based on this so now  all the every time we select a cell what vba is   going to do is going to put that column  number it's going to put it directly in   b1 here so when i select um e5 you see that column  number 5 is located is placed directly in b1 and   then likewise when i click billing history it is  going to be 6 and vba is going to place it inside   7 inside b1 so the differentiating factor is  that column number so what i can do is i can   then create conditional formatting based on that  number so let's take a look back inside that and   look at some of the rules that we've applied  now also keep in mind that what i've done is   i've taken this bold this dark border i put it  around all the borders so we look into home and   then we look into the borders or actually let's  go into formatting those cells and we take a look   inside the border and we see that we've got that  thick border all the way around so that is the   actual border so the changes of those borders are  done with conditional formatting so the actual   borders have that thick border all the way around  for every cell associated so it is the conditional   formatting that's going to make that bottom border  disappear and it's going to put that thin border   up on top and the right and why do we do that  because conditional formatting can only apply one   style of border but what if i want a thick border  well then our original border has to be that thick   border and our conditional formatting will have to  do that thin border because conditional formatting   we can only apply one style of borders what do  i mean by that conditional formatting and manage   rules if we take a look at any rule here and we  edit that rule we go into the format we can see   that we can only apply one style of form we can  apply dotted lines but there's no thick thinner   border we don't have that availability in at least  in this version of excel which is 2010 and i don't   believe it's changed even up to the current  versions so but you guys can correct me if i'm   wrong on that so with that one style of border so  what do we do so we apply that style of border to   directly to that so for example we know that if  b1 equals 5 b 1 equals 5 and that would be this   e is selected in that case what i want to do is i  want to make sure that the right is selected the   right border is displayed ok likewise here we go  on b 1 7 if it's seven what do we want to show on   seven in that case f5 f5 is going to take on let's  take a look at that rule directly the left border   right so we're basically specifying borders based  on whatever selected now it's placed on f5 right   so that means f5 is going to play that right why  is that when i select this where do i want that   thin border i want that thin border directly on  the right side of if we take a look at this i want   that thin board right here and i want that thin  border right here so on those two i want to play   however if i select f five and this is six i  want that thin border on the left and the top and   i want that thin border here on the right on the  top so basically it's just a number of conditional   formatting we don't need to necessarily go into  every single rule but basically that's it we're   just applying the borders based on that okay so  sometimes it's if it does not equal the existing   column if it does not so that works too and also  what i want to do is whatever the current column   is i want to make sure that the bold that the  font is bold if we take a look at this one we see   that the font is regular here and here but the the  one that's selected is bold so we want to make it   selected so to do that all we need to do is based  on the existing column number if the existing   column number equals bold the existing column  number in b1 equals the what was selected we want   to format that bold so that automatically makes  it bold and also one more thing what we want to do   is we want to make sure that that bottom border  is missing so we edit that rule and go into   the formats we see that i've cleared out the  bottom border here that bottom border is missing   for the selected column for the selected column  if it's b1 equals the column that you selected   then the border is going to be missing  we're going to apply that to those columns   okay so it is the selection change event that all  we need to do is then is make that change and then   we run the macros to load those tabs so let's go  into the vba and take a look at how we did that   so under the developers vba alt f11 will get you  there and of course we're going to be focused on   this members sheet member sheet and it's based on  a selection change if the user makes a selection   between e5 and g5 if user makes the selection to  more than one cell we're going to exit the sub   that gives me the ability to highlight these  without anything to happen i don't want this   macro to run when i highlight more than one  right if i select one fine if i select more   than one nothing's gonna happen it makes it easier  especially in training so that's why we have this   if the target count large is greater than one then  exit the sub okay so if the user makes a selection   change a single cell between e5 and g5 we want  something to happen first thing i want to do   is to make things a little bit faster and also to  avoid this flash is i want to turn off application   screen updating and then what i want to do is i  want to run a specific macro based on whatever   cell they selected if they have selected e5 we're  going to run this macro called tab general info f5   they're going to select tab billing and likewise  g5 we're going to go check and check out okay and   then what we're going to do is we're going to  select something else right i want to get that   selection out of whatever cell they've selected  and to do that so we're just going to select f3   so no matter what is selected f3 becomes the one  selected so we've shown that and now what i want   to do is turn on application screen updating okay  so oh that's all we have to do so these three   macros are located in a brand new module called  tab macros so tab macros we look and i've just   got those three macros here and they're relatively  simple okay so if they've decided to click on that   that original cell located here e5 then i want  this macro to run the first thing what i want to   do is i want to set that column number you could  easily use active cell column or whatever you want   but that probably wouldn't work until yet until  after they selected so we're just going to set it   to five that's gonna set five six and seven we're  gonna do that for each one of them and then also   what i wanna do is i want to display a certain  number of rows so we're going to display rows   7 through 26 by doing an entire row dot hidden  equals false so display rows that's what we're   going to be doing here displaying those rows those  are the original rows that we want to display   for those the ones that we discussed and so after  we do that we want to hide the remaining rows so   27 through 92 those get hidden and then the last  thing as we mentioned the id group those groups   of shapes those are the group shapes associated  with that id card those three buttons along with   all the shapes inside the id i want to display  that so we're going to do that with shapes id   group dot visible equals mso true that's going to  show that id group now for the remaining tabs it's   relatively simple again we're changing the column  number the selected column number we are going to   show different rows 27 through 59 we're actually  showing hidden equals false means we're actually   displaying those rows and then we're going to  hide the other rows the original general info rows   7 through 26 we're going to be hiding those and  of course the last tab the check in check out or   actually say sign in sign out it should be called  entire row hidden equals true we're going to hide   those and then we want to make sure that the id  group is not displayed it is only displayed for   this general info group so we're going to hide it  here and we're going to also hide it here and then   likewise the last one check in check out or sign  in sign out probably is going to be 60 through 92   those are the ones are going to be displayed  and then 7 through 59 we're going to be hiding   and also that's it so pretty much and make sure  that groups hidden so that's it for the tabs it's   relatively simple to create your own automated tab  horizontal tab and i've got a training covering   that entirely but it's relatively simple okay so  that's it so now what i want to do is i want to   show you some of the components of this screen  now that we understand that how the tabs work   and basically what we have is the billing history  all the invoices that were created for a specific   member and all these their history of when they've  signed in and signed out here so i want to know   that and so how do we display that information  well first of all it's all based on a member id   now that member id is based on this member list  here so we have a list of members now these   members list all contain a member id they all have  a member name status the address so all the fields   are located here including their picture which  is located in column a the plan that they're on   and also the next payment a start date and  some notes if possible all right so we have   all of that and it's all using data mapping now  data mapping is going to help us map that data   that means f7 is located right that member name is  located directly here inside f7 so we want to map   whatever's field while h7 here is mapped directly  to our member list here inside the status so we've   mapped each one and what that's going to do is  allow us to quickly either save the information   from our sheet-based form or load it from our  database here or table list here into the sheet   forms using this map data that makes it just a few  lines of code even though we have a lot of fields   and so that's going to help us do that all right  so we have that so we also want to know the member   id right each member id is very important we've  created some named range to track that so let's   take a look inside our member list here and go  into the formulas and a name manager and we have   some news so we're going to look at member id this  member id if we tab over that we're going to see   we're using a dynamic named range using offset  starting in the header row a3 going all the way   down right we start in the header row because when  there's no data at all we want to make sure that   there's no errors so we start on row 3 but then  we make sure to compensate by moving one row down   using the offset and then what we also want is we  want to count all of that the columns including   the header row but also we need to compensate for  that header row by subtracting one we're including   the header row here again because there's no  data it's not going to create any errors so   if the delay gets delete if the data gets deleted  we know it's not going to create an error okay so   we have a member id we have a member name located  here a member phone here and we've got a few other   information member folder here that's going  to be of course located in the setup screen   and the member plan name right we've got that's  in the member plans so basically for this we just   have a few fields so i want to go over and show  you that to make sure that we have that in fact i   want to make sure that we have the tab because  i've member email we have the locator i want   to make sure we've got all the right data member  phone that's going to help us i'm going to slide   over here making sure that we got okay the phone  is good so everything looks good here so we're   going to use the same offset for each one of those  fields so inside we want to make sure we know what   that member id is now that member id you can  see is not displayed here but we do have it here   inside that and it's based on b4 now b4 is  always going to have our member id here and   it's also going to be displayed on our id card  so we understand b1 is the select tab this one   is for the member name so if i search the member  name here i want that id to be associated with   that inside b2 here's the member so we can load  a member up based on either the name or based on   either the id so we can load it in two different  ways so this field is the search based on the name   and what i want to do here is i want to display  the id so whatever name they've searched here   i want to display the id here i'm going to stop  the code so we can see exactly how that happens   so to do that what we'll do is we'll go inside  our members here and we're going to use the change   right we're focused on the change event right this  is the member name search right so when we do that   what i'm going to do is i'm going to select right  let's go ahead and stop it right we don't want   that member to load i just want it stopped at that  point so when i search a name here we see that   it's been stopped exactly where we want it to stop  and we also see that we have a member id that's   associated with this what we're going to be doing  is we're going to take this id and we're going to   place it directly inside b4 and that'll come in  just a moment so we've done that so what i want   to do is i want to know i want to make sure does  this id exist so how do we extract that well we   can use the index we're going to index that member  id and we're going to match it based on the member   name that was used if that doesn't exist it would  create an error and therefore it will be blank if   it is not if it is blank we need to let the user  know that they've made an incorrect uh selection   or incorrect enter of their name however if this  does contain an id we know it is correct we can   take this id and i'm going to place it directly  in b4 so we can continue on with our code now and   load that up so it'll load that name so you see  how it was there and then once we have it we're   going to clear this selection once we've loaded  it up correctly we can then clear whatever's in   f3 allowing the user to add another search name  in there so we can clear that name here and we're   going to do the same thing for id now the id is  going to be based on a raw i want a row that's   associated with this id if they enter an id i want  a row associated let's go ahead and stop that code   because that's going to be based on fh3 so what  i'm going to do is i'm going to stop it right here   and then what we're going to do is we're going to  load in there okay so now it's been stopped and   we see that we have a row that's been associated  with that we're going to use just the match so   it's um we're using a match based on the member id  that was entered here once that does as long if it   is blank of course it's not the correct id if it's  not blank then i want to display the row and we're   adding 3 because our membership ids start on row  4 of that table so that will ensure row so i know   that it's row 7. if i know that that's correct and  we've returned a row then all i need to do is take   this id and place it directly inside b4 and that's  exactly what we've done inside the code so again   if in this one number search if b2 is empty then  we know they haven't entered the correct name we   can exit the sub otherwise take whatever's in  b2 which is that id and place it directly in b4   right remember once we have a good search take  whatever's in b2 and place it directly in b4   likewise now all we need to do is make sure we  have a row if i have a correct row all i need   to do is take whatever is in h3 and place it  directly in b4 that's going to put the correct   id in the correct place so that's what we've done  right here b4 equals h3 that's going to load that   member id then all we need to do is run the macro  which we're going to go over shortly called member   load so if we continue on with that code it is  going to load that member so that's how we do that   it's a multi-search based on either id or name and  it's going to always place that id here so the id   gets placed whatever the id is placed right here  and also what we would do is we want the member   row i want to know the row that's associated  with the id located directly inside b4 using   match we can get that match and plus three is the  same formula but this time the formula is based on   the id located in b4 i also want to know the next  member number now the next member number of course   we're going to use the max formula as long as all  of our member ids contain numerical values we can   use the max so the next one is going to be 1010131  so using numerical max plus one is going to get   us there so we're gonna use the max member id  plus one that will get us there all right that's   pretty much it now what we do is i'm gonna go over  this one in a moment and that's gonna have to do   with our name but now all we need to do is we know  we've got the correct member id we know we've got   the correct row so we can use that inside vba to  load up all of this data and fill it in we know   it's going to come directly from our member list  right here so all we need to do is use a simple   little lines of code to automatically we already  we know we've already got the id in b4 so we can   start it in column two and we can run a loop all  the way to the last column and that last column   is notes so if we type in equals column we've got  to know what column all the way to column 14. so   we're going to run a loop from column 14. let's  go into the macros that we just saw here called   member load if i want to go in here i'm just going  to right click and go to definition sorry that's   off the screen if i want to quickly go to it just  do like this right click right click the mac and   go to definition and that's going to bring us to  this module here now this module is called member   macros now i've got the associated macros that  are going to help us and this one's called member   load and the only one above is called member  save oh we have a few above it so let's go into   member load and then we'll go up the top since we  were just focused on that so we're going to focus   on this membership first thing we want to do is  clear out all the associated cells of column right   so not only do i want to clear out all the cells  that are located here i want to clear out all the   cells for the billing history and the scanning  out history so i want to do that automatically   this one's got some data in here so i want to  clear out all these cells here and i also want   to clear out all these cells so i want to clear  everything out associated so that one line of   code will take care of that next one i want to  make sure that b5 we have to have a row i've got   to have a row that's associated with this member  if for some reason that is empty we need to let   the user know that they've incorrectly selected  either an id or name so if b5 equals empty then   please enter a correct member or id to load the  member so we could probably put member name here   name or member id that might be a little  more clear or member id okay and then   that'll let the user know we're going to exit  the sub without that member row once we have it   we can put it into a variable called member row  that's a long variable and then as i mentioned   before starting in column 2 2 going all the way  to 14 we can then use another variable called   member column to load that so all we're going to  do is using data mapping and we're already focused   on the member sheet so what we need to do is we  need to find the range that specific cell to put   that data in that's going to be located directly  here and what is coming from the member database   on row one right our cells are located  and the member column so it's right here   and it is this range we're going to place the data  in here remember it's going to come directly from   our member list right here f7 h7 so all we're  doing is extracting those cells and that's where   we're going to get the data i know the row already  and i know all i have to do is loop through and   bring this on hold and put it into h7 bring this  address and put it into f9 and so on and so forth   and we can do that with this loop now these three  lines of code we can use regardless of the number   of columns or fields that we have we can use those  three lines to call that's called data mapping   data mapping okay so we're going to load the data  now all i need to do is show the member picture   right member show picture we're going to run a  macro that's going to show that i'll go over that   run a macro that's going to load that billing  history and another macro that's going to call it   load the sign in and out history right so we've  got additional macros that are going to load the   billing history and load the scanner it makes it  a little bit easier we break it up into individual   macros although it's not entirely necessary and  that's it so i also want to do here's the picture   that we're going to show so how do we do that so  let's go back up to the top of this module and   focus on some of the other macros all right the  first one is member add a picture right i want   a macro that's going to add a picture it is the  macro that's tied to this button if i add that   picture and i will click ok i want that picture to  show up here so we've got a few different macros   one that's going to load the picture and that name  of that picture is going to go directly inside   j20 so how do we do that well first thing we need  to make sure is we actually have to have a folder   here that contains our member picture so that's  very important i get a lot of messages saying   hey the pictures won't load make sure you follow  this training make sure you update this make sure   you join our patreon and get all my pictures too  so this is a variable called member folder member   folder here so i want to make sure that that  variable contains an accurate folder and that   that folder contains our pictures to do that so  that's the first thing that we're going to check   and so here if setup range member folder equals  empty if there's a missing folder let the user   know to please add a member folder and exit  the sub okay assuming that that's not empty   what we can do is we can set a variable a string  variable called picture folder based on the setup   member folder and we're also going to add a  backslash onto that and now what i want to do is   i want to make sure that this is accurate if the  picture folder directory equals empty then please   make sure to select a correct folder member folder  in the setup screen and what we're going to do is   we're going to add that up in fact i'm going to  copy that i'm going to place it directly in here   this is the macro that's going to run that that  particular macro it's going to is one that does   this right here we click this button it is that  macro that browses for the folder all right so   we have that and we pasted it in let the user know  once we've completed those we know that they have   a folder we know that that folder is accurate we  can then move on we're going to set a picture file   it's going to be equal to the application file  dial like file picker right we want to picture   pick a specific file here we're going to add  that picture it is a fixture and we also want   to focus on a please select member picture  you know where i stole that from now don't you   all right please select from the recipe manager  yeah it's a really cool program okay please select   a member picture right and the filters we don't  want to just have any file we want to have only   picture files so do that we want to make sure it's  dot jpg png gif or you can also add additionals   but i've kept it pretty simple right and what we  want to do is we want to select only a single file   we're going to allow multi-select equals false if  they cancel out of it the dot show is going to be   something other than negative one so in that case  we're going to go to no selection it's going to   skip all the way everything else however if they  have selected a file what i want to do is i want   to then extract that and i want to place it inside  a path so what i want to do is i want to set that   full picture path that's going to be a brand  new picture path wherever they have pulled that   picture from what i want to do is i want to place  it inside our member folders so to do that we have   our picture folder and then what i want to do is  i want to extract the name just the file name only   not the entire file path to extract that name  we can use the directory function the directory   of the dot selected items in other words this is  the entire path of whatever they've selected the   entire full path but i only want the name so i'm  going to take that name and i using the directory   it's going to extract the name so if i take that  name and i add it to picture folder that is going   to be our brand new file path right so if for some  now what i want to do is i want to make sure to   see does that full picture does that full picture  path does it exist already so if the picture path   equals the selected items in other words how would  that happen that means if we're already picturing   if i'm already adding pictures so let's say what  i want to do is i want to copy this picture i want   to put a picture from somewhere desktop inside  let's say i want to select this picture or one   of those pictures and i want to put it directly  inside this folder how do i do that but what if i   select a picture already inside the folder what if  it's already there well in that case i don't need   to copy it from any its existing location into  this folder because it's already there so how do   we account for that well we check if the picture  path is equal to the select items meaning the same   path that we want to move it to then we don't  need to copy it over we're going to skip the   copy and go all the way down here however if it  doesn't exist in that picture folder what we can   do is we can then copy it over but we only want to  copy it over if for some reason it doesn't exist   if the picture we probably don't need this line  right this one here means it already exists   inside the folder so i probably wouldn't we  probably don't need this this probably can get   rid of so basically this line says if it exists in  the current folder then kill it but we've already   checked it here so we don't need both however if  you want to do that right i'm going to comment   this out but i'm going to keep it there however  if you want to just delete it from its current   location then you can do it but we don't want to  delete it from its current location we've already   checked that it doesn't exist it doesn't have the  same path okay but we'll keep it in so let's say   here's here's there's a scenario where you could  have both let's say here you you're adding a   picture it is possible you're adding this picture  here but you're taking the exact same picture   it's already on your desktop but it's already  in this folder in other words the same pictures   in two different locations however so what you  want to do is you're going to add it from the   existing location you may want to delete it here  first before you add it in so we'll keep that   in there in the in the rare scenario that you  have the same folder so what this will do is it'll   check does it exist in the existing folder if it  does it's going to delete it so we'll keep it in   remove the picture if it exists okay next up now  what i want to do is i want to copy it from its   current location into the picture folder so we  can use the file copy we're going to use the   original location which is the selected items  which is the full path and then we're going to   paste it over into the picture path which is the  updated the destination file it's going to copy   that member picture into the member's picture  folder okay all right so that's it so now all   we want to do is i want to take the file name  of that and i want to place it directly inside   j20 here so we can do that here members j20 here  equals the directory to select that and remember   that directory takes only the name not the full  file path it's going to set that picture name then   what i want to do is i want to run another macro  that's actually going to display that picture so   a different macro to actually display it and  that's the macro called right here member show   picture first of all that picture name is going to  be located in j20 as we know we've set it here but   if that's empty then what we want to do is clear  the picture right there's nothing there's a macro   that clears the picture we'll go over that macro  next we're going to exit this up but assuming that   we do have a value inside j20 i want to set i want  to set that into a picture file so first of all we   want to extract the picture folder from the member  folder here i want to add the backslash just as we   did previously then what i want to do is i want  to make sure it's accurate so that picture folder   right if it's inaccurate it'll be empty please  make sure to set a correct member folder in the   setup screen and we're going to exit this up then  what we want to do is we want to add a picture   path and then that picture path we could probably  do this probably dude let's do this i'm going to   set the picture path first and then we're going to  double check this is probably set the picture path   first the picture folder along with whatever's  in g20 then i'm going to check the entire path   this is probably better then we're going  to check the entire path of that directory   if that's not accurate please make sure to select  correct member folder okay all right so now we're   checking to make sure that it's the correct path  if it is then we can do the following right what   i want to do is i want to add that picture in  here but we have this particular picture here   this one here called picture frame here the name  is called picture frame that's part of a group   right it's part of a group called id group so when  it's part of a group i want to call that specific   picture out the one that's it's part of the  group items it's one of the items of that group   so it is the id group picture frame that we  want to do i want to take this little frame   here and i want to fill it with a picture right  if we want to change it we can give it that round   look that might look kind of nice so we can  do here so it gives us the ability to do that   if we want to do so what i want to do is i want  to fill this frame with that picture we know the   path of that picture so all we need to do is based  on this shapes id group picture frame fill it with   user picture and that user picture the path is  here picture path that's going to set the picture   and also in our clear picture we're going to  make it hidden right so what i want to do is   also want to make sure it's visible just in  case it got hidden which it will in this one   i want to make sure it's visible so we need one  more step to ensure that anytime we hide a picture   we need to make sure that it's visible so also  the fill visible equals true ensures picture   is visible okay we want to make sure that it's  visible and that could happen it could be hidden   because we're going to clear the picture that's  the next macro so this macro is going to display   the picture this is going to clear it right if i  want to clear that picture i'm going to do with a   single macro it's going to just going to take that  default picture that default picture set up right   here based on this based on lookup and i've given  it a name called default member picture so in that   i want to take that and clear it so when i add  the picture again it adds it up or it clears it so   that clear picture this the macro tied this button  is going to clear out that picture and we can do   that with the following first of all i want to  set that default picture based on the setup screen   and that default member picture the value  here that's the default member picture usually   i use that but you can of course use anything you  want default member picture so we're going to set   that into a string variable and now what we want  to do is we want to make sure that that is correct   i want to make sure that that path is correct or  there really is one if the directory of default   does not equal empty right meaning it is an  accurate path then i want to do the following   first thing what i want to do is i want to fill  that same picture frame based inside that group   that member shapes id group group items one of  those items is the picture frame that picture   frame i'm going to fill it with the user picture  this time it's going to be the default picture   and i also want to make sure again it's visible  equals true visible equals true however what if   it's blank what if there's no picture at all right  what if the default picture isn't correct or maybe   we haven't set it up then what do i want to do how  could that happen well maybe we have an incorrect   default picture if i change this incorrect if i  change just the name of this right default pisk   or whatever it's now incorrect right so we don't  have a default picture if i do clear picture   i just want to clear the picture notice we don't  have a default picture because i changed the name   of this so when that happens we want to what i  want to do is i'm going to clear whatever is there   so to do that notice it got cleared now if i set  clear i just reset it so to do that i want to do   this else right no i'll just call it no default  picture so in the no default picture all i want to   do is make the visible equals false right so we're  hiding whatever is filling there if we do this   right and we try to show it it's not going to show  again we have to make it visible equals true again   so when we take this step it's hide hide pictures  so that was the only way that i could there is no   i wish it was like something else like clear it  should be there should be a thing thing says fill   dot clear that you can clear the picture but i  don't see that i don't think it exists right so   visible equals false works but then you  have to make sure it is visible again and   make sure it's visible equals true right it  would be nice if there was a feature that said   fill dot clear and then just clears whatever's  there and it deletes it but i didn't see that   all right so high picture will do just fine  for us and we need to show if you guys do know   another a better way around it do that because  that way i could avoid this and i could avoid   this if there's simply just a clear option but i  didn't see that so clearing the picture that's it   it's a nice way to show default pictures clear it  if you don't have a default picture and show it   around so we'll go ahead and add a picture to  this person here and now we have it the names   don't coincide with the name so don't worry about  that all right continuing on so now we have that   but what now what do we want to do is we focused  on the load we know how to get the data in here   but when i click save how do we update this data  how do we take all that data and make sure that   we save it back into the member list whether it's  an existing row or brand new row right if it's a   brand new member we'd also need to save it to  a brand new row if we click new member and i   put in a member name called freder's i want that  saved to a brand new row saving the member here   it's going to be saved to a brand new row here so  how do we differentiate know what's in existing   well we know based on this right if i add new  let's click members and click new one we see   that there's no member name there's no member  id we've cleared out the member id here right   excuse me right here located in b4 we've cleared  out this so as we cleared out we know there's   no row no row in b5 if b5 is empty we know it is  a brand new member however if we load an existing   member we see that b5 contains the row number so  b5 is going to be our differentiator okay so first   of all we want to ensure when we're saving we want  to make sure that they at least have a member name   so f7 is required so if f7 equals empty please let  the user know to add before saving that name we're   going to exit out okay again we're going to use  our differentiator b5 equals empty this would be a   new member otherwise it's an existing member new  member so if it's a new member we need to get a   brand new row it's going to be the first available  row located in our members database and then what   we're going to do is we're going to assign it a  brand new member id that new member id is going   to be coming using our max formula located in  b6 i'm going to take whatever's in here i want   to place it directly in b4 i also take whatever  is here and i want to place it directly inside   column a that brand new row everything else is  the same regardless if it is a new or an existing   so we're going to do just that b4 is going to  take on whatever's in b6 that's our new member id   and also i want to take that new member id  i want to place it directly inside column   a and that row that we've now assigned so  that's going to take on the new member id   these are the only few things we need to do  if it is a new member however it's an existing   member all i need to do is want to extract  the row located from b5 that is our member   row okay so once we have our member row  then everything else is the same and again   another three lines of code regardless of the  data using the data mapping and all we need to do   is go from 2 to 14 just as we did before we'll  just call this data mapping and the data mapping   again is simply doing exactly the reverse of  what we did here right when we're saving them   here all we need to do is just take excuse me  all the way down here the load loading up here   all we're doing here is taking whatever's inside  the database or placing it inside the form   however in saving it's completely the opposite we  are taking here whatever's inside the form here   and placing it inside the database here based  on whatever is located in row one that cell is   located in row one right here located in row one  so all we're doing is we're taking whatever is in   f7 h7 or f9 and placing it inside that associated  row that's it that's it for data mapping and then   also what i want is a fade out message it's a nice  message right when i save that you may have seen   that before let's scroll over here and i decided  to say that remember we see there's a called   member save you see that you can't show it up real  quick here and that's let us know it's kind of   it's a nice way of showing that it's been saved  without that pop-up in which the user has to   like if we have a message pop up the user has to  say click ok and it gets kind of annoying this way   they they're notified that it's been saved but  they don't need to do anything they don't need   to take any action and we can do that with a fade  out message and we've used several in this program   we've gone over it before but basically all we're  going to do is we're going to create a shape   this one's called member saved messages it's a  simple shape and we're just going to run a loop in   this case from 1 to 100 and we're going to change  the transparency of that shape based on this   if you want to make it slower you can add times or  you can add here to make this 200 200 and then all   we're going to do is just run a loop with a short  delay and as it fades out and though so the first   thing we do is make it visible the last thing  we do is make it hidden and then we add we can   increase the transparency as we loop through it  relatively simple okay the member load we already   went over and now we have the load this one's  loading the sign out history we have two different   macros those different macros are going to do two  things one's going to load that billing history   if we have let's pull up some another particular  member that has data billing history and that's   going to be all the invoices associated with that  member we also have the scan the internet history   based on this person coming and going inside  that screen coming directly from this screen   so i want to load that information now that's  data located so our billing history we've got that   located inside our invoice list item list here  invoice title let's pull this invoice list here so   what i want to do is i want to know specific  invoices for a specific member if i if i have all   of our invoices that were created and i know the  member id that's associated with it what i can do   is i can then extract only those invoices based on  a specific member and put that into some criteria   then what i can do is i can extract those results  into a table and then bring over those results   and bring them directly inside here and that's  just what i've done here using an advanced filter   so it's a very very simple macro all we need to do  is in this case we're focused on the invoice let's   go on invoice list first then we'll go the invoice  list we're going to determine the last row we're   going to if it's less than 3 then we're going to  exit the sub we're going to run an advanced filter   a2 through f we're going to have the criteria i2  through i3 and we're going to have those results   come k2 through n3 and that's just what i've  done right here inside the invoice list last   row getting it running our criteria i2 through i3  the results are coming from k2 through n once i   have the results we're going to determine the last  row if that last row is greater than 2 we're going   to take that and just bring that information over  we're up to a limit so also we're going to do that   in fact i need to set the limit here but what we  can do is if we know i'm going to update this if   i know that we have a certain number of limits so  we do in this case our limit is let's say 59 so   our limit here is 31 rows 31 rows so all we need  to do is just bring over 31 rows of data right if   i know that last row here inside our invoice list  so we can focus on that 31. so inside our invoice   list here right all we need to do is bring over  31 rows so if we bring over all the data from here   all the way until let's say row 33 that should be  sufficient i'll make that update right now because   we have a limit once we're in a limit right so we  don't necessarily need the last row right we can   do we can if the last row this is fine up until  now but what we're going to do instead of this   right we know we have a limit right we can just  bring over all the data so we can do it for e29   through h and what let's take a look back on this  screen here e29 all the way through let's take e59   59 being the last row why don't we just set that  here so instead of the last year this works great   when you have an unlimited but we have a limited  row limited right on this one at least we do   the billing history kind of theoretically we don't  have it in the scanner history it is our last tab   we could go on forever notice there's a picture  background here i wanted to change something   up here so this is not a color cell color it's  actually a picture background okay so what we want   to do for this specific tab let's set a limit of  row 59 so it doesn't bleed into the other tab to   do that we just need to set a limit so 59 is a row  so all we need to do is say h and then change this   to 59 and then put into quotation and remember we  also want to make sure we have the same number of   rows 31 so we just change this to 34 right so all  as we did on our results here let's look back in   our invoice list here right we want to go all  the way down to 33 that will cover our rows 33   here highlighting those we see we've have 31 rows  that'll be sufficient because we have to include   that so 33 and that's going to bring over all the  data regardless or at least up to that right now   let's say you're creating a large program but you  only you only want a limited show you only want   to show this many rows but what if a user wants to  see all of the data well in that case what you do   is you create a little bit of an icon here saying  something like show all data and then what you do   is you send the user over to this screen and you  run an advanced filter or to another screen where   it shows all of the data based on that member so  you could send them to a separate screen that's   what i would do and that's what i've done inside  the accounting program in our mentorship great so   there are options of course that we have so i've  made that update here so when we click on members   and we click on the billing history no difference  in the results right because we have limited but   inside the code there's a slight difference  scan on history is fine as it is scanning   out in history load the scan out we're going to do  exactly the same thing but this time we're focused   on the sign in and out database focused on a  different database so if we look on this database   something very very similar here just all the  data that's for signing in and out right we have   our member id the date the sign in date the sign  out date and time any notes and the row associated   with it we have our criteria here it's also  based on the members b4 just as our invoice list   here also based on members b4 right it's based  directly on the link that's associated right here   that member id here always based on that criteria  member id so we're going to run that criteria and   again i have the results in exactly the same order  as i want them all i need to do is bring in this   data and directly in here so that's all we're  going to do with this making sure that we run   our advanced filter getting the last row and then  as long as we have data we're going to bring that   data inside here the log in our history right here  this sign out history so bringing that data in   here exactly the same just the different cells  okay add new is a relatively simple all we're   doing is simply clearing out the cells for add  new we're running the macro to clear the picture   that's why it's nice to have that in a separate  macro we're going to set the tab right i want to   automatically run the macro to select general info  select general info tab why is that important well   it's kind of a nice feature if i'm looking at a  specific tab right here sign out in history and   i click new member maybe i want to go directly to  that general info so notice we've run the macro   that's not only going to clear out the fields but  the macro that's going to actually select it so   that macro is going to do that for us it's going  to automatically do that okay that's it and i want   to just select something else member f7 select the  different cell all right great so now what we have   is we've got gone over a lot of macros we also  want to do a few things i also want to create the   invoice right creating the invoice now this is a  great feature first of all we want to make sure   that we have a saved member i want to make sure  and if i create the invoice i want to make sure   that they have a membership plan associated we  can create multiple membership plans we'll get   into that in just a moment but this membership  plan is going to let us know more information   i'm going to save that member and now i know  what membership plan to bill this customer our   membership plans are located right here membership  plans have a plan name they have a plan amount   and they have a description and they also have a  frequency how often are we going to bill it that   membership plan is saved on the table below so  if i know it's got an associated membership plan   when i decide to create the invoice for that  member i know automatically what item to bill   i know the description i know the price of that  and how much to build them and i also know that   all the frequency that's set up so when we  save that invoice it's automatically go save   and create an invoice and then it's going to  automatically reoccur automatically okay so   how do we do that how do we automatically notice  if i change the member here to lisa here who's   already got monthly gold let's change her to  monthly bronze so we can save that member here   and then we create an invoice we notice now it's  monthly bronze we notice that our member is now   lisa we notice it's got different frequency and  a different price right so it's automatically   created so how do we do that all right well let's  go over the macro that does that it&#39