How To Create Your Own Drag & Drop Document Workflow Manager In Excel [Free Download]

hello this is randy with excel for freelancers and welcome to the document workflow manager in this week's training i'm going to show you how to create this incredible document manager complete with drag and drop complete with multiple different workflow types you'll be able to automatically create thumbnails based on word documents pdf documents pictures and a whole lot more i've got so much to share with you in this training so let's get started all right thanks so much for joining me today i've got a really fantastic training called the document workflow manager if you've worked in a large company getting documents approved or through the process between managers can be difficult cumbersome and a real pain it's hard to get them up the chain of command organized and in a nice timely manner a good application to help us with this is a great strategy so how are we going to do that well we're going to use this document workflow manager excel is a powerful tool when we apply a little bit of vba some visual helpful hints like these thumbnails we can create a powerful tool for any type of combination when you add sharing and sync in to this it can be shared in a shared folder across all deploys in a company and they can also have updated document management so that means when a document is created regardless of it whether it's simply time off or sales order or anything that you deem important for your company you can create this so that it passes through each manager each manager can then have their own revision they can write review notes and they can set their own status and you can also browse for any other file or you can simply move it to the next manager for approval just like that it's going to be a great training i've got so much to share with you in this application even if you're not really interested in creating a document workflow manager there are so many skills that you're going to learn in this master class including automatically created thumbnails including drag and drop including scheduling purposes or we have saving records data mapping and tons and tons advanced filters and tons more i hope you'll stay with us through the entire master class if you do like these trainings i create these each and every tuesday absolutely free there's some great ways you can help us out simply by starting to subscribe don't forget to click on that notification icon bell we create these every tuesday so that'll get you notified of these brand new trainings each and every week this download application is absolutely free but using the links down below if you like to use your email or with facebook messenger but if you want 200 of my best applications i've got that available also in a single zip file that's that's just 77 right now and it also comes with an incredible library and that means a single click to open the application or a single click to simply view the original training just like we're doing right now that's an incredible library i hope you'll set that up i'm going to create these for you so let's get started we've got a lot to cover i'm going to go line by line code for code in this workbook plus everything you need to know to be able to create your own document workflow manager or simply to create increase your skills to create your own applications in excel my goal is not just to make you successful with excel but to give you the tools and tricks and tips to give you all of the things that you need to get your career started in excel or create your own applications for sale let's get to it the best way to start with that is just to give you a general overview basically what we do is when we have workflow types we can create unlimited types of workflow types when we select on a specific document here all the information whether it's the document name the originator that's the person who first requested it and the workflow type we also have the requested on the date and the original file name we can browse for it we can open the document simply by clicking on here it's going to open it and that's regardless of the document we can then send it off to a different supervisor simply by either drag clicking on it dragging and dropping it or anything else so we can just move it along right here and it'll automatically move to a different thing or of course if there's no manager selected when we select on a document all the managers the supervisors in that entire chain automatically get displayed those particular supervisors here are based on the document type so notice there's no supervisor because we haven't selected an originator the first person based on this original is that chain of supervisors so how do we know that well let's get into some of the fundamentals of this application and then i'll walk you step by step how we create it so we're going to start with the admin screen we have a shared document folder right we need to know where those documents are what we want to do if you use a let's say a dropbox your shared documents can be throughout your entire company that means every single user could get an application like this they can be shared throughout the entire company if you share the data using one of my sharing and sync techniques that i've just demonstrated in an older video you can then share and sync this application with them it'll all be synced up but we do need a shared folder so we have that here we also need a shared thumbnail folder when we upload a file we're going to automatically be cr able to create thumbnails so for example if i browse for a specific file and i have let's say this receipt here or a pdf or anything or jpeg and i want to create a thumbnail for it it's automatically going to create a thumbnail so when i save and update that it is that thumbnail which is this one right here that automatically gets updated based on the document that we have done so it's a great way to create those thumbnails we're going to be doing again on pdfs pictures or word documents even if you have a word document you can do that too so we need to keep those thumbnails in a specific folder so i've created just basically two folders here i've got our documents folder here and i've got our thumbnails here so our thumbnails are created dynamically they can be pictures or anything else and our documents are uploaded and copied so basically when we a user simply clicks here the browse what that's going to do is going to look for wherever that's located it's going to upload that so if i want to change that to a pdf i just click here pdf it's going to automatically change that to pdf save and update that notice that that thumbnail here got changed to a pdf just like that so we'll show you that so we've got those two folders i also want to know the company hierarchy who's the top of the chain that company vice president so we have in this is generally it starts out with an office staff which is the lowest employee and then it just goes up team leader office manager supervisor regional manager this is really important because we're going to have to know who's on top of who we also have dynamic workflow types really really cool because we can then create workflows based on that so for example maybe you want to request the time off maybe there's only two managers that are involved in this that has to be improved but maybe you have a large expense right like above a thousand dollars and we need to get that approved by several managers here so in that case this type of workflow type will be different so notice here when we select sales order we have a certain set of managers here we have a work order we have just a few different managers just five different managers or an expense less again five different managers and we have a larger one we don't need this chairman here we have a larger one we have a project bids or invoice so we can create what's called dynamic workflow types and we can do that inside the admin so for example the time off we just have these four managers we can then select which type of a manager again sales order would be large so here what we can do is we can select all the manager all this managers type at least they're positioned on whose supervisor it starts with the office staff then it goes to the team leader then the supervisor and then for the final approval the regional manager so we can crew not only can we create any workflow types we can then create the hot entire hierarchy of manager that has to approve that type and what i've set up to let's see what do i have about here uh nine or ten different ones here so it's really really great that we can do that and we can create that you can create more if you need it but i think it's a good start so then we have dynamic floats and then we have document statuses right what is the status what is the initial status the pending status when it's being reviewed once it's approved or is it rejected or it needs revision and maybe it's finalized right so once it's finalized we don't want it to appear in here right so if we decide we're going to finalize a document right we want to set okay we're going to set this to finalize we're going to save and update that we don't want that that document's now gone it's no longer here so only those pending documents that's going to keep us really organized so it's easy to finalize a document so we're going to have that too so that's it for the admin screen relatively simple we have our workflows which will be going to detail i've got a document database this is where all of our documents are stored right notice that one that we just said was finalized right we put that one that's finalized here but if we change that to pending review it's going to show up again into sales orders so our particular document list contains a document id a document name the originator the original staff that created that we also have a workflow type right what is the type of the workflow that we're going to need to know because they're separated based on workflows so when i click sales order again notice that appeared back here because we now set a depending review inside the database we have the work orders right we have expenses we have notice that we can use receipts and pictures project bids and all and pretty much everything else we also have the quantity how many are here i want to have the quantity here okay so we have the requested on so all the data the original file name and the thumbnail that gets automatically created where is that located i need the file name i know where it's going to be located it's going to be located in this folder here but i certainly need the file name i know the original file name the details or the notes of that and that's going to be linked up who is currently reviewing what manager is currently reviewing this what reviewer position what is their position right are they the office manager who's reviewing this and what is the current status of the document is it pending review has it been approved does it need revisit you know does it need more review or has it been finalized or revision right sometimes we may want to also send this back to the originator right it's got to go back to the origin or notice debbie was on the originator so if i want to send this back to the original i just click one button it's going to be sent back to debbie here notice i click on that again it's back to the originator or in this case debbie right debbie here back to there so maybe debbie needs to to redo it or something like that so you know it's been rejected and the manager can put some notes on that so single click to move it also again we can do use drag and drop we can move a document along just by clicking on it and pressing this this mark next to proof or we can send it back to the back manager simply by sending it back to the back so we can move these documents along the chain of the managerial approval process simply by doing this now i've never actually worked in a large company i get fired from most of the companies that i work for which is why i work on my own so thank you very much sami for your advice and inspiration on this you helped me create uh at least the ideas for this and then i take it a little bit extra step so i don't know he knows how large companies act and so that that kind of helped me out so i kind of understand the process flow that so thank you sammy and we're going to continue on inside our document review now notice that there's multiple reviews per document so we need to track those in a different list here right so we've got that and we also have the revision file we can add a file onto that notes and so we can add a lot of information onto that if we want to okay we also are going to need to show that i need to filter that in other words i want to know all of the revisions or reviews for a specific document so we're going to need to run it through an advanced filter just like we do here so for filtering document 8 right document id 8 i only want those results going to come here and then those results are going to come into my workflow sorry so i know that these three types of reviews have been completed on this specific document that we've selected okay so that's why we need to keep track of this in a separate database i also have a staff list now our staff list here is going to come with an id staff name a position a supervisor if there is any an email which we do i think i'm going to think on our patreon platform i'm going to add email automation to this so if you haven't joined our patreon now's a great time to do that every single week i create additional features for these workbooks and a brand new training video something that maybe is featured or something that is a fix or something that maybe i'll take a focus on and i'll put that on patreon along with a whole pdf download and advanced trainings and a whole lot more on our patreon so i'll include the links down below if you want to join us there and possibly a picture i haven't really used the pictures but i think you know there might be a way we can incorporate staff pictures which should be kind of nice here right wouldn't it so maybe i'll add that in too you know into the patreon so workflows maybe i kind of run out of space but maybe a staff picture here i got a little space there something or maybe maybe a little uh circle staff picture here would be nice so i kind of put that in the database thinking maybe if you've got some ideas i'd love to hear them so at least we have a column for staff pictures and then i've got the row the team members that might be helpful moving on but nothing that we need to do so what we do is we want to know there's staff and we may want to put them through so we may want to run through advanced filter but for our purposes in this training all we're going to be using is basically the name the position and that's it for this particular training then i got a blank email sheet that just gives you an idea of where we might want to go with this right now it's blank but if you want to see this become active let's go ahead i might put some templates in here so got lots of ideas for a patreon platform additional training all right so let's get into it so that's pretty much it an admin of workflows and two databases containing the documents and containing the reviews and then a staff list so that's it so how we go into this well let's start with the basics right just a few things that i want to know we've got some named ranges that i want to bring to your attention because those are going to help us both not only in the formulas but in the code so let's go over some of the named ranges that i have created that's going to be in the formulas and name manager okay now these criterias when you see these these are created automatically by vba when we create those advanced filters so of course most importantly i've got a document id that is going to be in dynamic named range based on the document id so as we create them using the offset formula as we always do offsets can help us so as our document ids grow so is this list so document id we've also got document status i want to know the status of that and that's going to be here on the right side here current status so i want that in a named range that's very important because when i count them i don't want i don't want to know which ones have been finalized right i don't want to count that i only want to count those that are currently working or in the process or moving up the chain anything that's been finalized we don't need to count so having that status is really important i also have the document type i need to know the document type sales order work order expenses that's going to come in handy when we count them i need to know how many sales orders that are not finalized and things like that so that document type will be critical again extract ranges those of course are going to come automatically through vba when we create those advanced final now i have one i've got to create a named range based on a single cell and that's going to help in vba that's our finalized named range so that's just basically a final we know that final is cause a bit we also have the hierarchy that's very important because we need to know the company hierarchy and i also have one what's called needs review so if new excuse me needs revision or needs review either one would be fine needs revision we'll stick with that's going to be a named range for a single cell based on b25 same thing with pending here pending is also pending review that's helps us out on the code and once we get into the code you'll be able to see how having a named range on a single cell is very convenient and it's also much easier to read the code and we understand exactly by reading the code what that potential and of course reject it also we have that too so we have a few of them name range i also have some named ranges for the staff including a staff id which is also of course dynamic named range same thing with a staff name and same thing with staff position okay so we've got those named ranges and i also want to know the staff supervisor so those are name ranges based on that and i have a status right i want to know what the status is all of the document statuses name branch for that and then of course one for workflow types those are the workflow types in the admin again using offset so as our workflow types grow so does that so we can add and update workflow types they'll automatically be linked here so how do we do that well we simply link it whatever is located here you see if i select the large range and admin d7 is going to automatically appear here so basically link this list i just basically copied this list and i pasted the links directly in here pasting those links that's going to link it up okay so but what i want to know is how many time off how many sales order i've got that quantity here that's going to let us know we need to know that so how are we going to count that well we can simply use countifs ifs document types based on what d16 is right i want to know all the document types for d16 but not every single one of them because i want don't want to exclude those that have been final right if they're finalized i don't want to include them so the best thing to do is basically say count if all another criteria for the second would be the document status does not equal final this is that named range named range remember we have a name range called final so mark when i backspace this would start typing in we see it is that named range remember that's the same name range that i used right here so we click finalize we see it's final up here so that's why it's much easier using formulas much easier using code because we can clearly see it so it makes a lot more sense as opposed to connecting that with just a single cell where we might not know where that cell is so basically all we're using is counters that's going to let us know to count all the ones that we need right and if it's 0 we're just going to show nothing so that's how we count it's also based on remember all we're basing it on all the sales orders are all the ones for this but not including anything that has been finalized right the status is not financed so that's it that's all we have to do here now i've got some information in columns a and b for the admin this is generally hidden and let's go over some of the fields here so what i want to know is when i select the document how many filled in fields some of these when i click a new document right i need to know i've got five fields that are going to be required document name the originator a workflow type a requested on now inside the code i could do if f3 equals empty or h3 equals empty or you know it's a lot of code or what i can do is simply count the cells of those which contain text and put them in a number and we can use countif for that right so as soon as i add one it's going to count that and i've got that formula right here located in b1 what we're going to do is we're going to count a and that basically is count all the text of the following cells count the cells that contain the text so of these five cells we're going to count them right now we know we have one of them filled in if we select a specific document here we see that all five have been filled in and therefore it's 5. that way in vba all we need to do to ensure that those five required fields are filled in is simply say if b1 does not equal 5 then let the user know so just something like that and we also use conditional formatting to call these yellow so that conditional formatting as soon as we see new document as soon as we fill it it's going to go to white that's using conditional formatting and basically we've just applied a single conditional format to multiple cells here in fact i probably just need a single one here and i'll delete one and double up the so basically this formula is just for these cells right and i'll include this one as well here so all we need to do is just select the cells here and just make sure that if they're blank cell contains a blank value then i want to call it yellow so when we edit the rule we see its cell contains blanks and we're going to format that yellow that's all we have to do right so as soon as we select something we see that they all go to white okay so that's it for that so we have that now we have a document id this is placed by vba when i select a document that document id changes we need to know that document id that is the id that's located here so if you follow any of my trainings this will look a little bit familiar this combines a lot of training such as the kanban which we created a few weeks ago a great training it combines thumbnails which we created a while ago it combines drag and drop scheduling which we've created so basically we can combine all those skills together to make one very powerful application so we got the document id now i need to know the document row right i need to know that 7 id 7 is on row 10. so how do i know that well i'm going to use match for that so we're going to use if air just in case it doesn't match we're going to match b2 whatever's in b2 that's the id based on the name during this document it is and we want an exact match we're adding 3 because we know our first one starts on row four so we always need that three because i'm not looking for one i'm looking for the row number so in that case it would be four if one was selected okay i also want to know the thumbnail remember for each document we're going to create a thumbnail it is that thumbnail picture that's going to appear inside this shape here i need to keep track of that thumbnail i'm going to put that in b5 okay i also want to know the original staff id this could be helpful moving forward so the original staff id is simply taking care of that we're going to use index we're going to index that staff id we're going to run a match based on that staff name located in h3 we're going to run that match based on the named range and we want an exact match in the column one if there's an error it's gonna show blank so that's gonna get us the staff id i also wanna know the original it's called called the originator row the original let's put in the word staff so we know that's the originating staff row and the originating staff position and their supervisor right so i want to know all those things and i want that's because that's going to help us moving forward so we want that all that information here to show up here so to get that row again we're going to simply use a match in this case we're basing it on the staff table so we're going to add 2 why are we adding 2 in this case when i find that staff list notice our first one starts on row 3. so if our staff id is 1 i know that add 2 to get 3 that's going to help us with the row so we know and i also want to know what their supervisor is now i want to know their position we're going to use index i don't we don't necessarily need all these fields but they're very helpful for when we add in features so i want to know their current position what is their position so we're going to use we're going to run an index based on their position that's the named range we created we use a match based on that staff name that's going to get us their position i also want to know what their supervisor is remember inside every single staff that contains a position that contains a supervisor unless they're at the top of the supervisor and we also have email and picture and a bunch of other stuff so this is all for staff when we change the staff on here we know that we're going to automatically update that automatically okay so everything's going to update on that all right i also want to know some information for the staff that's reviewing that right the staff right if i change let's say we change let's say we got debbie and in this case her reviewing her manager tina james is reviewing that they're just set for pending review right it's waiting for tina to review that so we want to know their information so i want to know tina's uh staff id number using index match i also want to know the road that where stina tina is located and the staff and what position she is and who is her supervisor so i want to know that as well okay i also want to know the selected review database when i select a review i may want to edit one of these reviews so i can select on it and it's going to show up here so that's very very important we need to know that so i need to know what row is selected in this case it's going to tell us that row in this case 5 is going to show up right inside here that is 17 this select review database is the database row that's located right so basically we save this 34 on a database here located in row 34. take a look at that seven right that's the id 30k hopkins and pending review so that is the database row where it's located if we need to make an update i need to know that review i need to know the database right so what is the database here so this keeps track of the days that way if i make an update right if i say test notes and i make that update it's going to automatically save when we update that on row 64. so we look at row 64 here and we can see that those notes have been saved right here in row 64. so we need to know what row and column to save that in so we've got 64 and we know the row so that's very very important okay so we've got to have the database row now also we have the selected here's where we come in the selected flow row right what is the selected flow row when i select here i need to know what is our selected right remember we have different workflow and i've got conditional formatting that appears on these right so i need to know what row appears notice 16 17 18 right so we need to know what row because it is conditional format is going to help us and again i've got conditional formatting based on these two just simply based on the selected row so as you see here b15 equals rho that's the format that we've given that dark background along with the white bold font that is the one that we use and it's going to apply to d through e so it applies to everything all right so we need to know that row i need to know what type row two right notice there's a specific type this is one type row how do we know what that is right time off is one what i mean one here it's the first type second type third type right i want to know that because i'm gonna have to extract that information so i need to know the type row so that's the row that's located on basically two three four so it goes on and then okay selected review row this is where that review comes in right here when we selected we just went over that that is the row that we're going to use if we select something that review row 6 because conditional formatting here is going to help us highlight that row b17 b17 equals row conditional formatting again help us recognize what row we select in this case it's b17 okay the last thing this is going to come in handy when we go through the drag and drop macro right when i select a specific group now i want to know the left position and the top position because if it's been moved i need to recognize that there's been a move i need to know that it's been changed and i need to automatically update and refresh it and not everything so i'll go through that with you very very simple believe it or not okay so let's continue on so we understand the basic format of all this application right we understand that we can do that let's go over some of the basic types of macros and we have the ability to open again as we mentioned we can open any pdf just like that and we can also browse for new documents right so let's go over the macros and we have those both for the original document here we have the same similar macros for the revision if i want to upload a revision i can do uploader revision just like that and it's going to automatically upload that once i save and update it's going to save to that so each tip specific revision has that ability to so how are we going to do that well let's take a look inside the vba developers tab visual basic or alt f11 is the shortcut there we've got document file macros these are the macros for the document file okay they're all going to use a very similar variable so we can dimension those all above here i need to know the thumbnail folder and i need to know the document folder right those are the two folders that we're going to focus on your documents and thumbnails we need to make sure that they're put inside the admin right if i need to save those documents and save those thumbnails i need to know what folder to put those in so we need to ensure that those are accurate so also i need to know the full file name and i need to know the full file path i also want to know what file type it is that's going to be helpful when i create those thumbnails i need to know what file type because we can also create thumbnails based on word documents i want to create that on thumbnail i'll show you how that works all we need to do is browse for that we've got a word document here just click ok it's automatically going to create them for the word document and all we need to do is just refresh save or update that it's automatically going to refresh and we see now that that word document all the way down here this one right here is the one we have so this is the one that we just created when we open that we'll see it's going to open a word document automatically here and then that'll be that so we can even open word documents a lot a lot of really really cool features here all right so continuing on so how are we going to do that so we need the file type i need the thumbnail path i need the thumbnail name as a string right i need to know the thumbnail name and i need to know the document folder as a file dialog and the object chart is a chart because we need to create those charts that's going to help us create those thumbnails so the first thing what i want to do is run a macro right before this create i create a macro just to check for the photos i'll run this macro every time we do so we want to make sure that the user has uploaded proper folders in for both the thumbnails and the documents so the first thing the document folder is going to be based on d3 then we're going to add the backslash to it that's our shared document folder the directory the documents folder equals empty or the document folder example let the user know please set a shared document folder in the admin screen okay also we're going to do the same thing for the thumbnail which is located in d4 we're just going to check and make sure that that has an accurate file path if not we're going to let the user know so that's it so all we need to do every time we browse is just to run this macro and the first one is i want to browse for the original document that is the macro that's been tied to this if we right click on any specific shape inside the group and of course we click assign the macro we see that is this browse original that we've created so browse original is the one we're going to first we're going to run the macro check for folders as we do each and every we're going to set the doc file as the application file dialog mso dialog file picker we're picking a file not a folder so this is going to be file and with the doc file i want to assign a title browse for original document and we're going to allow multi-select notice there's no filter in here no no you know sometimes we add picture filters or sometimes pdf folders or text documents there's no filter we're allowing the user to upload any type of a document and that's really handy with this we're going to the file name is going to be based on the directory of the select item so what that's going to do is extract just the file name not the full file path just the file name this is the full file path right here selected items one that's the full file path but the directory of that is the file name only extract let's write that in extract file name with extension my auto hotkey does end with every time i type in with it says end with which is handy usually but not always workflows that's our sheet name right our sheet name here this is called our workflow sheet based on the vba h5 equals the file name so i want to take that file name i'm going to put it directly inside h5 once i have that i want to check does it exist i want to take this file wherever the user came from but i want to put it in a specific folder i want to put it in this document folder but if it exists already inside this document folder i want to make sure that we delete it so we check that with a single line of code if the directory document file name vb directory does not equal empty that means it's already in our doc folder plus our file name combine those as our full file path if it exists does not equal np then we need to delete it because we're about to copy it over from wherever it's located from wherever the user has browsed for it we're going to copy it over into our designated folder once this folder becomes shared it'll be shared with all the users so we want to kill it meaning basically delete if it already exists delete if already exists so once it's been deleted we can then copy it we're going to use file copy very easy we're going to use the selected item remember this is our full file path of its original location we're going to copy it basically i want to copy it to our document folder and our file name don't forget we've make sure that we always add the backslash on because our backslashes are not located but they're automatically added in here inside here so once we run this we create this here this automatically create these variables here and we check for it here because they're also automated they're constants up here so that's going to automatically create that and then we're going to do is we're going to copy it over then what we're going to run our macro called create thumbnail and i'll go over that macro in just a little bit then what i want to do is going to browse for the revision this macro is the macro that we're going to run this is exactly the same everything's the same the only difference is we're going to basically put it in this cell h9 and we're not going to generate a thumbnail off the revision it will be only on the original file name so all we do is just spin the only difference again h9 is where that file name is going to be located and we're going to copy the file just as we did here alright so what about if we want to open a document open the original open the revised document so all i need to do if i want to open that debbie's time off i want to open it here it's going to open that or maybe if i want to open our we already did that the word document we can do that here so that requires us a very simple macro so regardless open the original first of all i want to make sure that h5 is not empty if for some reason h5 is empty like it is here on any time we open it i don't want anything to happen so when you click open nothing's going to happen so if h5 is empty or h9 in the revision exit the sub i'm going to check for folders to make sure that they're accurate around that macro we're going to determine the file path it's going to be the admin d3 right we could also simply use because we've already run check folders we can also use document folder here document doc folder would be just fine right and the h5 let's do that doc folder doc folder is going to end h5 so the combination is going to open the original so when i want to open original i just click open and it's going to automatically open that original document we're going to do the same thing for this then the last line of code to open it this workbook follow hyperlink file path we've got that full file path based on here document folder in the workflow this is the full file path that's all we need along with the follow hyperlinks gonna do it gonna get that for us full file path now we'll open it open whatever the document is then based on the revision we're gonna do exactly the same thing exactly the same thing except we're originating our file name from h9 everything else is the same okay cool so now what we're going to do is we're going to create thumbnails let me show that to you once more before i say it so basically if i want to create a thumbnail i'm going to browse for a specific file let's say time off tammy right and what i want to do is click open and that's automatically going to create that for this one invoice seven notice when i save and update that invoice seven request now it's going to automatically be updated see how that did see see how it looks different near that thumbnail when we open that we see it's the basically the time off here and we created a thumbnail for that we're going to use that thumbnail inside that shape so all we need to do is a few lines of code and i'll show you how to do that first thing we're going to do of course is we're going to check those folders i want to make sure that the folders are accurate okay so the next thing we're going to focus on our workflows sheet so with our workflow c we're going to do a little bit and that's it so let's bring this out a little bit so first thing i want to basically stop the updating of the screen we can do application screen updating equals false that's going to make things a lot faster and they won't flash as much we're going to set that document folder based on the d3 value in the backslash it should already be done here just in case the thumbnail folder same thing d4 we're going to set that shared i want to make sure that those are set correct the file name what is that file name is going to be based on h5 value remember this is the one we're creating we need to create that full file path if it's a pdf basically our folder here here combined with our path is combined with our file name it's going to create that full file path we're going to put that in a variable called file name i also want to know what the file type is i want to extract that this is a string variable and i won't extract that whether it's pdf dot word or whatever it is dot jpeg i want to extract it so the best way to do that is we want to find the last period here's the last period right because that file name could contain periods so we want to use in string reverse meaning it's going to start at the end and work backwards in stirring reverse is a great command when we want to start at the end the backwards when we want to move from right to left when we want to move when we want to find a string and we want to move from left to right we're going to use in string but we want to use go the reverse in string reverse so what i'm going to do is i'm going to if i want to locate inside that file name where is that period located so once it's been found it's going to give me the specific character number that's been found at so let's say we have a 20 character text string and it's been found in the 17th position so that's going to say it so then what do we do is we're going to take the entire length let's say 20 and we're going to subtract out 17 that's going to leave us with three right then what i want to do is i want to find the right the right three characters that's going to leave me with the right three characters of this and it's going to put that into a string called file type that's what i want i only want that if it's a four digit extension it's also going to find that because this is based on that period so whether it's three or four it's going to be fine then what i want to do is i want to determine the file path the entire file path put that in there and that's going to basically be in the document folder along with the i guess i could put file name on this because i have that already inside that file name and that's going to be the document file path once we have that then what i want to do is i want to do the same thing for the thumbnail the thumbnail is going to be basically also want that so i'm going to replace that and so what i want to do is i want to create a very unique name for the thumbnail to do that what i want to do is i want to make sure that we are going to replace it the file name in this case we're gonna use the file name i'm gonna look for the period and then first of all i'm gonna replace it with nothing i don't wanna include that period right i wanna exclude it so once i remove that from the file name i'm gonna add in underscore thumbnail.jpg that thumbnail's going to be jpg so we're adding on including the file extension for that so we have our thumbnail name so we've got our thumbnail name it's basically going to take whatever the current file name is removing the period and adding underscore thumbnail so we can do that giving it a unique name then we need to build the entire path so the path is the thumbnail folder plus the thumbnail name that's going to give us the full file path of the thumbnail based on our thumbnail folder okay then what i want to do is i want to determine based on the file type right there's a few different things if it's a pdf we need to do something if it's a picture we need to do something so if it's a picture we need to do something if it's a pdf or word we can do something else so what do we do if it's a png jpg jpg or bmp or tiff then we know it's a picture then picture file then otherwise it's going to be probably a word or pdf l word or pdf or pdf probably if i would work with excel too okay so what we want to do now if it's a picture what i'm going to do is insert that picture dot pictures were already inside the sheet dot pictures dot insert the file path so basically taking that entire path of that picture and inserting it into our worksheet what we're going to do right away is we're going to assign a specific name for that picture we're going to call it document thumb else what if it's a word or pdf then i want to create an object called ole objects we're going to add a brand new object to our worksheet we're going to give it the name we're going to add it we're going to use it basing it on the file path that's our file name right that's how we know it needs to open that document and create that object so it's got to know the full file path here it is we don't necessarily need to link to that and we don't want to display it as an icon we want to display it as the full object whether the full word or fold so basically it opens up a pdf or it opens up a word document directly inside excel so that so once we do that and then what we're going to do is we're going to give that object a specific name that object is going to be the same name whether it is for a picture or whether it's for that pdf or word notice it's the same name that way we can work with it regardless moving forward regardless if it is a picture pdf or word we can work with it exactly the same that's all we need to do to differentiate just these three lines of code then what we want to do is we want to set an object chart regardless if it is a picture or a word we're going to create an object chart a chart we're going to set that called object chart that's the one we've defined up here all the way up here as object chart as an object then what we're going to do is we're going to create that object here it's going to be basically be chart objects 200 by this is just the position the left position the middle the right top position i believe just the position the position doesn't matter too much because we're going to delete it in a moment what do we want to insert right i want to give it a specific width what is the width of that i want to give it the exact same width as whatever our shape is whatever it's a word document so we're going to give it a specific width we're also going to give it a specific height that height of that chart is going to be exactly the same as that picture or pdf or whatever it is so we're creating that object that's exactly the same size then what we're going to do is we're going to take that shape whether it is again a word or pdf we're going to copy that we're going to paste it directly into the chart the reason we do this is excel does not allow us to save a picture the only way it does is if we put it into a chart once it's in a chart we can then save it as a picture so that's what we're going to do so once we copy the picture we're going to copy the shape we're going to activate that chart then we're going to paste that particular picture inside the chart remember they're both the same size now our picture and our chart are exactly the same so when we paste it in there it's going to be perfect right then all we need to do once we have that then all we need to do is export that chart into a file but i want to make sure that if we export that chart into file that that file name doesn't already exist so we need to check if it exists we need to delete it so we can do that with this line of code if the thumbnail path vb directory does not equal empty then we need to kill it right once we've deleted it if it exists then what we can do is run that export so all we're going to do is we're going to take that active chart we've activated it here we're going to export it based on the file name that thumbnail file path and we're going to export it as a jpeg that's all we have to do then that picture is now created and it is now in our photo so it looks something like this once it gets created it looks pretty much like uh this right here thumbnails here a bunch of pictures is all we have and you could use even shirts or pictures or whatever my test so creating those little thumbnails are here so we've created those now what we can do is delete our use for that chart is over we can now delete the chart we can also delete the picture that picture that we created here or here we can also delete that too we don't need any of those our purpose is only simply to save it as a file and then also we're going to set that thumbnail file name in b5 i want b5 to make sure that we save that thumbnail and that's going to locate it right here in b5 so that name notice we notice this time off tammy notice we have the file name then we have that underscore then we have the thumb dot jpg so that's exactly the name we've given it then all we need to do is which in the macro that i'll go over soon once we load it up all we need to do is embed that particular picture inside a shape we've got a sample shape right here all we need to do is do that so it's relatively easy good so now we've been over all the macros that we have on our document file macros that's all we have to do for that so that's going to allow us to browse for files it's going to allow us to open files and it's going to allow this to create thumbnails based on almost any document type okay fantastic now i want to go over some of the macros that can allow us to save a update new document or maybe even delete a document okay so how are we going to do that so if we want to delete a document we can but for now we're just going to say no so those are some relatively macros and basically all i want to do is save this information into this table and also i want to save whatever review whatever current the review is i want to save that information also to this and the only thing is we need to make sure in this one is it an existing or is it a new review right so we need to make sure that's going to be based on the database like if i select a specific one we know it's got a specific database here there so if we need to make a change like test we can make that change test change here and we can make that update just by saving okay so we need to know a few things let's get into it now we're going to use data mapping and basically what i want to do is i want to map all these cells including the document id including the thumbnail and a few others i want to map them to the database and i've done just that here something we've been over before basically b2 is that document id f3 of that page here located right here is right here f3 and our originator in h3 so basically we're going to each column we've mapped it and what that allows us to do is run a loop from 2 all the way to the last one whatever's in f3 put in our row whatever's in j3 put in a row what is that row well that row could be based on whether it is a new document or an existing document example if it's a new document this one's going to be empty right if i've selected an existing one we already have a row but if i click new document we won't have any row in b3 so inside that macro i need to determine does b3 contain a value or not so we're going to do that all right so let's get into some of these document macros and that's here so we have save and update which is we're going to get into and we also have load review and we also have new so the first thing we'll go in order will be easier and of course we're going to start out with some variables we have the document row as a long the document column we have the last column and i'll go over these last results row do we have the review database rows long and the count delay as long we'll go over those and the document id is a string that could be a long variable string and the supervisor string okay first thing basically i want to do is i just want to with the workflows i want to create clear out for our new all of the fields that are associated with that so basically in all the tables right so we want to when we select when i click add new document no i want to make sure we clear out everything here here and here and also including the database row that database row is going to be stored here so if i've got a specific change i know if i'm going to load that i need to know what the database rows this on i know it's 46 remember that's the database row that's located right here 46. if i need to save it i need to know what row what database row is located if we make a change like that test change i need to know what row to place that so that row is going to come directly from r and it's going to place directly inside b14 okay so we need to know that so we need to clear out all that information i also want to know if the active sheet name code just in case we're running this macro from another sheet f3 select that's going to allow automatically to when we click new documents going to select f3 which is our first one giving it a name the reason we ask if it's like if i were to select this and i try to run this macro it's not going to create an error but if it did for example let's say i tried to run it like this sometimes you run a list and you get an error and you don't know why it's like why did i just get that error it worked before right watch end now when i select this sheet workflows go back in here and i run this it's going to run without an issue why is that why can i why did it have a bug one time and not the other and that is because we cannot select a cell in a sheet if that sheet is not active if i activate another sheet and i try to run that macro right it's going to automatically create a bug so we always want to the best way when every time you use select it's really best to automatically check to make sure the sheet is the same if the active sheet codename equals workflows only then select otherwise we can ignore it that avoids the bug so now if i'm on another sheet and i decide to run this macro it's simply not going to select f3 but it won't create a bug okay so that's good to know when you use select because i've seen a lot of the questions in our group about you know having issues when we come to select so that's usually the case when there's an error on select is because you're not on the actor sheet that you're trying to select okay so documenting is relatively simple not much going on document load when we're loading a brand new document all we're going to do is clear all the cells and we want to make sure that b3 is not empty when i select a specific document what we're going to do is we're going to place that document id located here in b2 once we place it there it's going to generate a row if there's no row associated with that we know that we cannot load anything we've got to have that document database row that should be let's call this database row make it a little more clear database so we have to have that document database row so if that's empty then we can't go forward so the first thing we want to do is if b3 equals empty then please make sure to select document from the document or float nothing we can do so we can exit out of sub we're going to assign that document row to a database and then we're gonna use data mapping basically from two to eleven why are we not starting at one one would be our first b2 right we already have that we already have that in there and i'll show you how we get that in there in a moment but so we can start out document ids already there so we can start out column two basically all we're gonna do is we have the row inside a variable i'm gonna look whatever is in column two and i'll place it in f3 we're gonna look in column three and we're gonna place it directly in h3 so we go through these so that way we can use all we can load all this data with data mapping into just a small form very very easily i have a video directly only on data mapping if you want to spend a little more time on that i've got that called data mapping you can search for that on my channel okay so we have that so we've loaded all the data in so that's relatively small now what i don't want to do is i don't want to load in anything that's been a formula if we take a look at our office staff here right notice that we have here in b8 or let me just double check column 10 on that one make sure i got the right one here notice here in column 10 which is the reviewer position b12 b12 b12 i want to make sure this was generated with a formula with a formula so let's take a look at b12 notice the office manager was generated through a formula so that means i want to know what the position of this thing is and i want to when i save this i want to take this office manager and i want to place it directly inside here but when i load it when i load it when i want to bring the information back i don't need to right because all i need to do is bring tina back and it's going to automatically update here if i do put that office manager in b12 it's going to clear out this formula i don't want that to happen so what we don't want to do and that's going to be column 10. notice equals column this is column 10. so if i change the the back to a general so so basically for column 10 we don't want to load it so we could put in a caveat here if the document column does not equal 10 then bring the information in and to do that all we need to do is say the range this is for the workflows based on row one this is the range in row one this right here row one the document column so what is that range the range is h5 b5 f7 f9 so that's what the range is and that's going to be equal to whatever is in the document database the document row and the cell value that's it that's all we need to do to load it that's going to load the particular document okay so when we select that it's going to load that document so also we want to load the reviews now we're going to run this now both of these macros will run when we select we'll go over that just keep that in mind when we select something we'll go over that macro zoom we're going to go in order so that's organized when i make a selection two macros are going to run when i select this the load document which is going to load this information and i also want to load these reviews also in so that macro is coming up next that's called document load reviews i want to load all those reviews so the first thing what i want to do is i want to clear the contents of any reviews that might be here any reviews are here so l4 through n12 want to clear that out i also want to clear out the associated database row here when we bring the information in from the reviews right basically we're going to run an advanced filter i'm going to bring in all these reviews usually you won't have that many reviews right and then i want to bring it in i want to bring this information in here and i want to bring the rows in here so we're going to bring that in here and also i've been doing a lot of testing so i've got a lot of reviews there so to do that we're going to clear out the information first then we're going to focus on the review database we're going to determine the last row and then what we're going to do is if last row is less than three we're going to exit this up we're going to run an advanced filter if we lower this a little bit we take a look not that much see my purple desktop okay so what we want to do is we want to see this particular code here and i'll go to the document review database so basically our advanced filter we're going to go all the way from a2 through g our criteria is going to be the 11 right how do we know our criteria well if we link this to our document id we only want to load those reviews based on a specific document that's linked to workflows b2 we know that that's our document id the result is going to be only those reviews based on that particular document id so we can automatically run it inside our dingo so let's take a look inside here back here okay here we are so a2 through g in the last row we're going to run an advanced filter we're going to copy the information i want to set those criterias based on l2 through l3 and i want those results to come into n2 through q2 so all those results come in now i want to determine the last result row in case here but in this case it may not be so important we could bring it also actually i don't need it here this time why is that well i don't need that this time habit this case we only have a maximum number of rows right so in this case i'm just going to bring in everything normally you won't have that many so i'm just going to bring in everything so i'm going to say l4 through and 12 we have a fixed number we don't have we don't because of this design we don't have it if we were to move this over we could have unlimited so but i wanted to save these because these are all dynamic as we add more so i kept it that way so basically i only have a limited number of routes that we want to show it's enough so l4 through n12 equals basically we're going to just set the number of rows so we want 10 rows also from here so we're just going to set the first 10 rows here it's going to be all the way to here so all the way through in this case n3 through p12 so we do that just in my code l4 through n12 equals n3 through p11 then we also want to do that with the database 2 right the workflows r4 through r12 is going to equal q3 through q11 so i also want to bring over these database rows at least the 10 rows and bring them directly over inside r right here it's going to bring them all over okay because we have a fixed number of cells so we don't need the last row in this case it doesn't matter it doesn't matter what the last row is in our results because we're only going to bring in 10 which should be sufficient based on this design okay so we've got the reviews there so now what are we going to do i want to save and update this how are we going to save and update well again i want to make sure that we have first of all all of our required fields remember we went over that so b1 has to be five right if we anything less we need to let the user know if b1 does not equal five then we just let them know please make sure to fill in all the required fields we're going to exit the sub out of it now i need to determine are we is this a new document or is this an existing as we mentioned before b3 is going to tell us that if that row is empty based on an error then we know that it is a new document there's a few things we need to do if it's a new document if it's a new document i need to sign the next id we're going to use the max formula we've got the formula here max of all the document ids plus 1. if there's an error i want to return 1. why would there be an error well there'd be an error if there was no data so i want to basically determine the max of these in this case the next one would be 19. there's no data at all i'm just going to return 1. there would be an error so if there's an error it will be 1. that's going to get us the max of them and it's going to assign it so if it's a new document we know to assign a brand new document id and are based on our first available row in this case 22. okay so we would do that if b3 is empty we know it's a new document the document row is going to be based on the first available row inside the document database plus one which is the last row with the value plus one our first available row i also want to get the next document id and i want to place that next document id directly inside b2 it's going to come directly from b4 it's going to be placed in b2 i also want to take this next document id and i'm going to place it in column a right here okay so we do that with just a few lines of code here b2 is going to take on b4 also the first column here column a and our document row is going to take on b4 next document id also i want to make sure that it f9 is empty that i want to sign in just in case there they have not added a reviewer what i'd like to do is add the current originator to the current reviewer before basically before they've decided to assign it to another reviewer it's assigned to themselves and then if they decide they can then drag it on over or move it to the next one but first it's currently assigned to them when you create a document you haven't given it to your manager yet it's still in your possession so you want to make sure that you assign it so the current reviewer is the originator if they haven't set so if f9 is empty then what we're going to do is we're simply going to take h9 and we're going to place it directly in f9 so we do that with this if f9 equals empty let's just put in no reviewer manager set then we're going to do is we're going to set the default reviewer to the current staff okay f9 that's it just to make sure that we set that up okay if it's an existing document we're going to do something else all we need to do is extract the row from whatever is in b3 that's it that's it whether it's new or existing now the rest we do automatically for both so again we're going to use data mapping the same thing we did but this time it's in reverse whatever is located inside our range just here is going to be placed inside our database based on the document row and document column again we're looping through from 2 to 11 here from 2 because our document id is already here regardless if it's new or existing it's already here from 2 all the way to the last row and including where the formula remember we don't need to account for 10 because we're placing it here it doesn't matter if it's a formula we want the results from 2 all the way to 11 taking whatever's in f3 and placing it here whatever's in h3 and placing it here or whatever row we've set so that's going to do with just three lines of code next up what i want to do is i want to save the document review only if the review exists right if f9 is not empty which it shouldn't be but if it is i want to want to save if f9 is empty i want to save the review so we have to also save that inside our document review it's got to be saved maybe we've added a review here it's got to be saved at the first available or if it's already existing save in whatever row how do we know if that review is existing or not well we can look directly to our database row we know if b14 right if we if i click new document we know b14 is going to be empty right but if we click an existing one it's also going to be empty but if we click an existing review we know it's going to contain that database row so b 14 is going to let us know if it's existing or not so that's going to be inside the code if b14 value equals empty then let's just put new review row okay existing so new review just put a new review so if it's new review i want to get the first available row based on our review database and we're going to determine that between last row of course with a value plus one and then what i want to do is just same thing i want to place the set the database row b14 is going to take on that row remember we've just created it so b14 is going to take on that first available one and then also i want to do is i want to set the id that id must be set inside here i want to know that particular document id we have to place that directly in a for those new ones so a is going to take on whatever's in b2 setting that document id i also want to know the row i'm going to put that in so the row both the row document id and the row those are only for new we only need to set that once and that's going to be for new reviews so it's going to be set in a and in g we're going to set that row we're setting a formula and that way if we delete a row the row will maintain correctness okay so we have that there and now what if it's an existing row then all we need to do is extract the row into a variable based on what is in b14 that's it that's for both new existing so the rest is simply updating or adding to the database we're going to adding to the database we're going to set the current review time and date in column b we're going to set the staff name in c coming from f9 the revision if there's a file name in h9 coming from h9 f10 is going to take on the nodes in f12 so basically all i'm going to do is just take all the information from the reviewer the revision look the notes and the status and just placing it directly in the account the date the reviewer the revision file the note status and all and all sorts of that so that's all we need to do there to handle that okay great so that pretty much is going to save it that's all we need to do the last thing once we save it i want to run the macro that's going to refresh the reviews right because if we've added a new review i want to refresh this list notice there's a there's let's say one here this one doesn't have a full list of review so if i add a review here i want to make sure that we add that automatically here so if we test that here and then we save it i want to make sure that that review also gets updated here okay notice we've just added that here so we want to make sure that we update the list of reviews here so i'm going to run the macro that loads the reviews that's the macro we just went up here load the review so all i want to do is refresh that adding any changes that we made in here and i also run the macro called workflow refresh that's going to refresh it that way any changes that we've made i wanted to make sure that it appears that macro is going to refresh this list we're going to be going over that soon as well last thing is document delete that's relatively easy b3 we need to make sure that there's a row associated with that we won't let the user know do they want to delete it yes or no if they do we're going to assign that the row the database row documentally we're going to set the document id and then we're just going to delete it from the database and then we're going to refresh the workflow and then we're going to run a macro to new so basically we're just deleting the row associated here simply just taking whatever row deleting now again if you want to add more i would suggest you delete all the reviews associated this by running an advanced filter we just didn't get that because we got a lot running that advanced filter sorting it based on rows right it's a lot it's a lot of work to do just for delete sorting but i'll tell you the right way to do it sorting it based on the highest row first the lowest row and then pulling the row into a variable deleting the row deleting the row row and row so basically going through and deleting those rows associated so that's how you would do it if you want to deliver it for our purposes it's not necessary today but that's what you might want if you're selling it you certainly want to delete the associated reviews with each document okay moving on so now what i want to do is i want to load a review that's a simple macro when i select that's a simple macro when i select on a review i want to load that up right if i know that we have a database row associated here all i need to do is just load it so when i make a selection change that's when i want the macro to run when i make a selection change based on anywhere from l4 all the way through n12 that's what i wanted to run so let's take a look at that the originates back inside our workflows work fixed and based on selection change when i make a selection change from l4 through n21 i want to make sure that l contains a value right if i select on something that doesn't nothing would happen right only when we select value in that case i want to run a macro that's going to load the review details inside here okay so we want to load everything up so what we want to do is b17 is going to take on the target row i want to put that that we need for conditional formatting b17 remember that's our root selected review row as we select it notice b17 changes i also want to take that database i'm going to put it in directly in b14 so that's the next step b14 is going to take on whatever's in r and the target bring over the database row once we have that we're going to run the macro called review load that is the macro that we're going to go into right now that's here so with the workflows we want to make sure that b14 contains a value if it's empty we're going to exit the sub we've got to have that database row right we're going to assign that to a variable called b14 right that's the variable here b14 once we have that all we need to do is just go into the review database whatever is in c which is our staff name whatever is in d which is our revision file right basically we're just going to go whatever is in c or view name or revision file or notes or status and bring it directly over into the individual cells we could have used data mapping for this too f9 or h9 or in this case f10 or of course f12 is going to take on all that information just with these lines of code that's it that's review the load okay great everything works fine perfect i want to assign a macro when i click on a button i want to assign a macro the best way to do that is we're going to duplicate these notice that when i select something all the shapes get deleted but there's a macro that i want to send that macro that i'm assigning to these shapes is basically called macro select it's we're going to use a sample here when i create this we're using this sample if i assign this macro before we look into the individual shapes if i assign a macro to these when i duplicate sorry it's off the screen called assign macro it is this macro document select that i've decided to assign to our sample shape our sample shape is simply a group right this basically it's just this item here and this item here this shape here so text box here in the shape here this one folded corner if i want to update that i want to change it i can change it notice it's going to automatically change in the ones we created so you see now the fold so basically our sample is going to be the original docker so all i need to do is assign a macro to this shape and this shape or just the group as a whole and it is that macro called document select that's the macro so when i select something right it's the macro that i want to assign so how are we going to do that well if we take a look at this name here if i take a look at this name let's go ahead and update this we take we right click on here and we can see it's called document group 12. the name that i've given it basically i'm going to say document group and then this 12 is actually our document id so when i select here notice 12 comes up here and this one here there's a macro running that's why you can't see it believe it or not and this one here you that's basically if i let's select on that it's 13 right document id 13 okay so we can see that once the macro finishes running and i'll explain why that macro is running basically the macro is waiting for us to drag and drop it so once it finishes we'll be able to see that id up here here document 13 group you can see it here 13. so what i want to do is i want to extract this 13 from our document name i want to extract it or what i want to do is from the individual shapes this one's called document thm show of a thumbnail thm and this one's called document nam for the document name now the point is what we want to do is we want to make sure that each of these shapes there's basically two shapes in this group i want them to make sure that they have the same number of characters document and am and also document thm same number of characters because what i really want is i want that number and i want to extract it if i take out the first number of characters if they're both the same it's going to leave me with the document id and that's just what we do inside that and i want to place that id directly in b2 so how do we extract that from the name remember we don't know if they've selected on the thumbnail we don't know if they've selected on the text so the best way to do that is simply to remove the same number of characters so we can do that with just a little line of code here called replace we're replacing the application collar application collar is the name of the shape that called the macro we don't know if it's a thumbnail we don't know if it's a text box so what we're going to do is we've given the same number of characters so i'm going to take those 11 characters which is all the text and i'm going to remove it based on that so we're going to use the left of 11 colors i'm going to take all the first left 11 characters i'm going to replace those with nothing what's that going to leave us with it's going to leave us with that document id it's going to call this extract document id from the shape name and that way whether it is a single character id such as one or two or double like 11 or 12 or even if it's 115 it's going to automatically extract because we're taking away everything else okay so i'm going to take that i'm putting that into a variable then what i want to do is i want to set the move document to false b18 that will come in handy a little bit later basically you're going to take b18 to false okay that's going to come in handy so then what we want to do in b19 i want to set that left position and i want to set the top position what i want to know is when i select something i want to know the left position of that shape and i want to know the top position why is that important because if i decide i'm going to drag and drop that excel needs to know whether it's been moved right and i need to know that means if the left position has changed or if the top position has changed we need to know that so that we can tell excel as we're running the loop hey there's been a change we should make the update to the team leader or you know or whoever is we should update that so that's what we do inside this but the first thing what we want to do is set the current position so i'm going to set the position the set initial left position based on the document group remember this is the group as a whole right we have individual names the group as a whole is named called document group and then whatever the id is so if we've extracted that id and then i add document group we've got the group name the group is where i want the left position i want to set that left position and b19 again it is that group position where i want to set that top position located in b20 once i know the left position that i can compare it so basically when we move this it's going to auto it's going to run a loop and i'll go over that macro very very soon it's going to run a loop hey has that top position changed has that left position changed if it has get the up get the new person's right or even you know if there's no name we need to hey there's nothing there don't move it all the way over there you got to actually assign a macro so we need to know the difference so setting up the initial left position and then top position is going to help us determine if it's been moved now what we're going to do is we're just going to simply load the macro that's going to run the reviews we've been that and we're going to load the document load those macros are open so when i select it all i need to do is take that document make sure that document id is located in b2 once it is there i can then run the macro to load it and also run the macro to load the id so it's going to load here load that list of ids here so whether it's a sales order or whatever it is it's going to load those reviews one macro to load the reviews one macro to load that so all we need that required is b2 it's going to take on that and everything else will happen then what i'm going to do is i'm going to select the document grid basically what i want to do is when i click here i want that shape to be selected that gives the user the ability to drag and drop it very easily once we select it so very easily to do that all we need to do is actually to select the entire group we can do that with this shapes document group document id select selected item group then when i'm going to run a macro i'm going to run a macro it's called workflows check for move that's the next one coming up and basically it's just going to run a loop and it's going to continually check and check and check for a number of seconds to see has the user moved it or not if it has moved it then make the update this is going to be super easy so let's take a look inside here inside the vba it's going to be called workflows check for move and basically what this macro is we're just going to check to see if the user has made a change to the movement you know change the position of the group if they have we're going to check is it an accurate move right if it's not right if they've moved it somewhere crazy we need to let them know hey you can't move it there and then just refresh it but if they've moved it to a correct position then we just need to let the user know okay good and then move it all i need to do is then pretty much update the supervisor and move it into its new position simply by updating the database and then go ahead and refreshing it so we can do that just a little bit of code here first of all i want to make sure that b2 contains we've got to have an actual of course document id that's located in b2 if it's empty we can exit the sub document 80 is going to put that into a variable b2 then i'm going to run a count from 1 all the way to 100 000 and that's going to put us a delay right we need to give the user some time to make the changes right if they've selected on something give them some time if it's too quick they won't have enough time they might want to move it you know a little while after so we want to do that giving them some time to move it so we're going to start that now it's during a macro so we need to allow them to make some changes during while the macro is running so do advance is going to allow us to do that allows the user to make some changes while the macro is running okay i also want to check is has changes been made so is if it should continue running as long as b18 is false b 18 is false continue running and checking and checking and checking as soon as they make a change we're going to set b 18 to true that's going to allow excel to say okay we know that an update has been made we should make those changes so b18 is going to let us know whether a change has been made so that loop will continue running as long as b18 is false so b18 if it's true we're just gonna end it's gonna end the air once the document has been moved we're gonna do that okay so how do we first of all now we need to check to see if the document has been moved so we're going to focus on that we've got the document id we know the name of the group is document group with document id and then what we're going to do is we're going to check if the left position is different than b19 or the top position is different than b20 again that's what would come in if the left position is different than b19 or the top position is different than b20 then we know the user has made a change the next thing we do we need to check is it an appropriate change right is it a correct change if it's too far to left we need to let the user know please make sure to move the document to a workflow containing a supervisor name okay so it's too far over there so and then of course refreshing the screen so we can do that with this if right left is less than f1 f they'll call the in this case of course the row doesn't matter i want to know the left position of f right if it's if here's f if it's before that we need to let the user notice it's before that it's less than the left position of f let the user know or maybe if the top position is less than 15. in that case move it over right so let them know so here if the left position of the group is the less than the left position of column f then or in this case this case all i need to know is work order 14 right this case we're going to check for a proper supervisor the supervisor is located on row 14. so i need to check the column of the shape the column the top left cell of that shape what is the column of that if that if that column in that row equals empty what does that mean here's here's 14. here's our supervisors right let's let's do time off that's an easier one right if there's no supervisor here in case this one we need to let the user know hey don't move it there we need to have a supervisor notice the column right if i'm moving over here this column is all the way over here that's not going to work we need to make sure that the column they've moved it over contains a supervisor right we have to we're going to use the top left cell for that so this is our check top left cell column plus row 14 is it empty if it is that means no supervisor so that or another one or maybe the top position is less than f15 right remember f the f doesn't matter in this case if the left position is the of row 15 if it's below that if it's less than that then we know it's also not good as we mentioned before less than that would not work as well so we need to check on those three conditions if any one of those three are accurate then we need to let the user know please make sure to move the document to a workflow containing a supervisor name and then refreshing it right we want it we want to completely run the macro this macro will be next we're going to run that macro that's going to basically refresh all those back to the way it was so quickly if they moved the wrong one just click ok and it's going to return right back to the way it was okay great but what if it then we're exiting the sub but what if it was a correct move if it is a correct move then what i want to do is i want to determine the supervisor located here and i'll go over that form in a moment the supervisor located in whatever column they've moved it over to and row 14. so that supervisor is going to be in a string variable which is the workflows row 14 and the top left cell the column of that shape the column of that shape that is the supervisor name supervisor name and all i need to do is take that name supervisor name and place it directly located in here f9 so that's what we're going to do next f9 it's going to take on the supervisor name i also want to set the status in f12 to pending set the status giving it a status so when i move it right i want to set that status depending it's going to set that status which is pending review remember pending here here this is the named range pending remember i told you we're going to create those name right it's going to make it a lot easier it's called pending review so as we change this it's automatically going to change so that's going to give it that pending review so we've done that then all we need to do again set move to true right this this lets him know that a change has been made setting that to true what is that going to do why is that important because as soon as it goes to true it's going to exit out of this loop because as soon as b18 goes through it's going to end that means it won't keep looping and looping and looping as soon as it's going to end out so we set that to true then it's going to exit out of that loop right it's going to go down here we're going to make sure that b18 is true and then all we're going to do is just save that right before we do we're going to save those changes so automatically when we save those changes that new position is going to be automatically set that's the macro of course we're going to go over next and that's the macro that automatically refreshes those based on either selection or load so cool so we understand how to move it and how to save it because as soon as we save it right as soon as we add that new review as soon as we add that new sub it's going to automatically change very easily so that won't work because that's not far enough but if we move it a little bit farther over it will work just fine great so we've gone over all the macros but what i want to do is i want to share with you how do we create this really cool so that all these shapes go in here that's going to be based on our module called workflow sheet macros so it's called workflow refresh that's the one i'd like to go over with you now so the first thing we want to do again i want to check to make sure that we have a thumbnail folder in this case though we can still if we don't have a thumbnail folder i would like to display that even if i don't have a thumbnail folder let's say we make that folder thumbnail folder incorrect let's say we just add something onto it so that makes it incorrect right i'd still like to display them but we won't have to display thumbnails please select a folder in the admin screen they're still going to display but without let the user know hey put in the thumbnails but it's still good to have them even if the thumbnail folder file path is incorrect it's still going to have the shapes and still going to work just won't have those thumbnails okay but when we do so notice that they're there they just don't have the thumbnails as soon as we make sure that that path is accurate they're going to show up so we want to give the user a warning but we don't want to exit out of this up if the thumbnail folder director equals empty or the thumbnail footage to display document thumbnails please set a thumbnail folder nav screen but remember we're not exiting this up no need to exit this up documents can be displayed without the thumbnails it's fine right but you know sometimes you won't have a thumbnail for every one so we don't need to exit out okay so first of all i want to check for the selected flow row what is that that's going to be located in b15 remember i need to know what is located in that flow room b15 is going to be that select row what row is it work order is a sales order b 15 is going to tell us that so we have that and if that's empty please select a flow type we need to know that flow type very important because i need to know what type workflows to type what are we going to look at for time off are they going to be expenses less than a thousand are they going to be expenses greater than a thousand i need to know which ones we have so we need to make sure that that contains a value why is that important because when i basically use our advanced filter i only want workflows based on the sales order i want to run an advanced filter and it's going to be based only on a specific workflow type so we need to know that inside there great so we're going to if there's nothing in b15 we can exit out of the sub right that's very very important continuing on okay we're going to set the selected road basically as a variable inside whatever is b15 that's our selected flow type row i also want to clear the existing documents right if i'm refreshing it i want to make sure we delete all of these now notice they all have the word documented document our sample doesn't our sample is called sample doc group doc group it doesn't have the word document in it not the full word so what i want to do and i want to make sure that no other shapes have the word document i want to delete every single shape that contains the word document to do that we can just run a little bit of a loop in this case it's going to call for each document shape this has been defined as a shape all the way over here document shape as a shape so for each doc shape in shapes right we're already in the shapes if in string the name of that contains the word document is greater than zero meaning it does then delete the shape so for every single shape on our worksheet that contains the word document deleted okay we're going to be recreating them so there's no need to keep them then what we want to do again i want to set b18 set move true to avoid loop i want to make sure that we're not going to create any loop so i'm going to set b18 to true just in case why would that happen because if i click here and i refresh it real quick it could be a you know an issue if i decide to save it very quick i don't want to create a loop right so that way it's going to exit out of any loop that might be coming up so setting true exits out of to avoid any loop okay then what i want to do is i want to clear the existing positions and document counts so b2 f15 f101 through p101 what does that mean i also wanted to keep track of how many documents are here right if i move this document here i can't have them appear on top of each other i need to know that there's two documents in this column or three documents in this column so that they don't appear on top of each other let me move that over so we can see or three documents so we need to keep track of how many documents are in a single column well i'm going to put that all the way down here in row 101. one notice it's 3. i need to keep track of it vba is going to do that for us but i need to keep track of how many documents are in a column so we can know that so we're going to put that inside b101 when i refresh the schedule i'm going to clear out f all the way through let's say p or something like that f101 through p101 clearing that out we don't once we clear it out vba is going to keep track of it so f101 through p10 gets cleared out b2 the document id also f15 through p15 f15 through p15 let's take a look at that we also want to make sure that we are clearing out everything through here all the everything through here f15 all the way because these these types are also based these are based on whatever we've selected in the time off or whatever the so they're based on that so they're coming from here right the sales over here so if i change to time off i only want to load these four so we need to clear out everything that's located in f15 and throughout right so we're going to update them automatically but we want to clear out the existing ones once we select them then of course our supervisors will appear here so we're going to clear those out we're clearing all the existing positions and document counts here then what we want to do is b16 is empty what is in b16 let's take a look b16 contains our type row right we need to make sure we've got a type row here right here what is that notice that remember that is our type row here types are located here time off one two three we're going to use how do we get to that of course we're going to use just that simple indirect we're matching on the workflow types running we're going to use indirect d and i want to know what row it's in we could use a match for that that will work too i want to know what row it's on so we have that we're going to the b type if i want to get a column so we're going to start out with b16 right one two three or four but what i really want is not just the one two three four i want to know what column it is for example time off is placed in 6 right because i want to get all of the staff positions here i want to determine the last row and i want to loop through them all to determine that so i can place them directly inside here and so if i click time i want to place them directly in here well to do that i need that column so the first thing we want to do if we know one if we know the first one is one and we know the columns in six all we need to do is add five so adding five is just what i did here d here our company workflow column company workflow column is located in b16 plus five that's our admin workflow admin company let's call this company because we set up those company workflows and then once we have the column i want to determine the last row based on that right the last row is going to be the last possible row in this case it's going to be 18 18 i say in this case a lot 18 all the way down right so get that last row in this case again there's that word 11 right i'll think of something other than in this case in this instance i like that better in this instance 11 is our last row so we want to run a loop from 8 to 11 taking all those and placing them directly in our row here starting on f and then moving through okay so we can do that here so if the last was less than 8 then of course please make sure to add a company there's got to be at least more than 180 then what we're going to do is we're going to turn off application screen updating okay we don't need this one here that's not important got a better way around that so we're going to run this loop for the company row is going to be a to last so basically i'm just running a loop from the admin starting with 8 and going to the last row for each one we're just going to basically update the rows here starting of course in column 6 and row 15. so case cells right from our 15 right that's a row company flow row 2 minus 2. well what is that we know it's starting we know it's going to start in row 8 right we're looping we're starting on row 8 but this row 8 must be placed in the column column 6 here so if i know that row 8 must be placed in column 6 all i need to do is subtract 2 and that's just what i've done here the flow minus 2 that will give us 6. so row 15 column 6 value equals the admin cells the company flow row company flow column value so that's going to basically set run that loop it's going to be placed direct every single one one two three four all the way from here regardless of whatever's going to set so here we go in this case it's a lot here and so we have a lot of them basically on this so that's going to regardless of the column okay so that's just simply going to load those company workflows basically on that so all we need to do is that so once we have those loaded we can then move on so that's all so that's basically all we're going to do with the workflow section next up what we want to do is run that advanced filter remember we need to run an advanced filter i need to know two things one is it a sales order and two i want to make sure that it's not been finalized right so i want to make sure that the workflow is a sales order and the current status is not finalized so that is going to do it so our current status does not equal and then final there's that named range again final so whatever we have placed in here called final whatever we place here is not going to equal that so those two instances if those two instances are true right that it is a in this case it is a sales order and it is also not i want those results to appear here we can do that with an advanced filter that advanced filter will go through right now so here we go with the the document database here we're starting out here document database the last row we're getting our last row if the last row is less than three then exit the sub we're running our advanced filter based on a3 let's bring in that database here starting at a3 all the way to k3 we need those headers making sure and then our criteria is s2 through t3 so our original data a3 through k using the header row i'm going to run an advanced filter our criteria is s2 through t2 then also we want the copy to range where are we going to copy that to i want those results to appear ad2 all the way through a and 2. that's just what we have here 82 through a and 2. okay once we have those i need to determine the last results row i'm going to use a d column a d we're going to get that last results row if that last results row is less than three then we can exit out of the sub it's 11. so we can do that the last results row based on a d gonna get that if the last results was less than three then exit this up with the workflows now we're gonna so we focused on the database we've done that now we're gonna focus back again on the workflow sheet for the results equal three to last one i need to loop through every single zone three to the last row we need to extract some information the first thing what i want to do is i want to make sure that we actually have a no a reviewer that reviewer actually a-l that's got to be we need to have that if we don't have that we're not going to know where to place it right if i'm here i'm not going to know where to place it so we have to know where are we placing it we also need to know the position we've got to have that position here they have to have a position chairman stop if there's no position of course i'm going to change that to am that should be am i like that better right no no viewer position more more important okay let's exit out of there and position here position okay so no review position i like that better the document id that's going to come from a d here is a d i want that document id inside a variable we're going to put that inside doc id the name is going to come from ae you can see that above we're going to also know the position of it what is that position i need to know where that reviewer position is it's going to be based on am am that very important position based on the name then i want to know the thumbnail name that's going to come from aj here's that thumbnail name that name is going to come here notice they all end with thumb right we have to have those file names when we combine this thumb along with our thumbnail folder here it's going to be a full file path so we need to pull that i also need to know that thumbnail path remember that thumbnail folder plus the thumbnail name is going to be the full path of that thumbnail i'm going to set the position range what i need to do is i need to find that position i'm going to set the position range here i do find out right if i know that let's say here's our position coming from am in a variable if i know that position is off of staff where is that office staff what column is it going to go it's going to go here in column f but what if it is vice president if vice president we need to put that in column m i need to find out which column we're doing so we're going to use we're going to create a range located starting here in f15 all the way to the end and we're going to look for that position and find what column it's located in we can do that with this set position range is going to equal to f15 all the way through p15 we're going to find what am i looking for that position remember that position is coming from am directly here that position is coming from am we're going to look for that we're going to look at in the values in the whole if it's fin if it hasn't found if it's not found then we can skip to the node document there's right if it's not found i don't know where to put it right so we can't we can skip to the next one so if position is nothing means it's not found then go to the next doc it's going to skip everything and go directly down here assuming that it has been found we can continue on that position column is located the position range the one that's been found on on.com that is the column we got now we know what column to place it in once we have the column then what i want to do is i want to determine the left position what's the left position of that well the left position is basically the left position we can use row one of the cells were in the workflow the left position of that column and i want to add one it means i don't run it directly on that column i want it one pixel over to the right so we're going to move it over a little bit to the right one pixel i also want to set the top position now the top position is a little more technically because we don't know the top position is going to be based on it could be this it could be this it could be this it's really based on how many are inside here how many our documents are located in the single column we need to keep track of how many it's going to be located on row 101 and whatever the column is associated so basically that the top position is going to be based on the current top position based on row 16 right i wonder we're going to start out with row 16 the top position but we're going to add to that well we're going to add nothing if this is 0 we're going to add nothing it's going to be placed right here but if it's 1 basically i want it to be 1 times the height of this one times the height then we're going to place it one down if it's two it's going to be two times the height of this we're going to place it in the third so that's how we're going to be doing it so again the top position here let's go down here the top position here is going to be start out at 16 plus we're going to add something what are we adding we're going to be adding the height of this i want to add a little spacer right i don't want them right on top of each other times whatever the value is in 101 that's the number right plus two so basically we're going to multiply this so basically if it's if we have one here already if it's then all we need to do is add one times the height that's going to be our top position okay so all we need to do we're using the value here and so as we add more all we need to do is increment this every time we add one every time we add an increment we increa increment this by one that's how we keep track of it so i'll show you that as we come along in the code okay so that's going to set our top position based on the current documents inside the same column then what i want to do is i'm ready to create it so now we've got the top position now we've got the lapis we know everything we need to do we've got the thumbnail so what i'm going to do is i'm going to take this sample here the sample grip and i'm going to duplicate it and then what i'm going to do is i'm going to rename this part this part here is called sample document thumb i'm going to rename that and i'm going to rename this sample document giving it brand new names the macro has already been assigned so we don't need to create it we don't need to assign another macro so we do that here shapes sample.group we're going to duplicate it we're going to assign it a unique name remember that name document group and the document id that's the unique name we've already defined the document id up here okay so once we have that we can focus on that with shapes document cropping data so i want to set the thumbnail now what we want to do that thumbnail we're not going to set to the entire group we're only going to set that thumbnail based on one of the items inside it so what is that this shape here called sample document thumb that is what i actually want so i want to do two things one i want to take that and i want to fill it with a picture how do we do that well basically all i need to do is select here right click format the picture if i were doing it fill and then fill it with a file so we would fill it with a picture that's all i would do there if i were doing it manually what we're doing is a vba so basically that's all we're doing just filling it with a picture but we're doing it with this particular shape on the inside called sample doc thumb so inside vba we're going to highlight that with group items sample dock thumb first thing i want to do is i'm going to rename that shape i'm going to rename it to document thm document thumb remember those 11 characters very important and the document id because we need to remove those 11 characters then we want to extract the document id okay once we have that then what i want to do is i want to make sure that the document the thumbnail path is accurate if it is document the directory of the thumbnail path vb directory does not equal empty that means it's a good one it's a good path then what i'm going to do is i'm going to add fill right we're already inside this with the sample with this item filling that particular shape with the user picture based on that thumbnail this basically adds thumbnail picture as background so perfect that's how we have it that's it that's all for the thumbnail now what i want to do is i want to i got one more thing to do i want to take this text box here sample doc name text and i want to add the name of the document so basically whatever the name the one that we extracted here from ae i want to put that in the text box so we can do that with just a little lime code so document sample doc name again i want to assign it a name giving it that unique name again another 11 characters here and then giving it that document id we're going to assign it some text that text is going to be based on the document name that document name we've already defined up here based on ae here giving it that text name text frame 2 text range text equals doc name sets the document name that way each one has an individual document name and again with this particular shape notice it's kind of transparent if we look in the home right and the format actually we can do the shape fill and then we have more colors i've given it a transparent notice it's 27 transparent so that's all if we wanted to make it you know solid we could do it here and then also all we have to do is the solid one and it would be solid right so if we wanted to make a little bit transparent or maybe we want to change the color if we want to if we do that with just this it's very very easy we can easily make changes all we need to do is just change that right let's say you wanted it dark and maybe you wanted the text in this case white you could do that right it'll be very easy let's select all the text the entire text here and the entire shape make that white right so now when we run the sales order it would automatically be up so you can very very quickly and easily update i like that a little bit better maybe okay return it back to the way it was all right so it gives a nice kind of look so we all we need to do is simply change the sample and everything changes below that okay so we assign it that name now what we want to do is we want to set the group position the rest is easy we've already defined the left position so we can set the left left position of the entire group left position so we're out of here we're inside and outside of the document name here we're inside and outside of the thumbnail but we're still inside the group as a whole so we can set the left position and we can set the top position set top position and i also want to set the width the width is kind of easy because that way if we change the column width we can also change this here so the width of that shape is going to be based on whatever the column is that we're placing it in so the position column -4 so slightly less than the width slightly less than the width of the column that way there's some spacing we just set that way if we increase the column right then we simply rig it's going to automatically notice it increased automatically so i like that better because it increases with the column so it's a little bit easier and you can you don't have to change much it just automatically increases so that way we set the column width okay great so the last thing what we want to do is i want to increment that cell remember i've got to update once we've placed in a column i need to update whatever's here i want to increment it one more so we do that with just that single line of code cells 101 whatever column we're working on equals whatever is currently there plus one remember when we refresh this entire macro we're clearing it other out so as we're working through all of the particular documents we're incrementing the cells so that automatically updates that top position here as we add more so notice here this updated to zero right so notice it's not here so if i change this right and i bring it over here to one it's automatically going to change to one when i drag and drop it over the macro runs this updates to one so we've got that it gets a nice track of it very very easily all we need to do is simply refresh it so next result and then the last thing is application updating true so that is how we automatically create these very very cool things okay so now we've got a few more to show you we may want to send something to the next we can drag and drop but i also wanted to give you another option what if we just want to click once and let's say oh we need to select on let's say i want to select on something and i want to move it over one we can do that with one single button with just a few lines of code and we've done that here called send to previous revision or in this case send to next let's bring this back up here so we've got send to previous send to next approve and next so maybe you just want to do one click where you can approve it in next so if i want to click here and i want to prove it boom it approved one button will do that so how are we going to do that well all i need to do is determine if there is a supervisor in the next column making sure obviously if we're in the last column we can't do it here we need to let them know hey there's a problem make sure to move this document to position containing a supervisor name because there's no next here so all we may want to do is return it back to the original person that so maybe we're on here and maybe tim says oh we've really messed this up hank you know you got to go back i want to return it back to hank here so i just click back tank and that's going to automatically bring it back into hank's column here with just one that's back to the original so those are the few macros that we're going to show you they're all very similar so the document approved next workflows of course we need to make sure there's a document id if b2 is empty there's nothing we can do on so moving along so assuming that we do have a document a we're putting that inside a variable here we're going to set the document shape is going to be equal to document so we're setting that shape up here now the document shape if it's nothing right then just we can't move something if we don't actually have a shape so we're just checking to make sure that we actually have a shape then what i want to do is i want to determine what is the current position of that cell what column what what is the current column of this right i need to know if it's h i need to check the column next to it to make sure that it has a supervisor so we need to get that into a variable it's going to be based on that shape the top left cell the column what is the column of the current shape then what i want to do is i want to check the column next to it so the column plus one if that's empty that means there's no supervisor so we need to say please make sure to move this document to a position you saw that message containing a supervisor name that way we can't move it to somebody else okay but assuming that there is a document then all i need to do is change the update so what i'm going to do is i'm going to set this to pending review or pending remember it's that in this case we're going to use pending pending here that's the named range here set this depending and whatever the next person here is i'm going to set this in to our pending our current reviewer so if i take this and i move it over one i am automatically going to set tim over next it and it's going to be penny and then just simply refresh the schedule once i've changed it here and i save it right that's done through vba setting this setting this saving it then refreshing it automatically it's going to appear here that's what we do in the next line of code f12 is going to go pending f9 of course that's the reviewer name it's going to come from the row row 14 the position column plus one that's going to set the reviewer name then what i'm going to do is i'm going to clear the notes and revisions h9 i want to clear anything h9n clearing everything else just to make sure because it's brand new review so i want to clear here and i want to clear here so we can do that here and then what i want to do is just run document save and update once it's saved it's automatically going to refresh all right previous review is exactly the same except just we're going to check the column previous right so if we want to move it back to the previous supervisor we can do just that with here again we're going to run all the same checks here this time we're going to check position column we're determining the column i want to check the column before before that right if it's empty we want to make sure to move this document to the previous column okay also i should put here or right or i want to check also making sure that it is also if it's calm what column is that we want to make sure that if that this is called let's do equals column right we know that this is column or if it's columns column 6 right then we know that column 6 they can't move it over again so let's let's add to that or let's do it up here in the if statement here if or position column equals 6 right that would be all the way or less than okay so that way if they try to move it over even on the left one right if they click here and they try to do that we also want that message coming they can't move it left if they're on the first staff okay so the position column one is empty right so now again we're gonna do the same thing this time we're gonna put needs review i'm assigning this a different status called needs review that one here is going to be called needs or needs revision or needs review here right so that's what we're going to be doing here now that you've got a macro running so that's why it didn't show up need review here that's the one we want or needs revision here so that we can send it back and then of course the reviewer name is going to be the previous supervisor in the previous column also on row 14 minus one that's the reviewer name again we're going to clear out the notes and revision we're going to save it as it is and that's going to automatically redo it perfect okay but what about the original what about sending it back to the original person right i want to send it back if it says hank i want to send this back to hank how do i do that well basically all i need to do is take whatever is in h three and place it directly inside and maybe add something like rejected here we do have one called rejected so maybe we have one here called rejected we can do that with there so how are we going to do that well we dealt with just a little bit of code again everything's the same but in this case f9 is going to equal h3 set to the current review remember f9 is going to take on the originator located in h3 and also i want to make sure that we've taken the rejected notice we've got a name range called rejected here i want to place that directly inside f12 so we do that here f12 equals rejected and then clearing it out and saving updates so that's it that's all we have to do to send it back to whoever we want we can easily navigate we can use two ways we can use drag and drop and we can use also think uh the just the buttons here to to automate it so relatively simple this one automatically adds a specific status which is really helpful so we have a specific status so if we select on one we know that we're going to have a specific status okay we've gone over everything but how to get these things and basically what i want to do with this is i want to check here what is the supervisor i want to look for the supervisor of this or i want to check is first of all if b8 equals bf15 this formula here i want to know what does that mean b8 b8 let's look in b8 again remember we had that original staff if office staff is here we know this is set by vba but if the position of hank evans is the office staff and this he was offered then i want to put his name here but what if it's not i want i want to put his supervisor i want to basically put his supervisor so what i'm going to do in that case is i am going to index the staff position i'm going to run a check is his supervisor is the supervisor a team leader if the supervisor is a team leader we're going to put it here so it's a little bit of a complex formula but i want to just simplify it for you and basically say we're looking up a specific team leader here i want to look up who is the team leader of hank evans if it's craig put craig here so who is the team leader so once we have a value here i want to look it up and put it there so it's reflective of that i'm not going to go over this entire formula we've been over a lot in this it's already an extra long video but i want you to study this formula a bit okay and basically we're looking to the staff before and seeing is who is the team leader of software's manager if it's the correct office manager they have the correct position we are simply going to place the name of that staff directly in there that's it that's all we're going to do and we're just going to run two checks because maybe we're going to skip one right if we skip one let's say we don't go in the exact order maybe we want to skip so there's two checks one we're going to check for the current leader and what we're going to check for the staff after so there's just basically two checks okay all right very very cool we that was a really amazing training in this training i showed you how you can create this really cool drag and drop document workflow manager complete with ultimate reviews we've got have save and update and document and data mapping we also have added the ability to dynamically create thumbnails based on pdf word documents or pictures and how to also move them over and track them down and embed them inside shapes that are also dynamic an incredible document workflow manager i do appreciate these if you like these trainings you want to help support us patreon is a great way to do it i've got tons of stuff going off from patreon and i'm adding more we've got discounts and early birds you've got pdf code books i've got tons of great features there so i hope you visit us on patreon sign up there lots of great things i'm putting into that all right that link will be down below thank you very much for visiting us this week i appreciate your time and your effort that you're putting in to learning and educating yourself i guarantee you it's going to pay off we'll see you again next week for another incredible training thanks so much [Music]

Channel: Excel For Freelancers

Views: 276,579

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Excel Document Manager, Document Manager, Document Management, Document Workflow, Document Workflow M, Document Approval, Invoice Approval Software, Documents Approval, Manage Documents In Excel, Excel Document File Manager, Company Document Manage, Manage company Documents

Id: 4gIqZvR7RJo

Length: 112min 23sec (6743 seconds)

Published: Tue Nov 02 2021