How To Share and Sync Your Macro-Enabled Excel Workbook, from Scratch, With Anyone In The World

hello and welcome this is ready with Excel for freelancers and today we're gonna be breaking down a workbook sharing and sync so that you can share your macro enabled workbook with anybody in the world and sync it in real time and I'm gonna break it down for you showing you step by step we're gonna create a login screen we're going to create every line of code while you watch in fact we're gonna be starting with just this table and going through every step with you along the way it's gonna be an amazing training let's get started already let's get started thanks so much for joining me today I've got a lot to show you in fact I've covered this workbook sharing a sink in another part in fact I believe it was employee manager 20 but that was part of a much larger application that having a lot of code became kind of complex what we get is are gonna break it down we're gonna make it really simple in fact I'm gonna write every line of code during this training there is no code there is just a simple table there's nothing else on the other sheets 2 & 3 so we're gonna do everything step-by-step so you can see just how I did this and then once we're done with this training you're gonna be able to apply this so that you can create any type of sharing and sync and share and sync your macro enabled workbook with anybody in the world so it's gonna be a great training before we do get started I am really proud to announce the ultimate excel resource guide it's an amazing guide I would love to show you and have it take a look at something that I've spent a lot of time up and basically for those of you that are in sell Excel enthusiasts and love to learn Excel I've packed a ton of resources in this single guide in fact we have a hundred websites and blogs all filterable all searchable so you can quickly get to the resources that you're looking for simply you can sort any column so it's an amazing resource guide we've got 100 YouTube channels 100 forms and groups 100 Facebook groups 100 free courses that in itself is worth a ton in fact we've got a hundred paid courses for the best paid courses you can filter and sort any lists 100 books on Excel experts in consultants a hundred PDF downloads in fact the total number of pages on these PDF downloads are over 6500 all related to excel in one form or another and Excel add-ins and utilities we've got a list of a hundred of those so go ahead and I'll include the links down below if you like what we're doing here and you want to support me I'd love for you to have it I'll keep the price really low for those of you during our launch so if that's something you like go ahead and check it out I'd really appreciate it and that helps keep these trainings free each and every week all right let's get back to the training in fact this one's called workbook sharing and sync and I'm gonna show you how to share and sync your workbook with anybody as many people around the world using a shared workbook folder now this is a relatively simple process but we're gonna go step by step and there's certain things certain components that are required the first of which is we have to have a user list we have to have the ability for users to log in just like we've done before because that sets the user right so we need to know each user is gonna have their own workbook but we need to know what user that is so we're gonna start out we do need an admin sheet so let's go ahead and create that here that is an admin and then we'll call this a con sheet one we'll call that contacts and we also are going to need a login sheet this is just going to be a very simple sheet there's not gonna be nothing on this sheet other than maybe one button that says logged in and this is the default sheet that when a user exits the workbook it's gonna default back to this sheet and logout it's gonna hide the other sheets we've covered that before but it's a really important part of this so I want to include this training as well so let's just color some cells here and make it really basic we'll give it a light green ah and it's there's nothing gonna be on this sheet so I really want to just hide the rest of the columns and then also the remaining rows so that we can just keep this sheet simple so we're gonna hide I'd used shift ctrl down arrow to highlight all this and then we're gonna hide those so all I've got here is just a login sheet in fact we can we can call this login it we don't either either way we can give it up we can give it a name for the sheep but it's not really it's not really important I want to give it a but I want the users to be able to log in this is the sheet that they will see when they when they first open the workbook because we want to make sure we know what user is using what workbook okay even though they're all gonna separate users the username is critical so we're gonna focus on the usernames in the admin screen but for now we're just gonna give it a lot so let's create a button and we will just nice big button here I'm going to call it login you can actually add a log out button too I guess you can put that at any screen so we can make logging out automatic or we can we can make it so that the user manually logs out there's a few different ways so when they close the workbook that I will set the font of this a little bit bigger and then I'm gonna put it on the right in the middle and then I'll give it a color here just use one of our basic formats in that I'll I'm going to include just a little image I think I've got one here under login login okay I found one here that's just a little bit of an icon there it gives users the ability to log in okay so it's something very simple I'm not gonna make the most beautiful workbook here it's but because you're gonna create your own so there's no point there's no I just want to create something that is very simple not a lot of not a lot going on here and I want it here so we're gonna assign this to a macro we're going to assign this to a macro and so we'll get to that in a minute so we've got our nutsert that's all we want is our login page and when we click this login button we want to log in form to come up so we need to create that form our admin screen is going to be basically a very simple screen and this is only going to be visible to those particularly users that have admin access so there's a first thing we'll probably use two columns just as we usually do I'm going to need to know if it's if it's in the process of being synched or not so we'll go over that in a minute syncing I'm just gonna put a little question mark there and then the next thing I want to do is I want to know the username logging-in username so we're gonna create this and and then I also want to know the password the form when they complete the form when they exit the username form it's gonna put the username here when they exit it I'm not going to link those cells because there's kind of problems I'll show you what those are and that's so the password is gonna be here and then what I want to do is I want to know if it's a correct user Creek user and we'll this will be a formula b6 will be a formula and then b7 I want to know if it's admin so it does can be like a true or false so it's going to be something like this is going to be true or false right it's going to be or will do yes or no easier and then this will be a formula this will be password and we will we want the need one more actually let's put Adnan here and let's put the current user so the reason is the reason is I want the current user here because as soon as they login I'm going to clear this user name and I'm gonna clear this password don't explain that but these are only temporary but I don't want I want to take this using the amount of putting here this is always going to do in fact I'm going to name it I've already named this field actually in a past but the current user we want to name this field it's actually a good name from a previous sample so this field is named current user we want that that's important we're gonna use this named range in a code so current user is the name and let's highlight those let's color those just so we can differentiate we know where our I also want to create the ability to have users the user list so we'll create a user's small user it's very basic users list and I'm going to put the user name and password and then I need to know if their admin or not password and then add so admin is gonna have access to this sheet whereas normally users will not so we can merge and center this and we'll go down to say just give it but you can create as many as many as you want of course I'll just create basically let's say 15 here and format those just so we know where our username table starts and ends and give it a border color of let's say this and so we have an outline color and then a border so it's just a really basic user list as all I'm trying to do and I'll format this just so we can see clearly what our users list and when they log in and out and of course the admin will be able to handle those particular users the admin will be adding users and that's really really important component of this because we've got to know what user if we're going to be saving to specific folders we need to know what users what data what users so that's really important so as I build out this little user table here all I'm doing is just adding some effects but it's really not important your user can be a lot larger right your user table can be a lot the idea is that you want a separate sheet for users a separate sheet for admin so that's important so you whatever workbook you want create your own your own admin sheet you may want to put a lot of other stuff in your admin sheet things that a normal user wouldn't want so feel free to put any type of things that you don't want your regular users see sometimes it can be account types if it's an accounting program or different things so all right so we have that so we have a username we have R and E and then we'll put David and we can't forget Fred and Lisa and then put it one we'll put our passage here and then and then okay so we have just some basic passwords and then I want to know if it's a if their admin so I'm going to just use the basic data validation here and we'll just give it two types a list and then it's going to be yes or no so that users can cheat this is something very simple I'm not gonna use the checkbox cause we're just I'm what simple here simple simple so this is yes we'll go with this no in this no all right so we've got a very basic user list and that's going to be that'll be fine for us so now we know let's say the user named brandy and the password is one two three our our code is going to put this here and put this here but for now we're just going to put it just so we can get our formula so what I need to do is now I need to name some ranges here I think I had some name ranges but I'm going to rename those ranges let me just check because I had them in a sample I'm gonna I want to current user we have password password is a named range let's just extend this password is here okay check that we have a user name named range and I want to include all these you guys know how to create named ranges right you have note you know how to create those named ranges and admin we can create a named range like that actually I think we have it here right admin option so let's update that so all of that is is this it's just a simple creating a name on this named range and we'll include that too row 19 because I had some samples that I was working with so I want to make sure to get this right for you and quickly build it as quickly as possible so when we highlight this we'll see that this is the app and perfect when we highlight this this named range is called password normally highlight this we used it now I'd like to use now you can use one named range for the entire table and then index the columns the only reason I like to do this is when we use these in formulas it's really really easy to see so it's real it's really easy to see what when when I'm showing you that so first we want to know if if this is the correct user if it's greg user so we can start out with equals if index what are we indexing we're investing the password file and we're gonna use a match what are we matching we're going to match the username where's the username found right here and what are we gonna match that we're gonna match that up with the username name range we just created we want an exact match so it's gonna be zero okay so now we've out the road now we need to call them columns one we're going because we've already set the column so this is gonna find the password right if it is equal right if this if the password that is found the password that is found if it is equal to what equal to this right here then we know it's true otherwise false that's it do you understand that let's go over that one more time we're gonna index the password that means I'm looking in this I'm looking in this column but I need to know what row and I need to know what column well the row is set by match what I'm gonna do is I'm going to look in the username and I'm gonna match Randi I'm gonna match whatever is in b4 and then that's gonna tell us the row so we have the row in the column is going to be one just call them one because we're indexing the password that's gonna get us on row and our column if it's equal to b5 then it's true otherwise false the only thing I want to add to this is if for some reason there's an error I want to put if air and then black because I don't like to have errors especially when working in VBA so I want to wrap this in if air so if air and then we're just gonna put that as blank and then what we're gonna do is we're just gonna test if it's true if it's not true we know if this is not true the user puts in some other password it's going to either return false or it's going to return just blanks so either way so we're just going to test is it true if it's not true or false so that's a simple way just to get us the right password so that we know the password and user name is right and of course if the username is wrong as well it's gonna it's going to return a different it's gonna return false or blank in this case blank because the user names wrong but the regardless it's not yes so anything that's not yes is not going to return as a correct username of correct password so that's a really easy way to you can build this out a little bit more complex we're gonna build out a form that covers that so don't worry current user I'm going to in VBA I'm going to take this user name I'm gonna paste it right here so that's all we're going to do in VBA all right so we've got our login screen we've got our admin screen with our users and our context now one of the most important components of this is I need to be able to set a specific folder that folder is going to be different for every user every user that has this but that folder is going to be whatever the shared work but the folder is in other words we're gonna use Dropbox or you can use any other type of sharing but there's a specific folder where all of the synced files are gonna be so we need to tell the user hey please tell us where this folder is so let's go ahead and set that up now that's shared folder and it's just gonna be really a single field and then we're gonna use a browse button to do that so we'll just call it shared shared folder and then we're gonna put it right here so I'll format this let's format the border here and then we'll put a browse in there too and we'll use just the same color and then a bit of a thick border around and then I'll use a dotted line in the middle okay and let's just put this white and I wanna I want to also on a button that we're gonna tie to a macro so the user can browse so I'm gonna put a button right here just call it browse so insert shapes the shape will use the same one and I'll make it about this size right here and I would type in browse okay and then we just have to set the text so it all fits so we're gonna go into size and properties and we'll just go into text box here and then just remove the margins so that that way all over text fits and then I'm going to Center both the vertically and horizontally on that here and heat and let's drop this down for now so we can have it here and here and then we're going on let's select it here in here and then I'll give it a format of our standard blue color that I used on every single video okay so browse we're gonna tie this to macro so when they click on this button I want that folder that entire folder path share you this is required so we can't do any kind of syncing unless we have a actual folder here so that's very important so we're gonna need that alright so we've got that we've got the framework covered up maybe we're gonna add a button another button why don't we just add it while we're here that would tie back to macro I wanted to sync now when we sync you can set your sync really to Annie you can set it on sync on open you can switch you may not have a button but I'm just going to sync file I'm just going to set a button right now for it so that we can manually do that but I'll go over some some types of options that you may want when you seek in other words when do you want this workbook this workbook that you're working on when do you want all the data that all the other users have made changes when do you want to coming into that workbook so that's what we're gonna focus on Wednesday coming in all right so we're gonna focus on that so now we've got the framework done at least on there now we can probably dive into VBA I think we've covered everything here on the front end and we can dive into VBA and see just what we're gonna start off with some some changes some on sheet changes and a few macros relatively simple so let's go into the developers individual basic here clicking on video basic on the developers tab if you don't have that of course you can just go into to your options and then check your customized ribbon and then make sure the developers tab is selected here all right alt f11 will also get you there as a shortcut and here we have no macros and nothing on on any of the sheets hopefully it's just option explicit so we could have that in here an option explicit a lot of a few of you asked me what is that and that requires variables and you'll notice when we create macros it's automatic so how do you get that as automatic when I create this module how did we get that well we get that right in the tools in the options and when we see this option here require variable declaration and that's really good practice that means that if there's a variable that is not dimensioned out it's gonna throw a bug and it's really good practice so in your settings make sure you click require variable declaration and what that's gonna do is that's gonna place this word option these words option explicit here on any macros so that they're automatically created which requires you so it's really really good practice I pretty much want two modules in this the first of it will it's gonna be syncing and the next is going to be the login logout it's gonna be very simple so we'll just name those modules now and I'll bring this over here and just name that we will call that app sync macros so this is gonna be all the matters that sync our application scene macros and then we'll create one more and just call it user login logout cuz that's gonna be a little bit separate so we'll insert a new module here and then I'll give this a name called user login logout okay because that's gonna but we'll start with that let's start with our form first actually right-click we're gonna insert a user form and you know I don't use user forms too much but in this case it's kind of important alright so let's go ahead and create this user form now we have some properties let's give it a name we'll just call it login form like that and then we want to also give it some type of a caption here login basic and then I'm going to give it a title so when we click on it then the tool box comes up and we'll give it a label and for this label I just want to give user some instructions I'll change the font a little bit bigger because the defaults too small will go to twelve and two home is fine on that and I want to Center it so I'll double click on here and I want to give it some instructions so please enter your user name and password okay that is good and we'll stretch it out so that doesn't appear on two different lines and it's automatically centered okay so now we just need to create two different fields one called username my password so I'm going to copy this ctrl-c ctrl-v and place it copy this ctrl-c ctrl-v and then i'm going to call this one username and then the next one password so we have our field labels for both days this is not going to be the prettiest form you've ever seen that's for sure but it's the point is to get this form in and not necessarily make the most beautiful form I've created some forms that are okay in the past but this one is not so we can create let's give it a little color that gray just gives me a headache sorry I just can't handle it great and we can make these transparent when you see that so we can the back style can be transparent just by double clicking on here and then double clicking on here and then this one I want to make it right align it to the right and then I'm ready to duplicate it now and create one for the password so ctrl-c ctrl-v and then it'll make one for the password and we'll call this one password we also want to name these fields clear to password okay so this one we're going to call it password that's fine and this one I'm going to call it username we're gonna use these within the code so it's important to give names that make make good sense all right so we've got those and now we got just our fields and then we've got our buttons to go so let's create a text box here we will give it a little bit larger so that users can put in larger and I want to change the fonts right away here and then also go maybe it will go 12 on this too and then I'm gonna left justify this so we're going to scroll down locate that from a line left it's already there and then I want to call this one here gonna call let's change this to use your name label that makes sense LBL right because it's actually a label and then we'll change the other ones too so we don't want the same ones here name label here password label okay good so now let's create make this one we'll call this one username and then we're gonna duplicate that and make it password don't see ctrl V okay and we can we'll bring it down and then I'll use ctrl and I'm gonna align these make sure they're aligned in the center not together aligned in the center the middles okay and then I'll bring it down a little bit some spacing alright it's good but it's not perfect okay so we've got this one let's name that textbox password so we've named the fields that's important and we've given them the right font and left-justified they're very basic and we'll just now all we need to do is enter our buttons ok and cancel we'll have two buttons so that's all we need so some big buttons here and I'll change the font as well on here the for color let's go with white and then we will change the background to border like a dark blue on this not the most beautiful but it'll do and then just the font I'm going to create a larger font so that we can make sure it's clear for the users and then I'm going to name this we're gonna call this ok button and then I'm going to give it a caption of okay now I'm gonna duplicate that and create one for cancel make it a bit smaller we don't need it that big and then control C control V control C control V okay now we got one for cancel that's all we're gonna need in our form so it's going to be relatively easy and then this one is going to caption going to be cancel and then I'm going to give it a name of cancel button alright so we have that here we've have now we have everything we need for our login form so let's double click on cancel and when we click on when you use your clicks on this button what do I want to happen well I want to hide the form and I want to do anything just hide the form maybe in the future all actually I want to clear out the fields I want to log in form and I also want to clear the username and password user name dot text equals blank and password dot text equals blank so that's what I want on cancel so we've got that covered now we also want some action on ok right so let's take a look at that on ok we can also find it here we have it here the ok button and on ok that we want some things to happen on that well what do we want to happen well when I first want to do is I want to determine how the user entered the correct password and user name if they have then I want to do one more thing I want to check are the admin or not if they're not an admin what I want to do is I want to hide I want to show all the sheets but I want to make sure that the admin sheet is hidden however if they are an admin I want to show all of the sheets because when we log out we're going to actually hide all the sheets except for the login form so that's what we want to happen so let's go ahead and program that in now the first thing I want to do is we're gonna go through each worksheet so we need two dimensional worksheet dimension work sheet as worksheet okay because we're gonna go through those so we need to dimension those the next is we need to check if it's correct if it's actually correct and in fact let's do one more thing before we do that because I want to show you how we get to these values let's reduce that we don't just reduce that now we don't need that so big and what I want to do is I want to say on username username what when the user name has changed now I'm not changed when we exit when the user exit exits from that field it's clear change what do I want to happen well if you remember correctly let's pull up our sheet here and look at our admin sheet when you use your exits I want to take the user name and I want to put it right here in sheet to be for when they exit out of the password I want to put it in b5 so that's all we have to do I want to make sure that those two fields contained it now in the past I have taken those names and linked them to the form but I'm not going to do that anymore because it creates problems what I'm saying is in the past I've used a control source that creates lots of problems when changing verbs so I'm not going to use our not going to create a control source we can through VBA we can connect it to a particular cell so in the past you may have seen me use this I've decided not to do that it creates some it's just it's a bug and it creates some issues and divergence so it's not necessary we can get around it so I'll show you just how we do getting around it we can double click on the user name and you'll see when we exit this user name what do we want to happen well what I want to happen is sheet - dot range B what is it b4 equals username b4 dot value equals and then M e dot username dot value okay so that's just gonna take it there and then we want to do the same thing for passwords and so we look up the password here the one that we've named and then on we want to exit what do we want to happen we can get rid of this same thing sheet to b5 in this case sheet two range b5 dot value equals meam use the form dot password dot value okay so basically that means when a user makes a change let's take a look here in the login form when user makes a change to this username I wanted to automatically go here when they make a change to password and exit it automatically goes here you see how that happened now of course it's not gonna be so that is all we have to do so cancel that oh we got an invalid user log in form dot hide okay cover that all right so we want to hide that form and we also want to clear these two fields out so when we do that all right so that is how we do it that is why we make those change so let's go look at that again just so we can see when we run that login form when we log run that login form click on here we can see automatically automatically and then we want to make sure I cancel that those two forms are clear out let's clear these on Form B 4 B 5 range 2 we also want to clear those out when we cancel just so probably it's probably it's good practice it's not absolutely necessary but it's good practice sheet - dot range b4 dot value B 4 comma B 5 . clear okay so we've cleared those out all right so now continuing when we click it we don't need this alright continuing on now we're on the ok button what what do we want for it we've dimension the worksheet let's continue with this macro we can close this out now and we're going to check to make sure if sheet 2 is true let's bring this over so we can see both will bring this down a little bit so you can actually see if sheet 2 dot range B 6 equals true then it's a creek v 6 value equals true then and then we want to continue basically then we want to login and what else what if that's not well what if it's not that I want to put a message like else if it's not correct message box please enter a correct username and password okay so those two things are very important all right so if it's if it's not correct it's gonna be false and they're gonna go down here and nothing else is gonna happen okay but assuming it is correct they're gonna continue on so let's let's make it correct Randy Randy Randy one two three okay all right so now it's true so it's correct if we do then we want to continue on with our macro so again what do we want to do I want to check to see if if it's an admin or not that's important and I want to run through all the sheets in the workbook so let's go through that and see just how we do it the first thing now we know it's true we want to hide we can hide that login form login form hide so because it's good so we don't need that form anymore now we've got the login form hide and now anywhere now we're ready to go through the workbooks for each work sheet we've already defined this as a sheet right in this workbook dot worksheets okay so we're gonna go through each worksheet in the workbook next here's our loop next work sheet so everything's gonna be done in here alright so I need to know are than admin if sheet 2 dot range b8 remember ba it's gonna tell us if it's an admin yes b8 dot value equals yes then admin okay then alright smart mark that admin so we know then else it's not an admin and if close our loop okay so we have our admin loop so first we're checking so if it's an admin in that case what do I want to do I want to make sure the worksheet so actually we got one more thing here to do we need one more loop if the worksheet because this is only gonna be for worksheet Adam if the work sheet dot name equals admin then then what we can do okay then and close this in an and if right here so I'll clear those loops out all right so we've got a we've got two ifs first if is it an admin sheet then do one then do something so that's our loop and I'm gonna bring this out so we can see it okay so we're in the leap for work sheet next worksheet and now if the work sheet that it's currently on its looping through all of the worksheets and of course if your admin name is something different change it here if the name is admin then let's check are they is this user an admin or not if it's yes then do what then work sheet dot visible equals visible else what else we wanted hidden work sheet dot visible equals very hidden because we don't want them to unhide it very hidden so we want to hide the workbook if they're not that's all we need to do so you see how we can do that now we're ready to move on okay continuing on with our sheet so that's all we have to do in for the remaining sheets that are not for the remaining shoes that are not admin if their admin then what do we want to do else right here's a : not admin okay let's put a comment not admin we just want to show them worksheet dye visible equals visible okay so these are non admins this is an admin this is not admin so we want to make the remaining visible all the others visible and if there's its admin see we just run a check to see if the users so now we can make give the users the ability to see all the work she's except for the admin if they're not an admin so that's and the last step is I just want to clear out before here's of what I want to do I want to put clear these I want I don't want to oh we do need to do one more thing I need to make the current user in be seven so we want to do that that's a very very important be seven has to be the current user so we're gonna check so sheet 2 dot range B seven dot value equals of course our current users before currently before so XI two dot range B for then now we can clear out b4 and b5 now we can clear my right this is the username set current user sheet now we're gonna clear it out and now we're gonna clear out b4 and b5 because those are tied to the specific user name so I just want to clear those out those are tied to the specific form so we're gonna close sheet two range b4 b5 clear those contents b4 b5 it may not be a necessary step but it's something I just like clear contents ok so now we've set that now we've got our username we've got our OK button we've got everything we can test it out let's go ahead and run that we'll go back let's tie this to the macro we've got a login form so let's write the login let's write login out that's a little bit easier because we can just write it so let's write our sub login user user login and then we have one more called user logon okay so we're gonna write those two we can then we can set our scope first of all we want to clear when they're logging in I want to clear everything I want to clear it just in case there was any previous usernames and passwords here and whatever the current users so I want to clear out b4 b5 and b7 so we can do that in the code right here sheet 2 dot range b4 comma B 5 comma B 7 dot clear contents want to clear those out just to make sure and the next up I also want to set any previous form login form you can use that form dot username dot value I want to make sure that's also clear too all right and now if you want to save the previous user login you can you don't you can not clear this out in other words if you want the user to save you don't want to clear have them type in the username you can not clear this out farm dot password dot value equals close ok so now the last thing we're gonna do is just show the form login form dot show ok so now when they click the login form it's going to show great ok so now what I want to do is I want to tie this button to the form that we just created assigned macro user login ok so now we've got it so now when we login now Randy one two three and then click OK alright that looks good and now were you gonna try one more thing it's gonna hide I forgot the password though David four five six ok let's login David and see how that goes David David four five six ok alright admins not hidden let's take a look at why not because it's not let's take a look at the admin here David okay that's not right so we've got out of formula here now let's add that formula in here because it can't be equals equals all right index what are we indexing the admin we're admin option and what's the row well the row is going to be the current user so let's take a look at that we know that match we're gonna match the name here username zero automatic and the column is one okay so now we have no and yes okay I forgot to have the formulator now we've got the formula in here and again I'm gonna wrap it in if air alright so now of course David's gonna comma double quote so that's just in case those narrow David's no and then Randy would be yes okay perfect so let's go ahead and log in one more time go to the login got to have our formula log in David four five six click OK all right now admins hidden you see it's gone right admins no longer here login again under Randy one two three click OK alright one more thing we got to do before we set that up let's go back in here is we need to set this current user all the way up here all the way under after Ben so not right after but there so we'll hit enter then because once we are we need to put this there because we're checking for yes here right and yes this yes is tied to be 7b7 right so we need to make sure that that is there before alright and login Randy and then one two three okay now Adams showing it to move this up here sheet to be seven right because this yes here is based all right this yest is based on b7 right so we need to make sure we're checking for this yes we got to move I moved it up there just a minute ago I move this up to the top right sheet to b7 so we're taking the current user and we're moving it we had it down here so that's gonna help us down make sure it's alright so b7 our current user gets moved up all the way to the top as opposed to being down here alright so we've got the login we've got the admin set up let's continue on now we know we know the current user the most important thing is we want to get that current user right here current user name is so now we can continue on okay so here's the idea we can start now our sink what I want to do is any change the user makes any change I want that to automatically be saved in a shared folder so how do we do that what we need to get that shared folder and then we need to put it right here into P 3 let's add a Mac or so when we click browse it puts it right there we can do that in our macros you know let's make this a little bit bigger here in we can use it in the modules let's add let's put that in right here in fact maybe I should add laga let me add logout first ok let's start it first thing I want is dimension the worksheet as worksheet just as we did there before and the next thing is I want to make sure to go to the login first right we cannot hide other sheets if we're not actually if we're on the contacts we cannot hide it if we're on the admin it cannot be hidden so the first thing I do is send the user over to the login screen then hide the other sheet so we're going to do that within the code so work sheets login dot activate ok so that's going to activate it the next we want to run a loop for each work sheet in this workbook dot worksheets next work sheet ok so that completes our loop what do you want to I want to make sure that it's not the login if work sheet nickname does not equal login then what we're gonna do hide it work sheet dot visible equals very well hidden ok so that's gonna hide it so that's all we need to do and that's going to when we run that it's going to hide everything else but this so I just ran it let's take a look at it ok they're all hidden but the login great so that worked Randi and we'll log in again so now we've logged in and logged out and that's a really important part of the process of the shared work break so we got that covered ok now let's move on to where we were now that we have the finished now we can go to app sync and now we can add in that browse browse folder so let's do that right now sub browse shared folder is a very simple macro dimension the shared folder as file dialoguing and file dogs very important Mac uses slightly different so you might want to check on that set the shared folder set shared folder equal to application file dialog and what do we want not file converters file dialogue just died file dialogue what do we want we want a folder picker so MSO file dialogue folder because we want to set a specific folder okay good and then with the shared folder we want to do a few things spell that right that's together with the shared folder what do we want to do well the first thing let's give it a title equal to select a shared folder okay and then I also want to say if just in case the user hasn't selected anything dot show does not equal negative one then go to no selection and this will keep from the user from having bugs so we'll go to her no selection all right so assuming they did then we're gonna do sheet one dot regs I think it's let's take a look at that where do we want to put that contacts I want to put it right here into p3 and I've already named this range shared folder given that a named range called shared folder p3 is where it is so let's put it there in 2 P 3 dot range p3 dot value equals dot selected items one get just one alright so that's it so that's all we need to do just to get our folder in our fly our file path inside there so now let's assign that to the button that we created for that and check it out right click assign macro browse shared folder click OK click the Browse ok and we got it I'm gonna set it I've got one already here called shared folder let's pull it down here and I've got one in my Dropbox in my Dropbox right it's in a shared folder you can use Google Drive you can use P cloud or you can use Microsoft onedrive or a few others but as long as it's a shared folder that you can share that with other users so it must be shared with people around the world that's important alright so now we've got the shared folder it's set up we've got that now that's an important part now we're ready to start writing macros so the idea is here when I make a change to anything I want that change to go into the Dropbox folder for all the other users and all of our other users are here all these other users so that's what I want so the first thing we need to do is write some code but in this particular training in this particular feature we only want the users to be have the ability to change one cell at a time because when we change that one cell we want the address we want the the individual want the sheet name we want all to go into a small text file so that the user so that the other users can pull up that text file and make the changes into their specific folder so how do we do that well the first thing I want to do is make sure that users cannot make a change to more than one cell at one time so we need to prevent that we can do that with these on sheet macros so when we go into the context we can write some code here it's gonna be for worksheet change right we're focused on not selection change but when the user actually makes a change not selection change but when they actually make a change so what I want to do is I want to make sure that the user has not selected it in case there's more than one but first of all I want to set this is very important I only want to focus on this if the user makes a change to this area this is the only change that I want to share with other users your range your be multiple sheets our merchants but we're gonna focus on just this range right here d5 all the way through K 18 that is the range that we're gonna focus on so let's do that let's write some code so we say if if not intersect actually let's try this if all right that's faster ahead on autohotkey intersect d5 through what did I say K 18 K 18 is nothing then then what then do something but the first thing I want to do is I want to know basically if they've changed more than one cell I want to undo that action I want to undo that action I only want to allow the user to make a change to one cell at one time so how can we do that well weekend up with just a few lines of code we can use application undo so first of all we need to run a test if the target doc count is greater than one that means they've changed more than one cell then do something what are we gonna do I'm gonna undo application dot undo but before I do that we I need to make sure that there's no screen updating so we have to turn off the application screen updating then turn it right back on application dot screen updating equals false and then we're gonna turn right back on after undo application screen updating equals true okay so now that's important so now the user changes more than one cell it's not gonna louse it and we can add n there we don't want anything else to happen so and we'll close out all of that other it'll not continue with anything else in this macro if we put in because we don't want if the user we just wanting and user side and continue on doing anything it's alright so now if we go ahead and highlight a large amount of cells and we try to delete it's going to return automatically so that works well so that's very very important of course if they try just one cell it works just fine all right so that limits it to one cell and that's what we definitely want so now we can continue on with the macro that writes it so what do we want to happen next well the first thing is I want to make sure there's two types of change and those are both important to differentiate there's one type of change where when another user has made a change and then they will change in this workbook right so let's say another user made a change of this address and now we're in the MUP process of syncing and this file has changed when that type of change is made I don't want to sync it back into the workbook for the other users right that's like a double it it would create a loop and we don't want that so we only want the type of change where the user itself is actually making the change and not the macro itself and we differentiate that so when the sync runs we make sure that this is true when the sync is running it's false when it's not so this has to be false so it means it's not currently singing sheet 2 B 3 must be false so we want to make sure we run that so if it's if it's false then we can continue on so let's do that right now with a different loop if if sheet 2 dot range b3 got value dot value equals false then we continue on then we continue down a little because we want to make sure that's very that's a really important we also want to make sure that our shared folders are named range shell Florida is an empty that's really important because we've gotta know where to save those changes from so that's on sheet 1 sheet 1 p3 are also a named range called shared folder we want to make sure that's not empty so we can run a check on that in our code if so if sheet 1 dot H we can use our named range folder dot value e does not equal empty then we can continue on then empty then we continue on so we got an if and if another if we can also probably put in a we just put in two exits up let's just go ahead and write empty its value equals MD then end okay that wins we're gonna end it if it's empty because we don't want to continue on anything else if it's empty now we're ready to go dim the user row what I want to do is row as long why do I want to do with the user oh I want to go through each user so I need to know the user row what is each user well that's a new users here in the admin each users are starting in five going all the way to 19 of course if any is blank will get out of there but I want to run a loop all the way from five to nineteen user one go through each user because I want to add that change to each of their folders so we can do that right in the code right here so we're gonna dimension that user row that's important we'll need that dimension the current user has a string current I need to know the current user because I don't want to add anything to the current users folder if I'm making a change in my folders Randy I don't want to put my changes in my own folder only other users changes come into my folder so that's important current user as string and also the shared folders string didn't the shared folder as a string not that and then the username that's going to be the as we loop through all the users and user name has a string that's important we need that and then of course we're gonna be using the file name dimension the file name as a string so we need to mention that and of course the FS are the file system object and dim F and so that's gonna be our file system object as object and then of course we're going to be working with a file in text file so we didn't need to dimension a file an object file o file will call it as object because we're gonna be working with a specific text file so we need to set so we need to set that as an object alright next up we want to create a file system object a scripting object actually and that's we're gonna use for our small text file so what we can do is we can click the FS Oh equal to what equal to the create object what kind of object we want we want to create a scripting scripting dot file system object that's the kind of object I want file system object so that is the object I want to create that's going to create our little text file and now I want also set the current user the current user we know what that's in of course that isn't a named range called current user I think it's also be seven or sheet 2 equals sheet 2 dot range current user why am I using current user because I can dot value because I've already said it has a named range where is that right here b7 current user that's our name range so we can use the name range even in the code all right next up we hope will comment that out even though it's pretty obvious current user alright next up we need to set the shared folder what is that the shared folder equals equals sheet one another name range range shared folder already showed you that named range shared folder ok so we have that dot value shared folder all right so we've got the shared folder file name we've got the current user good all right now we're ready to start our loop what is our loop going to be our loop is gonna run through all of the users right here your table will be different we're gonna start off v and go all the way to nineteen so we can set our user row from five to nineteen for user user row equals five to nineteen let's close our loop next user row okay so now we've got a loop so now we're gonna run through each of the users and what are we gonna do the first thing I want to make sure that if she to do I want to make sure if it's empty right if we've gotten if we've gotten down to the word that's blank and the only thing with this is you don't want to add a user here in here and here it really you want to keep them in order so what I'm gonna say is if it's blank then just exit out as long as your users are continuous themselves it's fine so once it's blank lays eggs now let's write that a little bit of code right now if sheet 2 dot range D because that's the column D and the user row dot value equals empty then Y then go to no user we'll just create that no user and then we'll send it all the way down here at the bottom under as long as it's under here we can do no user and so it's gonna skip out all the next it's gonna skip this next and go beyond it so we can so that way we don't once we're out of users we don't have to continue through the loop you can use a do-while just as well do I don't use do Al's too much but you can use those as well that would be fine as well all right next up we need to test I want to know is there a user right so what I want to do is I want to create here is our shared folder here's our shared folder in my Dropbox right there's no users there but what I want to do is if there's no users there each user should have their own folder so if it's not there I need to create it so that means I'm going to need to create a folder for each of our users all the way so if there's no folder and that means that if we can create the folder and that means there's nothing to sync back so we just create one for each user so we need to make sure that each user has their own folder so we can do that now with this little bit line of code it's not very much first we need to run a check so let's go back into the VBA bring this up a bit ok username will set the username equals we know it's not blank sheet to range D and the user oh and the user wrote value ok so that's R so that is our username now we set that up so now what we need to do is we need to check that there is an actual folder ok but first before I do that I want to check is the user name the current user if so then go to next user if current user equals user name then go to next user what is next user it's going to put that right down here next user okay so what I want to say is if if I'm making a change right if I make a change I want to put it in that change I want to put that change in everybody else's folder but I don't want to put it in my own folder so that means if if the current user if we get to the point in the loop so again let's look through the code so here I'm gonna take the change that I made and I'm gonna put it in everybody's folder but I don't want to put it in my own folder so I want to skip me I want to put it in David's folder Fred's and lisas folder I want to put it in all their folders the change that I made but not my own folder so that's why we use that letter code just just skip that so if the currents they go to next user alright so now we are ready to write it and let's continue on if the directory okay of the shared folder we're gonna check to see if that if that folder actually just folder and backslash quotes and what is it username I want to check for the folder now is it there if not I need to create it and okay user name if the directory okay let's we're checking on that VB directory equals blank then we need to create it so then let's go then created so I need to create it if it's blank right if the VB directory is blank then let's create it and we can do that actually we can do that with one line of code we don't need the end if then then what then simple f:s oh that's our file system object I think FSO dot create folder what folder are we creating we're gonna create the folder it's gonna be this basically the same here right here just gonna copy this right into the shared folder plus username so we're gonna create the folder if it doesn't exist let's close this out we don't need anymore alright so then we can create that then to create the shared folder that's all we have to do so that's gonna create this says it's going to create the shared folder so we're good to go on that now we've created it now let's continue down our macro so just in case there wasn't a folder now we've created next up the file name uh set a file name file name is equal to the shared actually we can just paste it it's gonna be the shared folder plus the username and what else and I want to use the target the worksheet name and the target the worksheet name target dot worksheet dot name that's the name of the sheet because we need to know and what and the target dot address because I want to name the file that address right I want to name that little file I want to know the sheet name and I want to know the address why do I need to know this because when the user pulls that information up we know exactly what sheet they need to put it on what cell they need to put it on because it's already named so the target address helps that and what and txt and dot txt cuz it's a text file ok good now we've got the file name now we can set set remember we created the auto file we need to create it set the old file we've created it up there equal to what is it going to be equal to FSO the file system object create text file create text file and then the file name we define that so now we're just gonna create a text file with that file enabled all right so we've got that set up let's just capitalize just so it's easier to create text make sure I spell that right file okay so we're creating the text file there and now what do we want to do with that text file now that we've created it we want to set the target address and the target value we're gonna write to that so we're gonna go file what are we doing right aligned well I want to write inside that file right line what am I gonna write I'm gonna write target dot worksheet I want to write the sheet name and then I want to separate it with a comma and parentheses comma parentheses and what else I want to write the target address I need to know what address the user made that changed target address we need to know what what address what what is the cell the target dress that's the cell dress that they made the change and a comma again parentheses comma and the value and the value so that means we have all of that information target dot value so inside this file inside this line I've got the worksheet name I've got the target address and I've got the target value all in a text file because when the user pulls it up where when the application pulls it up they know what she could put it on they know what cell to put it on and they know what value to change it to all in that one line of code we just need to parse it so we're done with that now we need to just close our file Oh file dot close now we've closed the file all right so we need to repeat that for each user that process of when we make a change each user and then we'll clean it up now we just need to clean it up what do I clean it up just set the episode of nothing in the O file to nothing set FS o equal to nothing and then of course the O file set o file equal to nothing and that's good for coding okay so now we've got that now we've cleared that out now any change we make should automatically change in the application so let's try it out go into the application and then we'll go into our named range and we will just change this to Fred and see what happens okay let's take a look in our shared folder now we have three different notice my name's not there right every other name is automatically created and what is in this file again we have the sheet name we have the cell as the name right d7 and when we double click that what do we have I have the sheet I have the cell address and I have the change that we made all separated by commas so now every user has this change they know what to pull up they know automatically what to pull up and we're gonna run that we're gonna run that sync macro right now of course so you see every every automatically got created and in every folder is the same exact file every folder has him alright great so we got that so now we know how to add it but how do we take the information and now we know how to add chains and just just so you know if I make one more change let's take a look at this I'll just do one more just so you can see we'll change this to woodland weed Woodland Hills okay so now that's change is G fifth fourteen to Woodland Hills on the contact sheet we look back in our folders in our folder here we have again three folders but now we have two files now we have two files g14 contacts g14 changed to Woodland Hills see how it works okay so that's helpful but now how the second part of this is actually how do we bring that information and sync it to our file so let's write that macro now now they have it now so when you want to write your code all you need to do really is copy and paste this code change here change the range remember the range might be different than every sheet only the range where you want to save the changes like if you're running reports and things like that those are per user right you're not changing data so you don't want that you don't want to include this range in that but if you're actually changing the original data that include those fields here so you can pretty much just copy and paste it everything is the same other than this range for each worksheet for the most part I think it's everything is the same I don't think we changed anything else so for every sheet you can copy this page Oh remember this this here application undo this must be at the very top of worksheet change very top has to be the first thing so keep that in mind right here you can we can have lots of other code down here it's fine but that undo must be the first thing they see okay must be first thing in the code otherwise it will not work okay so we have our application sync macros or all we have is a shared folder but I want to actually create the macro we're actually going to sync and that means when I have my own folder Randy what changes come into my computer or what changes so when I pull it up let's do that macro right now it's not a very big macro so we want to run a macro that updates my file with all of the changes of the other users so we can write it out sub update file okay so the our macros is going to be someone other you just make changes this macros gonna run and it's automatically going to update so we get a dimension a few items so more than few dimension the file name just as we did because we need a string just as we did before we need to know that file name dimension the file path as a string that's important I need to know the path also the long file name back we're gonna need as well and I'll explain that long long file name as a string and again we're gonna need the FSL object dimension the FSF filesystem has an object and late binding we're not we're not using it here we're gonna define a later so it's late binding and again Oh file as well just as we did before Oh file as an object that's going to be used for our text file we'll need that next up dimension the TC file TC is gonna be our fault TC file as a string we need that for the file we also need to sync text that is the text within the file so dimension the sync text that's the text which we're gonna actually sync as a string and also of course the current user that's important we need to know that current user s string we need to know the user name also very important and the she named whatever she named our on sheet name the chid that is the name of the sheet that had those changes as a string and then of course we also want to know the cell address the dress remember that's very important so we need to know what cell address as string okay and then the cell text what is the value of that cell we'll just call that cell text to mention the cell text has string all these parts are going to be it happens very fast but we need to do that okay so just like we did in the earlier macro we're gonna set the FSOs the create file system object scripting object so set fs0 equal to create object and what kind of object we're creating the scripting file system object that's the one we want file system object that's going to set our file okay next up we want it also we want to set the current user we already know number one over that the current user is equal to sheet 2 and then we have a named range for that current keeping it simple current user okay we need to defend dimension that as well dot value okay so we've got the current user that's very important we all know and now what I want to do is I want to make sure that there's actually a shared folder right we have to make sure this is not gonna work if there's no there's no file here this is not gonna work at all right this is clear so let's run a test to make sure that the shared folder named range is not empty we can do that with this line of code all right we can check for the directory it's easier if it's either missing or if it's incorrect we can do that with directory if the directory of what is that sheet1 range shared folder dot value so we're saying is if this directory and why we do that that means if it if it looks it up and it can't find the file account if it's missing or if it can't find it it's gonna return the same air and we just want to cancel out of that because if it's if that folder is missing or if it's incorrect we always should not continue on we won't be able to so parentheses dot value VB directory okay equals empty then what do we want to do then exit sub nothing I can do if it's that folders not correct okay so now we've tested that now if we're going beyond this point we know that the shared folder exists and that it's a working subfolder proper okay so now let's set the file path the file path is going to be this equal equal to sheet 1 dot range shared folder dot value we notice this shared folder and what and we also know that it's gonna be the backslash and user name and okay one more time one more backslash okay okay so now we've got that now we set the file path that's gonna be important moving forward okay so we got to run a check so now I need to make sure if there's no folder right if I'm syncing everything from my folder but it doesn't exist so the least I can do is create the folder and exit out right so if my folder doesn't exist that means there's nothing inside the folder but we do need to create the folder in case there's future changes by other people to put in that folder so if the folder doesn't exist let's create it my folder which it doesn't and let's exit out of the sub so let's do that inside the code right now we can do that if the directory again directory we can just file path dot DB directory we can use the same type of equals empty then what then created ok then create it and then exit out of step FSO create folders right create folder file path we've already set the file paths that we know it it's the shared folder plus the username FSO create FUP so that's gonna add folder users put user folder if needed and then of course there's nothing in that folder because we just created so we can exit out there's nothing to sync exit sub so we're gonna exit out of it exit nothing - sick okay so we've got that covered it just creates the folder in case so now we can continue on with the sink so now what I want to do is I want to set the sinking to true we're ready to sink and that means right here in the admin I need to set b3 to true remember we had to check that remember we want to make sure that this is set to true and of course before we finish sinking we're gonna set this back to false so sheet to b3 it's getting equal true let's go ahead and add that in right now sheet sheet 2 dot range B 3 dot dot value equals true set sinking to true this way this way nothing else is gonna happen in other words it's not going to loop or run the sink automatically when the data is being brought into your work because that's really really really important and what I want to do is I want to make sure that I set this to false so we're gonna copy this and before the end of the Mac or we want to make sure that we set it to false so let's do that now because we know it's very very important so set sinking to false because before we finish the macro all right so now we can write everything in between here again let's set the file name is going to be the directory path and what I want to do is a run a loop so the first thing that we're going to do is we're going to set the file name for the each file name equals directory file path and anything and that's a txt file inside that director anything using the asterisk right using the asterisk that's going to be any that means any name with that that's our wildcard character so that any file that ends in txt is going to work for we're gonna focus on that so the file name is the directory of that and now we're gonna use a do-while loop so that means do-while any time we have a files for all the files the length of the filename we're gonna use this as our test so it's gonna loop through all the files and when it's done all the text files it's going to leave is greater than 0 so we're going to run the text to do all the length of the filename is greater than zero that's gonna start our loop start of loop okay and of course we're gonna end the loop we've already ended automatically our autohotkey does this so if you don't have autohotkey grab it it's actually a bonus on the dashboard course so I've included that there if you want to pick that up it'll automate your coding which is really helpful okay so we got to do name let's set our long file name long file name is equal to the file path and the file name that's gonna set our long we're gonna need open it up that's the text file we need to open open so now we're ready to open open long file name we're gonna open it up that's important and then for input as number one I want to focus on the first line of that file line input or focus just on that line I want to focus on that one line let's take a look at that that's what we're gonna focus on line one what is line 1 line 1 is right here this is what I'm focusing on the sheet that cell and the name the user and the value the value ok sheet the cell address and the value those are the three items they're all in one single line so we're gonna focus on that on line one line like this line line input number one sink text so we're gonna assign a string to that line 1 I'm gonna call that string sink text now I have assigned a variable called sink text so now we can work with that sink text so we're done so I'm ready to close number 1 so as long as we have the information all the information now is in sink text so now we can continue on so we can close number 1 okay so now the sheet name we need to define I need to know the sheet name I need to know the cell address and I need to know the value I need those three items all of those three items are in this sink text but they're parsed by commas we went over this before so we're gonna go over quickly again I want to just pull out I want to pull out the sheet name I want to pull out the cell address and I want to pull out the value and the parsed by comment so I can do that with the following lines of code here we go so the sheet name is equal to what is equal to left sync tax remember that's where everything is located in that text in string we're looking for the first comma right in string the sync text again comma I'm looking for what I'm looking for that comma that's what I'm looking for and then - one - one - and then in spreadsheets so this is going to take what it's going to do is going to find that first comma let's keep this open here so we can see it it's going to take this let's reduce it we don't need this big it's gonna take look for the first comma subtract one and it's gonna get the left that's going to extract the condom next up our next line is gonna pull this address right here so let's do that now we have the she nameless market sheet name alright next up we'll you want the cell address cell address what is that it's equal we're gonna also use mid this time because it's in the middle mid what is the mid the sink text right that's the full line and we're gonna use in string again in string sink text so what I'm going to do is I'm going to pull the middle the first part of its going to be the sink text looking for the comma starting point that's going to be our starting point our first comma write the sink text plus one plus one because I don't want to start out the comma I want to start 1 over that's going to be our starting position now what about our ending position again we're gonna use in string so again we're gonna start this is our starting point the sink text the first comma plus one our starting point right here our starting point is our first comma plus one so it's that Dawson that's our starting point now what's our ending point it's the last comma right minus one so let's focus on that our ending point our ending point to pull up that they'll address is here in string again we're gonna use in string again sync text comma and then we're gonna look for the colon 2 colon why are we looking for the con because that is going to tell us where it is let's take a look at that all right we got to add a colon in there let me update that cuz we're gonna be looking for the colon it's right here this should be a col and when you separate them that should be a colon okay I want to separate I want to have a comma I want to have a comma after the worksheet name then I want to have a target address then I'm gonna have a colon okay we need that call it alright missed that up not a comma dish should be a colon okay so when I change that let's let's make that change right now just so we can see how that would look okay colon naught comma because I want different characters it's much easier when we have different characters so now let's make a change just double-click on this on g18 and take a look at that and close that out we really want the call and that's gonna help us let's pull up though just the last change we made into the Explorer and take a look there okay so now this is the last one we change so what I want is a colon so the first one is a comma second one's a calling you saw we made this change so I'm looking for the colon now right here so let's continue on with the code okay we messed that up at the comma so we fixed it so make sure in your code just so I just so you know we're separating or separating the not a comma not a comment should be a colon like that okay between the value in the cell alright because that's going to be easier easier much easier alright continuing on with the code so we've got that we're looking for the colon here alright continuing on with the code let's continue on we're looking for the colon so we're gonna sink the text +1 in stirring we're looking for the colon and now we just need to subtract the number of characters what am i in string sink text once again sink text the comma we got to look for that comma sink text and then we're looking for the comma this time so that's gonna tell us how much to subtract out of it there minus one so that is going to give us our cell address so again let's take a look at that here and the g18 we're going to go look for the first common we have our starting point now I need the ending point or the number of characters so that's going to tell us out subtracting this out so we looking for subtracting the number of cells out it's going to get us the number of characters up until this point so that's how we use the mid to pull just this information that's all we need there okay so again we've got the first part we've subtracted out the last part that remains just the part where we've located the colon just the part there that's going to get us our cell address all right next up is the cell text cell text I want to get just the cell text and we can use again insert this time we're going to use write sink text length of what sink text - in string sink text comma colon so basically what I'm going to take the colon looking for that colon and I want everything to the right of the coal and basically that's what it does that's what that line of code it's gonna pull everything to the right of that colon you just saw it there so that's gonna get us ourselves text alright now we're ready to put the volume in there so we can do this we have the sheet name we have the cell address and we have the text this workbook dot sheets we know the sheet name sheet name right from above dot range right and what is that we wanted the range is gonna be the cell address what is that the range cell address cell address okay so we've got the cell address and now what are we going to you dot value equals cell text equals cell text that's put us our change right where we want to on the right sheet on there right now we're ready okay now we can delete the text file now we're finished with that text file so we can delete it we use the word kill kill whatever want to kill when I killed a long file name that's going to delete it right there and then the file name we're gonna reset the file name because we need to loop through these so we're gonna reset the file file name we're gonna loop through these so we need to reset the equals directory we're just going to clear it out so that we can loop through the next one directory and what this does is it clears out the current file name clear out current file name that way it's ready for the next one in the loop and that is how we do it that's all we do and then of course we gotta clean up and how we're gonna clean up well we can cheat too we know we need to return that back to false on b3 all right and set the FSO to nothing equal to nothing and of course the Oh file set the Oh file equal to nothing all right we have that and then of course we have b3 all right we're set up that is all we need to do to run our lip let's check for errors I'm just gonna run this macro okay username variable not define let's take a look here alright this should be actually current user all right so we've got that up make sure we current user let's continue on all right and the sync text this has got to be changed to sync text not just sync continue on see if there's any other issues okay so i'll address too many S's there let's take a look that's what I get okay so I think we're good now we've cleared out all the bugs in there save our work and let's take a look back in the file and see what we can see back into the shared folder after running this code it created there's gonna be nothing in here but now we can see that Randi is in here you can see that the folder is created of course there's nothing in here so what I want to do now is I want to create another file so I'm gonna save this just in case and what I want to do is I want to create another workbook light duplicate and log in as a different user file save as I will just call this Lisa it's gonna be Lisa's file and then we're gonna log in as Lisa on this one okay Lisa doesn't have a password that's fine for now log in Lisa no password click OK okay so now we're logged in as Lisa right and you'll see that our admin is gone which is fine and now Lisa's gonna make it change Lisa's gonna make it change to D 18 and we're gonna call this Kim Kim okay so Lisa just make it change now let's take a look at the Randy folder and you'll see right Randy here is the change has been made okay so now you'll see so let's open up so now let's we can open another file we have Lisa so let's open up the original recent and I'll look in here which is this workbook here ok so now we need to sync the file you see this is the old Barry right but it's gonna look in my folder so just to double-check we're logged in here as Randy under the shared workbook that's important and we have another one here this is Lisa's file and we you can't see the login because that one's hidden but it's fine we just made the change so now what I want to do is I want to run the sync file on my folder I want to run this but let's assign the macro assign the macro and just to this work but just this worker so we know and what we're gonna be doing is we're gonna call it update files so now as soon as we click sync file this Barry should change to Timothy so let's click it now and there it is changes Timothy so if you have multiple users using changing multiple that is how you do it and of course it happens all through the magic of Dropbox in our shared folder and you'll see now in Randy's folder it's gone because the file was deleted all right I know this was an extended training hope you have appreciated that and I know a lot of we went through this before but I really wanted to break this down it's a very very powerful feature because this gives you the ability to share macro enabled workbooks with anybody in the world so thank you much for joining me on this training I'm really happy to bring this to you if you have any questions comments please let me know down in the comments below whether you're on Facebook or YouTube of course subscribe to my channel if you haven't already you can click the subscription and notifications down below and if you haven't had a chance to go ahead and check out the dashboard masterclass or the resource guide both are amazing pieces of work so I think you'll really love them to advance your excel freelancing career thanks so much and have a great day [Music]

Channel: Excel For Freelancers

Views: 65,522

Rating: 4.969378 out of 5

Keywords: Workbook Sharing, Excel Shared Workbooks, Share Macro-enabled workbooks, share macro enabled workbooks, share workbooks with macros, share excel workbooks, sharing excel workbooks, excel shared workbook feature, shared workbook feature problem, Sharing your macro workbooks, global workbook shareing, Sharing Excel Workbooks, How To Share Workbooks In Excel, Excel Shared Workbooks Alternative, An Alternative To Sharing Excel Workbooks, Sharing an Excel File

Id: aKV5seZmiBs

Length: 88min 47sec (5327 seconds)

Published: Tue Apr 09 2019