Great ways to get shared folders and we'll go over a few of those like Dropbox Google Drive onedrive and there's a few other great but that's that's easy and free the shared the shared folder is the easy part so I don't want to use a network right that that means you know a hard network would be like computers in your office and also workbooks containing tables cannot be shared in tables is something that we use there's also lots and lots and lots of other limitations with the shared workbook feature with this shared.
Work feature let's go over some of those limitations because that's gonna move right into why we want to do this I made a list here you cannot create an Excel table insert a delete these are things that you cannot do with the standard Excel shared workbook merge cells or split merge cells and we want to do that protect unprotect inside the code in fact one of my favorites you can't use advanced filters inside a shared workbook which is quite a shame you cannot use text to columns group outline data insert automatic sub.
Tons and tons and tons of these of these features that are really really cord and very important to excel simply we cannot do inside a shared workbook well today we're gonna create a solution I'm going to show you how to create a solution that allows you to share your workbook data with anybody in the world with as many people as you want at the same time with this unique and incredible feature that I'm gonna show you and it's not too.
Difficult to achieve so follow along and I'm gonna show you exactly how to achieve that and one of the things that we're going to be doing is we're gonna create a contact manager this is just a simple program and basically what it's going to allow us to do is create new contacts and save those contacts inside our lists always new save it and we can put address and then they're automatically saved down at the bottom of this list we can select on a contact here and update the address and then we.
Can click update and it'll update the contact here we have the ability to refresh the contacts delete we can delete a contact so if we want to click on here we can delete the contact and it will delete it automatically now now I'm gonna go over that with you but we're gonna go over that quick the idea is basically that in this particular mini application that I've created we can create new contacts we can update contacts and we can delete contacts so the program is very very basic but that.
The idea behind this is that the data here gets saved and can be shared so that there could be 100 people using this workbook or actually a copy of its and all doing the same thing at the same time and sharing their data and then their data will also appear on this list so that is what we want to achieve now the important thing that we understand is you know Excel is is it is a spreadsheet program so we kind of have to get that out of our mind and think of it more as a application and any true.
Application has what we call a front end and a back end and the front end is just what you're looking at here it's basically the a bill to enter data update data or delete data and it's what the user sees and that's called a front-end and what we have to do is we have to get out of our mind at the front end and the back end are the same right so what you're looking at here is not the back end okay the back end is this here and that's just basically a table right and this is actually this is actually a copy of the back end the true back end is simply a.
Table and I'll show you what that backend looks like let's go ahead and into our file folder here and we'll go ahead and browse into into and pull that up so we can see our specific folder that we're looking at and that would be here under the shared workbook okay so we have a we have a customer data this is our database so we've separated it and what does that look like it looks just like it looks just like a table right it's nothing more than a table and so the idea is here that we have to.
Separate the front and in the back end and the idea is that that back end everybody will we have to link to that back end so let's go ahead and close this all that this back end is is a simple table when we close that and when we go back into context and we add a new contact let's go ahead and add a new contact we'll call this person Jasmine Jasmine Smith let's see Smith okay and we give them a.
Address 1 2 3 4 5 Main Street ok and a city Los Angeles and now we want to save that right and we'll see it save down here but what we're really doing is I'm saving to the database and then I'm pulling it and I'm putting it back up here so if we go ahead and we pull that database up one more time it's going to the recent look into that customer and you'll see down here is our new so every.
New one gets added to this separate excel sheet and that's important because it is this database that the other users
Will link into and I'm going to show you how that is done of course and then what we want to do is we want to actually bring the data back in here so there's actually three tables there's three tables so let's go over those three tables one is the list now this is not your original data and the reason that this is not is because I want to give you the ability to sort to.Filter data and I want you to do that here right sometimes you know in my prior videos we we have one clicks or we can click on a heading and sort it we have the ability to filter so I want to give you that ability and we can do that if you look at my older videos you know one-click sorting of inline filtering and things like that when as long as this isn't the original data we can do that so I want to put the data separate I want to separate the data and this is your database so the idea is to separate the front-end in the backend we've done.
That here so that means when you sort it or you filter it we can have that the idea is that I want to keep this table hid probably hidden right and out of the way so the original data is never touched right because when we filter it we don't want that touch and we also I want to make sure that this is always synced up I want to make sure this is synced up with the database in our folder that is the folder that is that that is that folder okay so I want to make sure that this customer data is.
Always synced up and let's go ahead and delete this and I'm gonna create it again so let's go ahead and show you how that would look go ahead and delete that everywhere that's a new Dropbox feature so let's go ahead and create a copy this and I'll show you how that would work so what we'll do is we'll take this and then I'm gonna save it and then what I'll do is I'm going to also save it as so we're gonna create a new workbook multiple workbook and we'll save it at this just called shared workbook too and then we.
Want to differentiate between this one and the old one so let's just put a put it two here so we can clearly see which is which now let's go ahead and you know what let me freeze this column two because I want to be able to scroll up and so we can do that that's going to be very very helpful so let's go ahead and freeze that and now we can scroll up and let's go ahead and pull up the one the previous one which is you know without the to our original which is right here.
Okay so now we have to open and now let's go ahead and view them side by side so we'll go ahead and arrange them and let's call it vertically and we don't need this one open anymore let's close that and let's go ahead and review these again view them vertical okay okay so now we've got to open now we see there our original is here and our second one is here okay and to do that what we will be have already done and I'll show you how that was done is I need to link that I need to know where.
The database is for this right so we've browsed it and we know where it is and all we've done is selected here is the database okay so we know that's important and that's already done for both files so that is the only thing you would require your users to do is make sure you link up now if you have a larger program you will want to have a separate database for each and it's just because it's faster you don't need to in other words let's say you have contacts you have invoices you have accounting or you have I would put a separate excel.
File for each of them because the smaller that excel file is the quicker it opens the quicker it updates and the quicker it saves and you and speed is an issue here so I would have separate ones right and then you would just link them separately so let's go back into our original which is here workbook you know without the two let's go ahead and add a new contact and let's just call this person okay Smith we don't we can do.
Without the additional and we'll save it alright so now we've saved it in this one and we have the contact save message now I want to go back into this one and remember we have the database linked and let's look down here there's nothing here right jasmine Sabbath Kay Smith and this one right is 19 right so now what I want to do is I want to refresh the data and you know you can put this on a timer so I'm gonna click refresh contacts and now all we do is look down here and we see it's here so and this will of.
Course would happen on separate computers around the world right so the idea is that it just moved all the way
From here to there and now let's go ahead and update Kate Smith let's go update on this one okay let's pick two and let's update now let's say this user has added a toast let's say we have Los Angeles as a city right and then we click our update contact on workbook 2 now we move back to workbook 1 and we do the same thing and we refresh the data refresh context and we.Go back down right and here okay Smith Los Angeles so we scroll up here so it's you see how it works it works both ways now now picture this all around the world you have a program you build a program that acts as a powerful powerful database front-end and back-end and users can use your program around the world you could have 10 users in different cities and they could all be working on the same application at the same time well he's not really the same application it's a basically a duplicate.
Of it but it's not actually the same excel file now that is the difference with shared workbooks they're actually working on the same file in excel was never designed for that that is the reason there's so many issues with shared work words it was never designed with that and and even with all the limitations you get crashes you get Network failures you get there are so many issues with this and now the important thing is that ok if you look in this file I'm in a Dropbox ok that's important you want to be in a shared.
File so if you have a Dropbox and you have a shared folder it's critical you do need a shared folder of course we need to be able to share this data but what you can do here let's say you you don't want I have it in that let's go ahead and go back where we had the original folder which is here so you could only see this file this is the only file that needs to be shared we don't need all of these other files we only need to share the database so only.
The database files need to be in that shared folder and that shared folder should be shared with all the users I'm using Dropbox Google Drive would work too megha I have you know one drive could work as well as long as it's a shared folder among your users you can use it and and has to be sync in other words in other words when I make an update you know that that sync has to be automatic so it should become automatic and and it works really really fantastic let's go ahead and show you how we did that let's go ahead and close.
Out workbook 2 and we'll just work with one you've seen how it how it handles so now we can go ahead and get on to the hood and see how we actually have done this we have a two hidden columns as we usually do in most of the most of shared workbooks because that's where we keep our code from so let's go ahead and show you what we have there not too much just a few a few things so let's go ahead and go over that weird when we select a row.
We use code and we just as we've done nine many other videos which you can see where the selected row will always appear and be one so when we change that it's gonna go so that we use with our conditional formatting to highlight that and we've been over that many times before but let's go ahead and show you under the manage rules well we've got a lot of once we don't need here but basically just some old we can delete those we have just two really two rules here we've got the alternate rows which we use in the table and we have the highlighted row which basically.
Says you know if b1 is the row and that's the one we used so that is the one we that allows us to highlight the selected row we also have the selected contact ID and all this does is it can use put some code that tells us what ID what is the ID of the contact that we're currently working with this and this is gonna help us when we need to update that we'll need to know what row we have used a max to figure out what the next.
Contact ID and I'll go over that with you and we need to know what the row is of the current selected so if we update that if I update that I need to know what update remember this row below is not our database right our database has launched sheet 2 he so we need to know so for example if we're working with Freight Frank Fred erst I'd be number five it's telling us our database Row is row six six of the database so when we go into customer database we will see row six is Frank so.
That's the one we want that's the one we want to work with so that when we make when we make a change to that if we make the change to the notes some all change and we click update we need to know what row we updating that's gonna be row six when we click update we know automatically that that that that's going to be right here it's going to change right here and then what I want to do is I want to take all this data and I want to bring it back and put it right here right so then so basically.
This is simply a copy of the data right I want to do that as well so we're gonna go ahead and do that let's go into the VBA in a moment let me just show you real quick okay so I used match here to get that contact I use a match and all we did was match the contact ID now the contact ID is a named range in that when we look under the formulas go into the name manager we just have one named range and we use offset because this is.
Going to grow this is going to grow so we use offset it will count the number of cells with text and it will go so as we add as we add to this it will grow and grow and grow so the contact ID and the reason we also add one this starts on Row two so we want to know once we find once we've locate that which once we locate the ID right here we using this ID the selected ID we add one to that because we we start in row two.
Right our first contact starts in Row two so we want the row number so the first one is in row number two the second one is in row number three that is why when we when we use match we add one I mean use master we had one and we're just gonna wrap that in an affair if there's an heir to that formula for some reason it's not found we just want to make sure that we put an empty so that vba doesn't run into any issues we can just test if it's blank that's all we need to do if it's blank.
Then we know there's an error when it comes to VBA I also want to know if it's a new contact or not okay that's an important so when we click new contact this goes to true I want to know if it's a new contact because when I save it I need to know if I should have signed a new contact ID or not right so if it's a new contact right when we cancel that this goes back to false for example when we make a change test right I need to know we use the same button and the same macro for both update and save and I'll show you how we do that it's very cool.
But out so I need to know if it's on if it's an existing contact use D the contact database row in b9 if it's a new contact if this is true we need to we need to create a new row and I'll show you that how we did that in the code so when we update that on existing it's automatically brought in here so that is how we do that let's go in to the VBA and show you what we have there we'll go over quickly the contact macros because.
I really want to get into the sharing and that's the what that is the really the main focal point of this training but we'll go over quickly into the developers tab if you don't have a developers tab you can reach that from the file into the options and then just go ahead and click on the customize ribbon and you'll find the developers tab make sure that is selected alt f11 will get you into developers as well or Visual Basic by clicking here here is our VBA we have a few on sheet macros.
When we click on the Contacts we can see just a few we have a tab automated tab and we're not going to go into this because that is but if you want to see how we created these automatic tabs automatic tabs allows us to go from one to the other all right automatically that's a great feature I've already put that into training just look under Auto no autosave load tab Auto tab load save I'll find you the link it was under one of those and where we covered a lot of that so we're not going to cover that because we have so much to cover but.
Basically the idea is you go in here and you pull down and then you just put the range here whatever the range of the cells and the order and that's what your tab is going to be so it's very very simple and then we just turn it on and turn it off turning on the tab when the worksheet is active when we deactivate the worksheet we turn it off so that's all that that the tab is we only have a little bit of macros on the sheet itself basically when a user makes a selection.
Change to the dis where this is our main table inside that contact sheet and we're just gonna put the row b1 for our conditional formatting and then we're gonna load the contact we're gonna run a macro called load contact that is it for the on sheet macros let's go into the contact macros and I have all the cockers associated with contacts here we have new contact we have save update remember the same macro either saving a.
New contact or updating existing one we're using the same macro so that's a really great convenient we have to cancel new if they've if they've selected a new contact now they decide to cancel it right where that's a macro in itself but it's just a one line of macro we also have delete contact right we have load contact so load contact basically it loads the information when you select it loads all the contact information in these six cells above as well as the selected contact Ani so 7.
Min total we also have refresh contact table refresh contact table basically allows us to take information from the database from the database and bring it into the context so that's all that that macro does and what else do we have we have contact save message this is just a fade out message we've been through fade out messages before but basically when we update a contact and then we have a.
Message that will appear contact save you see that little fade-out message right there that's a fade-out message we there's a training on fade-out messages as well so you can go over that but it just lets us know that a context didn't save or less user note without one of those annoying pop-ups that a user has to press ok on that is all the macros you see we can do all that save new delete with just a little bit of code I've refined this code over the years onto just the minimum just what's needed we're gonna go over a little bit quickly.
On this because I really want to get to the sink data but I do want to go over that with you the new contact all we're doing is clearing out the existing fields right we need to clear it out if we're gonna put a new one in and let me pull that up for you if we want to want to clear that up we want to make sure we clear out the fields so we clear that out and then we also want to make sure we clear out the selected roads like so we just clear those clear those cells out using clear contents we want to change the button sets right if you'll notice there's a different button set.
This group this group this group right here new update and delete are all existing contacts we use those for existing contacts right but if we click new we use a different group we just have two buttons we have save and cancel so this is a new contact group new contact group right so I've given a name to this group of buttons and that's allows us to show and hide buttons very easily so new and council two groups five buttons total into two groups so.
Those two lines of code right here allows us to show and hide those accordingly so for new contact we are going to hide the existing group and we're going to show the new ones we're gonna set b10 remember b10 lets us know is it a new contact or not we want to set that to true I want to make sure that new contact so that we know whether it's a new contact and then f5 we're going to select and that just basically allows us to enter the name first it gives the user a little bit of.
Convenience so that when we were here and we want a new contact the first thing they do is enter the name so and it's required so we've selected f5 automatically to give these so the user just has to start typing in that is why we have decided select so that's it for new content very simple now the next is save and update context saving update so we're gonna use the same macro for both but we still have to make sure that we know whether worse we are it's a new contact or existing and we'll put a test on that in just a moment the first thing.
We want to make sure is f5 is empty I want to make sure that there's at least a name so if f5 which is the name if that's empty we're gonna throw up a message box saying please enter a contact name and then we're gonna exit ok we're gonna stop the calculations and if you remember in our old videos stopping the calculation helps us to read re make it faster all we're doing is taking turning off the calculations and turning off screen updating we just have to make sure before the macro ends.
That we reset the calculation which turns all the calculation back to automatic and turns on the screen updating so that's what those does so you'll see that often in my code stop calculation now we need to run our tests we have an if and if right here we run a test we need to know if this is a new contact if B 10 is true then it's a new contact so the contact data base row is going to be the first available row in our database the first available row which is right here so that we're gonna.
We're gonna run a test the last one the last row with the text is 20 and so the plus 1 is 21 so it's gonna tell us 21 is the first available row so our database row is 21 if it's new so we do that if it's new and also we need to know sheet a we need to know what the con the new ideas what is the new ID now we want a unique contact ID for everyone we need to make sure how can we assure that the next ID is new we can use the Macs and.