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

🎁 SAVE UP TO 40% IN JULY 2024 DURING OUR BIRTHDAY CELEBRATION ► rebrand.ly/7thAnniv_YTDesc
🔥 GET THIS + 350 OF MY BEST DONE-FOR-YOU TEMPLATES HERE ► rebrand.ly/350Wkbks_YTDesc
📚 GET A DISCOUNTED DEVELOPERS LIBRARY HERE ► rebrand.ly/UDVL_YTDesc
🤖BRING AI INTO EXCEL WITH THIS INCREDIBLE ADD-IN ► rebrand.ly/AIToolpack_YTDesc
👉CREATE & SELL EXCEL BASED SOFTWARE IN MY MENTORSHIP PROGRAM ► rebrand.ly/MentorshipProgram_...
▶️ GET ACCESS TO OVER 100 UPDATED TRAININGS, WORKBOOKS & PDF CODEBOOKS: rebrand.ly/Patreon_YTDesc
👨‍💼BECOME AN EXPERT AT DASHBOARDS ► rebrand.ly/ExcelDashboard_YTDesc
📣START LEARNING VBA HERE ► rebrand.ly/VBAEFFDan_YTDesc
🏝BECOME A SUCCESSFUL FREELANCER ► rebrand.ly/FreelancersAcademy...
⭐️JOIN KZbin MEMBERS FOR UNRELEASED TRAININGS ► rebrand.ly/YTMembership_YTDesc
In this week’s training, I will show you how to create Workbook Sharing & Sync.
⬇DOWNLOAD THIS WEEKS WORKBOOK FREE:
📩 USING YOUR EMAIL: bit.ly/ShareAndSync_Wb
🤑 START EARNING BIG $$$ WITH MY AFFILIATE PROGRAM 👉 rebrand.ly/Affiliate_YTDesc
EXCEL COURSES:
►FREELANCER ACADEMY: rebrand.ly/FreelancersAcademy...
►MENTORSHIP PROGRAM: rebrand.ly/MentorshipProgram_...
►DASHBOARD MASTERCLASS: rebrand.ly/ExcelDashboard_YTDesc
►BEGINNERS VBA COURSE: rebrand.ly/VBAEFFDan_YTDesc
►WEB AUTOMATION COURSE WITH EXCEL VBA: rebrand.ly/WebAuto_YTDesc
EXCEL PRODUCTS:
►Turbocharge Your App Development: rebrand.ly/UDVL_YTDesc
►Get 350 Templates In 1 Zip File: rebrand.ly/350Wkbks_YTDesc
►Get ChatGPT Into Excel With This Addin: rebrand.ly/AIToolpack_YTDesc
►The Ultimate Excel Resource Guide: rebrand.ly/UERG_YTDesc
►Get Your Actual Employee Hourly Cost: rebrand.ly/LBC_YTDesc
►1000 Incredible Freelancing Resources: rebrand.ly/UFRG_YTDesc
►Secure Your Excel Applications From Theft: rebrand.ly/XLSPadlock_YTDescrptn
🧐Find the Best Excel Products & Courses Here: rebrand.ly/ExcelProductsAndCo...
TIMESTAMPS:
0:00 - Introduction
0:37 - Overview
4:00 - Worksheet Design
24:42 - Click Cancel Button
27:33 - Exit Username
29:47 - Click Okay Button
36:00 - User Login
41:07 - User Logout
42:45 - Browse Shared Folder
46:29 - Change Event Worksheet
1:06:49 - Update File
JOIN OUR COMMUNITY:
►Facebook Group: rebrand.ly/EFF_Group
►Facebook Fan Page: rebrand.ly/EFF_FanPage
Follow me on my Social Media Accounts:
🤩TikTok: / excel4freelancers
🐦Twitter: / excel4freelance
🌏Website: www.ExcelForFreelancers.com
🔗LinkedIn: / excelforfreelancers
👤Facebook: / excelforfreelancers
📸 Instagram: / excelforfreelancers
🎥Rumble: rumble.com/c/ExcelForFreelancers
✉Telegram: t.me/ExcelForFreelancers
About Us:
I help Microsoft Excel enthusiasts turn their passion into profits so they can earn passive income using their skills without having to trade time for money.
I love making these videos for you each and every week. A great way of supporting Excel For Freelancers to keep this training free each and every week is by supporting us through one of the amazing products offered that will help you skyrocket your Excel Skills and reach your dreams.
Get Alerted IMMEDIATELY with new Free Training Videos & Workbook by subscribing here:
👉 / excelforfreelancers
New Training Video Uploaded every Tuesday!
Thank you very much for your continued support,
Randy Austin
#exceltraining #workbooksharing #workbookshareandsync #excelvba

[@ExcelForFreelancers]

🎉 7th Anniversary Sale: Save Big This July 2024! ► rebrand.ly/7thAnniv_YTPinnedCom

[@munendra1]

Randy.. dear.. you are simply a GEM for folks like us who are trying to lift our skills up onto an advance level. Thanks u once again. God bless you.

[@ExcelForFreelancers]

Thanks so much Munendra, I am so glad you love the training. Much appreciated.

[@nerenlakhraj5252]

Simply the best Excel Guru out there. Awesome video and presentation skills and not to mention a workbook to accompany it. Makes VBA worth learning to enhance your skills … thank you Randy. I am sure you all would agree 😊

[@ExcelForFreelancers]

Thank you so much Neren. I always appreciate you and you never miss a video. Thanks again, I am so happy to help.

[@radhikajuneja44]

Hello I can’t find the workbook .. Would be really helpful if you could share the link here 😀😀

[@winfriedwindegger5450]

Thanks Randy. A really amazing training. And a fantastic functionnality. So useful!

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Winfried :)

[@grahamparker7729]

Randy, another great tutorial! This is certainly one I will be using.

[@ExcelForFreelancers]

Thanks so much Graham, I am glad you liked it.

[@setyoufree2726]

I love your teaching method. Simple and to the point. also love your clean and powerful VBA code.

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that.

[@waynekaniewski2421]

This is brilliant. I learned several new things. Thank you!

[@ExcelForFreelancers]

Thank you very much. I do appreciate that.

[@SuperHanme]

This is and your other videos are fantastic and have taught me so much, thank you!

[@ExcelForFreelancers]

Great to hear and thanks so much Mark. Thank you for your Likes, Shares & Comments. It really helps.

[@Sebastien_Frederic]

Hi Randy, Words can't explain how great is this training ! You're Just RANDY, and Thank you ! May God Bless you more! #FromHaiti

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Sebastien.

[@watsnewin]

Awesome as always. Thank you very much

[@ExcelForFreelancers]

Thank you very much. I do appreciate that.

[@michaelsvenson2456]

Absolutley amazing, this is what we need in our Archery-Club, thank's. Thumbs up....

[@ExcelForFreelancers]

For sure, you are very welcome. I am happy to help and share. Thank you for your Likes, Shares & Comments. It really helps.

[@mauricelee2219]

Very nice video and thank you for sharing this to us..!!

[@ExcelForFreelancers]

For sure, you are very welcome Maurice, I am glad you liked it.

[@mattgeissman9475]

Hey ! Thanks so much for this - question though: I'm receiving a 1004 runtime error when running the logout sub - it's saying that the visible property of _worksheet can't be set. Any ideas?

[@ExcelForFreelancers]

Hi it would be hard to know but check the form name. Please take a screenshot of the bug and upload it and all of the details in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel Thanks so much

[@christianbirkmann547]

Very nice. Thank you for this great Training

[@ExcelForFreelancers]

For sure, you are very welcome Christian, I am glad you liked it.

[@user-zu5fe9yi2m]

Wow WOw WOw, Great and Usefull!!!! Big thanks

[@ExcelForFreelancers]

Glad it was helpful and thanks so much. :)

[@aliibrahem6313]

Hi Randy, how are you? I hope you are doing great... thanks to you I have applied you lesson and now I noticed when update data from other users, if the value is a date it is treated as a string, not a date.. Ps. I use UK date system.. and how can we apply run the change event when we delete a record? Thank you.

[@ExcelForFreelancers]

Hi Ali, you can format any cells in VBA with a date such as Sheet1.Range("A1").value = Format(Sheet1.Range("A1").value, "dd/mm/yyyy") I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel

[@amrutchitragar3014]

Wow your explanation style is amazing, amazing teaching style

[@ExcelForFreelancers]

Thanks so much Amrut, I am quite glad you liked it.

[@abdelhaq975]

you are an angel of knowledge

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that.

[@LearnYouAndMe]

One can also use Application.Split to parse string and store individual values in an array. From this array one can get individual values

[@ExcelForFreelancers]

Thanks so much. I use Split with arrays quite a bit in the recent videos including this one: kzbin.info/www/bejne/mZywdIiiZcaaiac I hope this helps and thanks so much.

[@FrankCampbell]

This is and your other videos are fantastic and have taught me so much, thank you! I was really hoping this video would share insights on how to sync Excel tables across multiple workbooks, which would include record changes, adding records to a table, and deleting records from a table. Is this even possible?

[@ExcelForFreelancers]

Hi Frank, Thanks so much. Yes it is certainly possible and mostly the same way that this workbook sync's data. Each of your other workbooks can look for text files in a specific folder and make those updates based on the text files. This way you can sync data across workbooks around the world using a shared folder.

[@moesadr3342]

As always AWSOME traing and a great subject. I wish you could also show us how we could have two additional columns to datestamp the created date and last update date, if ossible! Does anyone know what code can be added to what is noted in this training to do so? If note hopefully we see that in our own Rany's furture trainings. Thank again, I am delighted!

[@ExcelForFreelancers]

Hi Moe, thanks so much. Please feel free to download my template and customize it, however you like, including adding additional columns. If you need help with this, I have a group of 50,000 members who would love to help you here: bit.ly/groupexcel I hope this helps and thanks so much.

[@chrizki456]

Great tutorial! I just wanted to ask about the singular cell change before I commit to this. I have a macro that copies and paste multiple cell data to another sheet. Would this be possible if I left out that part of your tutorial? Thanks in advance!

[@ExcelForFreelancers]

Hi Chris, thanks for your method, You want to make sure that only single cell changes are done at a single time, so this method can work, not multiple cells or ranges. I hope this helps and thanks so much.

[@joseqduarte257]

Thank you very much SIR!

[@ExcelForFreelancers]

Most welcome Jose

[@raffaelerimorso9671]

tks for this lezion

[@ExcelForFreelancers]

For sure, you are very welcome

[@matthoyle951]

Really good vid, great info, and training. Are you able to do the same thing but without the Login Screen part? The sheet I'm trying to create is only going to be accessible by a few people within my team so a login isn't required. Is there code within the VBA that doesn't need to be there for the Sync? Also due to the nature of the business and personal data, I can't create it on OneDrive, Google Drive, or Dropbox. I take it I am still able to set this up on the companies shared network drive?

[@ExcelForFreelancers]

Hi you would not need the login, however you would need a cell to store their name so you know which user is witch. You can use the shared network drive as well. I hope this helps and thanks so much.

[@lavablock1999]

Hello Sir, I appreciate the knowledge you are putting out on the internet for others to learn from. I Copied the code that should generate the folders and text files exactly as you have it but when I run it, it doesn't seem to do anything. I have already checked to make sure the target range is working by having it write to a random cell. It's once I paste back in the code to generate the folder and text file in the directory, it just does nothing and throws no error. The directory folder right now is just a folder on my C drive. It is not a dropbox folder or anything else. Could this be the issue? I plan to have the directory to a folder on a local server that a few computers have access to instead of using a shared folder from dropbox or one drive. I would think it would create the text file and folder regardless of whether it's shared?

[@ExcelForFreelancers]

Hi , Thanks for your comment. Try using F8 to step through the code to see what the hang up is. You may have an incorrect file path. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.

[@aliibrahem6313]

Great, thank you so much... about "if(path, directory) " return runtime error 52 I guess, if the drive is incorrect. For example on one PC my shared folder was on f:, on another PC there where no drive f. So I had to use if err.number 52 ...gotto nodrive:... is it the right way to do it? Thank you so much.

[@ExcelForFreelancers]

HI Ali, that is a great point, you can add above that line On Error Goto NoDrive, then at the bottom of the macro you can write Exit Sub NoDrive: MsgBox "Please correct the incorrect folder path" End Sub This should work well.

[@neerajam6414]

@@ExcelForFreelancers I am getting runtime error 52 even though I shared the OneDrive folder with the person. Can you help me how to provide OneDrive access to the person

[@mansurahmad2699]

Thank you Very Much Sir

[@ExcelForFreelancers]

For sure, you are very welcome

[@gauravtanwar1984]

Very nice video n explanation Randy. Lightning fast sync compared to other methods. Thanks. I've a query... if any user changed same cell twice or more (let's say there was typo), how will we make sure that the latest change is updated last n saved...?

[@ExcelForFreelancers]

Hi Guarav, thanks so much. That is the beauty of this method, if a user changes the same cell twice, the text file is specifically named after the page and cell, so the new text file automatically replaces the old text file. This ensures only the latest change is used. I hope this helps.

[@arielgarcia4199]

i just liked it already. 😁

[@ExcelForFreelancers]

Thank you for your Likes, Shares & Comments. It really helps.

[@supamomo91]

Hello Randy! Thank you so much for this and I really appreciate all of your efforts! I have a userform that pushes data to more than one cell at the same time for each row. Is it still possible to sync this I were to remove the code that blocks editing of more than one cell at a time? Thank you!

[@ExcelForFreelancers]

Hi and thanks so much. Sure you can as long as you do it in individual lines of code so that its not in a range. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel

[@supamomo91]

@@ExcelForFreelancers Thank you!!!

[@pieterroobrouck8830]

Thank you so much for this great sync video. But if I am correct every user has to have his own copy of the workbook to be able to sync all the data?

[@ExcelForFreelancers]

Yes, correct Pieter, Every user needs to have their own, individual copy of the workbook, and only the data itself is synced between users. I hope this helps and thanks.

[@mengkeli1263]

Hello, Thank you so much for this video, it is amazing! I do have a question that is that possible to change multiple cells or add multiple records? we have massive data and sometime need apply functions to a whole column and in this situation, the code will not work......Really want to seek a way to get through this. Thank you very much again!!

[@ExcelForFreelancers]

Hi and thanks very much. You will want to make sure to use macros to loop through cells when you want to change more than one cell at a time. You would not want to apply functions to an entire column or row as this leads to a larger file size and slower workbook. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel

[@andrayfrancis5632]

Great training, thanks. will each user be able to see the contents of the Shared Folder and also subfolders created within the Shared Folder for all other users? If so, how can these be protected so that only the admin can delete or amend the contents within the Shared Folder?

[@ExcelForFreelancers]

Hi Andray, thanks very much for your comment. Since access to these folders is required, it would be difficult to restrict user access however each file only contains the value of the individual cell. If you have an application that requires very high security I do not recommend using this method to share and sync your macro-enabled Excel files. I hope this helps and thanks so much.

[@LizzieBywater]

Hi, thanks for the video I've been finding it really useful! I have an issue, though - the sub procedure to stop users editing more than one cell in Sheet 1 doesn't work. It doesn't work when I tried it myself and it doesn't work in your workbook I downloaded from the link in the video description. I can't figure out why? Any help would be much appreciated, thanks.

[@ExcelForFreelancers]

Hi Lizzie, thanks so much for your comment and feedback. Within the change event, before the database is updated you can add something like IF target.countlarge > 1 then msgBox "Please edit one cell at a time Exit Sub End If With that said, you never want to give a user access to the actual database. This way they have no control over how many cells are changed, They only have access to the front end (user forms) this way it is VBA that controls what cells are updated to the database and it is VBA that ensures that only one cell is changed at a time. I am showing you how to do this in my new series School Manager right here: Please check out our School Management series in which I show you how you can create your own Excel applications, from scratch, in an amazing series right here: bit.ly/SchoolManagerSeries

[@emilvichev5920]

Great!

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that.

[@gabswills7804]

Hi Randy, Could you tell me how to go around the fact that if someone sorts a column and then makes a change that the change won't appear in the right area for another user? Is this a functionality that can't be used with this type of macro/vba or would there be a way around this? Thanks in advance

[@ExcelForFreelancers]

Hi Gaby, thanks for your comment. You would want to have a main table of data in which no users have access to and which the order never changes. Then you have another sheet in which the data comes from. On this front-end sheet users can filter and sort without affecting the data or any other users. In other words, which shared workbook you want a Front End sheet, which is not synced with other users, and a back end, which is synced. i hope this helps and thanks.

[@pankajsurwadejmr]

THANK U SIR

[@ExcelForFreelancers]

For sure, you are very welcome I am glad you liked it.

[@jicbro35]

Hi sir, Good Day!! First of all i thank you for your great work. I just have small doubt that we can make the syncing automatically to the all users, bcz the data is overwriting when the multiple users adding the new datas if forget to click on sync button. It could be very good if the syncing is happening automatically. Expecting your valuable reply. Thanks once again

[@ExcelForFreelancers]

Hello Jic, thanks for your comment you can set up the syn macro to run as often as you want on a timer, such as 'Every 5 minutes, 10 minutes or whatever interval you like' using Application.OnTime function. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel

[@karisiajelle2752]

@@ExcelForFreelancers Hi Randy, thanks a lot for the video and the bundle of 200 excel files. I still have an additional question. When several users have to work on the same file at the same moment, and you setup to automatically synchronise every 5 minutes, do I understand correctly that each user in this case has his own work file with ".Naam" at the end of the file name? Or is this a misunderstanding and does everyone work from the same excel file at the same time? Is this possible with the passwords? Can the excel be a shared-file while you have different passwords with different rights on like you have in the 'user login security rights' excel? Thanks a lot!

[@thomaslau2362]

Hi Thanks for the amazing video, at 45:32. I am done with entering the macros for browsing. When i click browse, there is no file for me to select. Pls advice what is the possible reason.

[@ExcelForFreelancers]

Hi you would want to select a folder, not a file, that is why there are no files to view. We are only interested to select a specific folder in this part of the training. Thanks for the nice question Thomas.

[@W3DRM]

Really enjoyed your sharing video. However, it brings up several questions in my mind. First of all, what if another user "adds" a new row of data to a file or updates multiple cells? It seems that this particular code won't allow that. I'm also thinking of a situation where there are multiple users accessing the workbook/worksheet simultaneously but this code appears to handle only one user at a time.

[@ExcelForFreelancers]

Hello NVDon, thanks very much for your comments and feedback. Each user would have their own individual workbook, therefore only the data is changed back and forth between the workbooks. When adding or removing records we would use a Form such as my video on "Data Entry Form" so this way VBA can control the data being added, or new records being added at the bottom of a list. VBA and protection can keep rows from being inserted or deleted. (I did this in my Employee Manager application which does have the ability to add and remove records) The code that i added in this training also prevents users from making changes to multiple cells at the same time with application.undo I hope this helps and thanks so much.

[@pequadcob2009]

This is amazing and I have successfully replicated the syncing for my own tool for my company. How would you handle updating the excel structure (not just the cells)? Like if I add new sheets and what not, I am assuming you would send them a new version of the excel document. The issue I had with this was that I opened an older save and it synced. Then I realized and I opened the new file, but it didn't sync, since those files are deleted after they are synced.

[@ExcelForFreelancers]

Hi Caleb thanks very much. You would just send them the new file and in that new file would be an 'update from old version' button. When they click it, it prompts them to browse for their existing version which will then automatically update the data from the older version into the new version. I am teaching members how to do this in our Mentorship Program. www.MyExcelMentor.com Thank you for your Likes, Shares & Comments. It really helps.

[@pequadcob2009]

@@ExcelForFreelancers Awesome idea! I am fearful that some of my team will save another excel copy somewhere outside of the drive and will continue to access their own personal copy (which I could not technically edit). They'd still get the updates from the network drive, that is only cell data... It wouldn't update shapes and new tools I add... or new sheets that are added, correct? Or is there some way to to the same update thing for new sheets, new shapes and whatnot, just like you are doing with cell data? Thanks a billion!

[@CarlosAnibalEspinosa]

Excelemte, felicitaciones

[@ExcelForFreelancers]

Gracias. Thanks so much.

[@UlviAhmadov]

Great stuff! One question though... In case one user changes the same cell several times, it is important to have the sequence in right order, so when you sync, not end the wrong value in the cell.

[@ExcelForFreelancers]

Hi and thanks Ulivi, only the last, and most recent changed is saved. Because the name of the text file is the same as the sheet and cell, it will replace older changes automatically. I hope this helps and thanks so much.

[@cupidlover3017]

Dude you're a god.

[@ExcelForFreelancers]

Thank you so much. I am really glad you liked it.

[@carlossaavedra2950]

Randy a amazing piece of work. I’m a recent subscriber on both, your YT channel and your site so I’m checking on your older tutorials. In this particular one I have one question: This application assumes’ that every user will save his/her changes before closing the WB. That statement comes from the fact that if a user makes some changes but closes the WB without saving the other users will sync changes that are no longer valid. How can with address these sorts of scenarios? Tks in advanced. Regards…

[@ExcelForFreelancers]

Hi and thanks, its a good question with a simple answer Inside the workbook events, there is one event called "Before Closer" (or something like that) then you can add something like ThisWorkbook.Save I hope this helps and thanks so much.

[@GauthamKrishnaChannel]

Hi randy! I came here after your reference from other videos comment. I like this method but can this method be used on a workbook where the target range is the not a cell but multiple cells in a row at the same time? If I didn't include the target count > 1 statement will it work? Again I'll have to say thank you for these wonderful tutorials.

[@ExcelForFreelancers]

Hi Guatham, thanks for your comment. This will work on any range BUT, you must only change one cell at a time, so if you want to clear a row, run a loop from the first column to the last column, and clear out each individual cells You also want to organize your project like this 1. Userforms and Sheet forms (this is where users enter data, update and add records 2. Sorted & filtered lists. The data comes from the data sheets and can be sorted and filtered by each user 3. Database sheets. users do not have access to these sheets and no filtering or sorting is done. No deleting of rows and no changing of more than one cell at a time. If you follow the above structure you will have no issue on this. I am teaching all of this in my Mentorship Program here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/ Thank you for your Likes, Shares & Comments. It really helps.

[@darlaemerson2893]

Hi Randy. Thanks so much for your willingness to share your knowledge! I watched the video "The AMAZING TRICK To Add Different User Security Rights to ANY Excel Sheet and User Login" and created a spreadsheet that will only allow certain users to see certain tabs. However, it doesn't allow more than one user to be in the worksheet at a time which is how I discovered this video! Can I just add the field for syncing, Current User, and Admin on the Admin page and add the Macro for syncing or will I need to update in other places as well? Trying to figure out how to combine these two tutorials🙂. Thanks so much!

[@ExcelForFreelancers]

HI Darla, thanks for your comment. The user list (or database sheet) must also be synced so that new users will also have the information on their applications (although its hidden from them). I have an updated video on this here: kzbin.info/www/bejne/nJCUeaaIbphja6s I hope this helps and thanks so much.

[@fredheijmans1759]

Hi Randy, every time I am really surprised about your excel skills. I have one question. It is also possible to use instead of a “.txt” file a .xps file and put the information in the properties of that “.xps” file. Than it is not necessarily to open the file and only read the properties of the file and kill it afterwards. The question is what works faster if you have a lot of changes in the file?

[@ExcelForFreelancers]

Hi Fred, that is a good question. I have not tried this using an .xps file, but you can try both with about 100 different changes, and then time it to see which one is faster. Please let me know the results of your tests.

[@kleinboertjie]

Hi, just a thought, while using only commas to create the text in the .txt file, couldn’t you use the split function in vba to extract the values for the sheetname, cell addres and cell value?

[@ExcelForFreelancers]

Yes certainly you can do that. I just also like to use the Sheet name and cell in the text file name, this way if a user changes the same cell in the same sheet, the new one, replaces the old one. Thank you for your Likes, Shares & Comments. It really helps.

[@briansilvestri707]

Is it possible to bring over highlight changes, deleting of rows, adding of sheets...and I assume it isn't possible to use multiple changes and syncing?

[@ExcelForFreelancers]

Hi Brian, great question. The quick answer is 'No'. We should deleting rows in a shared environment creates a lot of issues, therefore, its best to not delete the row, but render it 'inactive'. For example if in your data you want to delete a customer, simply remove the customer name or ID, and replace it with Inactive. The idea that works best with this is to separate the 'shared data' with the 'visible user data'. With the visible user data we can then allow the user to filter, sort and also limit that data to only those active records. This will also not affect the other users, who can perform their own filters and sorts. When updating, deleting or adding records it is the user front end, and VBA, that controls this process, one cell at a time. This ensures proper updating of all users that are shared. I will be taking a deep dive into this in our Mentorship Program starting in just a few weeks in which we will be creating an entire Accounting Application which can be shared and synced as well. I hope you will join us for that. Make sure you get our emails in which you can sign up here: www.ExcelForFreelancers.com Thanks so much, Randy

[@Kim-kr6of]

Do you have a guide to how to make the excel sheet you show in the first minutes? - Really nice setup changing between ''sheets'' (KZbin, FB, websites etc.)

[@ExcelForFreelancers]

Hi Kim thanks so much for your comment. I do have a resource guide that may help you in which I have a list of Excel KZbin sites and Facebook groups along with Exce-related websites here: www.excelforfreelancers.com/product/the-ultimate-excel-resource-guide

[@sadiqbetto8798]

Hey, thanks a lot for this video, but i have a question please. How will the other users access the workbook on there desktop?

[@ExcelForFreelancers]

Hi Sadig, Each user will use their own workbook (separate copy). Only the data within those workbooks get shared and synced using a shared folder such as DropBox, OneDrive or Google Drive. Thanks so much.

[@twoheadlines]

Hi Randy. Everyday I work with your Excel tutorial videos is a great learning day. Thanks heaps. Occasionally though I encounter a problem which try as I might I am unable to resolve. My current issue is I am getting a run time error (76) which seems to be generated because a problem within my 2016 version of Excel "Path not found". What I am finding though is that despite this error, *.txt files are being written to C:\ drive, so I am presuming my coding is correct. I do not (will not) subscribe to any social media sites, such as Twitter, Facebook or LinkedIn. This is my choice, but I do respectfully ask if Excel for Freelancers has other, alternative forums through which I might be able to look for solutions. Thank you.

[@ExcelForFreelancers]

Hi and thanks Stephen, you may want to check the entire file path or perhaps characters within the file name that are not supported by your system. Our Facebook group is the only forum we currently have for support. Thanks very much.

[@MihiraStage]

Hi Randy, I am not a compute expert but am able to understand well and operate. So I tried to code everything fresh along with your tutorial, however I am missing the part for sharing and logout option. I am using google drive (shared within our office by all users) for "Shared Folder" location, but where should I place the excel files for other users to login? - Is it the same drive? If yes, then do I need to create duplicate files for every user and place it there with their personal names? When I run the "Logout" command it does make other sheets invisible, but when I open the excel, all sheets are still visible. I need that everytime a user log's in they have to put their credentials. Thanks in advance!

[@ExcelForFreelancers]

HI Amit, seems like you have set up the shared file location correctly Each user gets their OWN version of the Excel (this is not shared) so they can open it whenever they want. Only the data txt files based on the changes they make is shared. SO just give each user their own Excel application. This is the beauty of this feature. Thanks so much.

[@emilyranwang2754]

Hi Randy, thank you so much for your class, I have one question, after I sync, the numbers are updated, but the txt file in share folder was not deleted, but I used the code: Kill (LongFileName) FileName = Dir() 'Clear out Current File Name Loop Sheet1.Range("B10").Value = False 'Set Syncing to False Set Fso = Nothing Set oFile = Nothing End Sub Do you know what could caused it? Thank you

[@ExcelForFreelancers]

Hi and thanks that looks right. I have an updated training on share and sync: kzbin.info/www/bejne/nJCUeaaIbphja6s There is some updated code in the new training that will help you. I hope this helps and thanks so much.

[@sorolvision7986]

Nice video good video.

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Sorol

[@datebayo7961]

Hi Randy, Could this work with Sharepoint as shared folder? And instead of login/password to the duplicates, Sharepoint access security is used instead?

[@ExcelForFreelancers]

Hi it is possible however I have not tried it. Give it a try and let me know the results. Thanks so much.

[@sheikhified]

Thank you

[@ExcelForFreelancers]

You're welcome Hamza

[@allabouttruckin2635]

Thank you Mr. Randy it helped me allot. However i have 2 questions. 1. How can we make sync automatically. (No button). 2. I am trying to use this training for POS so in that it may change few columns. Thank you in advance

[@ExcelForFreelancers]

Hi Sourabh, you can set create a macro to run on schedule using Application.OnTime, also all of the applications, including the POS are open and you are welcome to customize them. Please keep in mind that making any changes require an advanced knowledge of VBA. Thank you for your Likes, Shares & Comments. It really helps.

[@wesleyholt8850]

Hey! great video! I was hoping I could get some help. my code is the same as yours,, i've checked it 10+ times to make sure I don't have any issues but even when a user has admin access, the admin sheet disappears completely. How do I fix this?

[@ExcelForFreelancers]

Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 30,000 Members who would love to help you with this. Thanks so much.

[@musawirsoomro9982]

Hey thanks for this i have followed every step and it works, Just have one Question, How can i automatically logout when i close the excel? I had to assign a separate button and click it every time. Is there any chance it does that automatically when i close the excel?

[@ExcelForFreelancers]

Hi Musawir, sure you can add the CloseWorkbook macro to the Worbkook_BeforeClose event, so this code runs automatically each time the workbook is closed. I hope this helps. Thanks so much.

[@suhanenteprises6819]

Very nice

[@ExcelForFreelancers]

Thank you very much. I do appreciate that.

[@anacrob]

Randy GREAT as usual, but unfortunately it is a bit more complex than that. As we discussed I am building a complete version. The strategy is similar but we could give user several possibilities: 1. Delete one line 2. Add a new line 3. What if 2 users make modifications on the same line during the same period? 4. What if 2 users add a new line at the same time? 5. Not a good solution to leave the user the Folder Creating routine handling 6. What about automatic syncing? As you built up in a different lesson, reading the file update time 7. What about the typical delay of Dropbox syncing (measured in about 1.3seconds, depending on connection speed) Once again a great work, an even better lesson, well explained, but not a usable solution Ciao, roberto

[@ExcelForFreelancers]

Thanks so much Roberto, Thanks for the feedback. Separating the user interface and the data table, and locking the data table from the user avoids all of the deleting/adding row issues, so that VBA has 100% control of the actual data. (As I did in the Employee Manager Application) Therefore Rows are never deleted and new rows are always added at the bottom of the table. Users who make changes to the same cell, will have the most recent change take precedence. Syncing can be set at any interval or change, depending upon the developer's or admin's preference. (In the Employee Manager users have the ability to set the # of minutes /hours in which syncing will take place. Dropbox has a delay but its small. While this is not a prefect solution for all. I believe it is a great solution for many. Thanks so much for your feedback.

[@emilyranwang2754]

Hi Randy, I have successfully used your VBA code combined with login form with different user. It works very well by now. Thank you very much! But now there are new requests from users. As some of them were linked with other excel file, so they don't really update in their file but just to update the working files. But VBA can't recognize the change they made. Do you have any idea how to solve the problem? Thank you

[@ExcelForFreelancers]

HI and thanks, you want t make sure each user gets the updated file and uses the updates file (each user has their own copy of the file) I go into more detail in this updated video here: kzbin.info/www/bejne/nJCUeaaIbphja6s I hope this helps and thanks so much.

[@billgillock]

He's a genius

[@ExcelForFreelancers]

Thanks so much Bill. I really do appreciate that.

[@TheHansnobody]

This training is really helpful. Thank you! I have a question tho. How do you setup your Dropbox/ One drive etc? Bec on my end its working perfectly, but my users are encountering runtime error 53 file not found. Thanks in advance. More power to you. 😊

[@ExcelForFreelancers]

Hi and thanks for your comment. You will want to make sure each user has downloaded and installed the dropbox software. Each user must share the same folder in their Dropbox and also each user must browse for the location of the shared folder within Excel. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel

[@TheHansnobody]

@@ExcelForFreelancers thanks a lot. i have another question if you don't mind. Have you encountered a problem in syncing if the user is in a different time zone?

[@ExcelForFreelancers]

HI Hans this should not be an issue at all since all syncing via Dropbox or any other shared folder, is done in real time, regardless of time zone.

[@abigailbubbles7709]

Hi Randy, Thank you so much for this! Question: Would this prevent another Macro from being able to preform in the same section? I am trying to have a Macro automatically add a new row, a date, and remove the last date and it's only adding a new row now. Thanks!

[@ExcelForFreelancers]

Hi Abigail, thanks for your comment. There should be no problem with that as long as you are using the first available row at the bottom of the table and only making changes to 1 cell at a time. Make sure you separate the front end (what the user sees) to the back end (database table). This way VBA controls the data going back and forth and the user never has access to the database table. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel

[@ExcelForFreelancers]

I will also be adding an updated 'Share & Sync' feature to our School Management series in which I show you how you can create your own Excel applications, from scratch, in an amazing series right here: bit.ly/SchoolManagerSeries

[@billgillock]

Randy. I'm having trouble getting the sync to work when i add a row of data with data mapping. the sync works fine otherwise when i edit an individual cell. is this the right program to use for syncing a row of data or should i be using your "Sharing Workbooks" method (syncing the entire worksheet) ?

[@ExcelForFreelancers]

Hi Bill thanks for your message. When adding a new row, make sure the row is not inserted, but added at the bottom. Make sure that when you add new data to that cell, it is cone cell by cell (as opposed to copy and paste) and also make sure Events are Enabled, because each change to an event will cause the macro to run that saves that cell change to the dropbox folder. This method, cell by cell, I like better than the original of syncing the entire sheet, as its just a lot faster. The only thing you have to make sure of is there changes are to one cell at one time, always. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel

[@billgillock]

Thank you Randy ! That was the problem. I had the Code Reset Macro turned on which disabled the Events. Now everything works like a charm. You are a genius !

[@ericyanez5463]

Hey! I have multiple Excel with macros and the result of all I put it in another excel file as a comparative chart. Is it possible to make a 1-form that gives the information to my another excel files and these results copy them in another chart?

[@ExcelForFreelancers]

Hi Eric, thanks for your comment. You can link cells or tables from one workbook to another as long as the file path does not change, however try if you can to keep all data in a single workbook which will help for speed. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel

[@jufailitech]

Very nice tutorial Randy I have created a simple macro enabled workbook with a very simple user form to allow multiple users over the network to register their names. When I share the workbook the macro does not capture the name. How can I allow multiple users to register without sharing. I don’t need the login feature, just for them to open the workbook then register their names?!!

[@ExcelForFreelancers]

Hi Mahfoodh, thanks for your message. While you do not need the Login feature, you will want to know the user that opened the workbook and is using the workbook. It is this username in which the folders are created. You could use a simple drop down list in a pop-up form that allows them to select their username. Each workbook, when opened, must have a separate username and you also want to make sure that a user does not use two different usernames. Folders are created based on these usernames. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel

[@mzsh1801]

Hi Randy İt.s good job İ have made the same steps but i faced error run time 75 Path /file access error Also if i need some lines to be added by each user. İs it possible

[@ExcelForFreelancers]

Hi you would want to make sure the file path is correct per user. You can have users add lines to the database, as long as they are adding them cell by cell. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel

[@lazarlazarevic5396]

Great stuff. Can this kind of syncing work with API connections in Power Query?

[@ExcelForFreelancers]

Hi Lazar, perhaps however I have not tried it in Power Query. Can you post this question in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 30,000 Members who would love to help you with this. Thanks so much.

[@abdulqadirsyed]

Hello Randy, your video is fantastic as usual. odd thing happens is as soon as i change selection of the sheet it gives error: the cell or chart you're trying to change is on a protected sheet. To make a change,unprotect the sheet..... after this the code stops working.... kindly do guide

[@ExcelForFreelancers]

Hi and thanks if you are trying to make changes to a protected cell on a protected sheet, this error will appear. Please make sure to unprotect the sheet or the cell that is creating the issue. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel

[@jamcast725]

Is it possible to make it work to so that it can changes on an entire range of rows? Basically, i want to incorporate this with a ticketing like tool where the user can input on multiple fields then the changes apply to the corresponding ticket row. Many thanks

[@ExcelForFreelancers]

Hi Joseph, For sure it can work but you will want to change only one cell at a time. I just sent you an email detailing how to design this type of sharing & sync application. I hope this helps and thanks.

[@supamomo91]

Hello Randy, I'm facing this similar situation too. Are you able to send me an email to solve this as well? I would really appreciate it!

[@clarkpapke877]

Great Training! I've run into a problem though, when the line in VBA, "Application.Undo" executes it crashes excel. If the line is commented out everything works fine. Any ideas?

[@ExcelForFreelancers]

HI thanks if you have a large data set, undo may cause a crash if you have limited memory. I suggest remove that line of code. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel

[@jimbo9247]

Randy... As always, excellent tutorial. Clark, I don't know if you saw the same thing I did at 48:11 into the video, which showed 'Application.ScreenUpdating'; But, I resolved my issue by using 'Application.EnableEvents' around the undo instead. Otherwise, it seems Excel gets stuck in a continuous loop thus causing a memory issue.

[@andrewmishler9139]

Works Awesome, one question I have is, is there a way to use auto sort? without that it is hard to find a contact out of 500 contacts

[@ExcelForFreelancers]

HI and thanks so much. I have a great solution for that using auto-complete (that was the first video I did on this channel) here: kzbin.info/www/bejne/hoeoq3uYma2ZZ7s I hope this helps and thanks so much.

[@jnvc9121]

Hi, 1)is there a tutorial on how to add a "register" form, in which the other users of the shared workbook can set or change their password or other profile details, but of course only the admin can change the sheets they can access, 2) is the admin sheet located at the back end file together with the actual data? If so, the admin sheet also needs to be sync?

[@ExcelForFreelancers]

Hi Jay, thank you for the comment. The Admin should set the passwords, then hide the file, After passwords are set this sheet should be hidden for those without admin rights. These passwords MUST be synced, so that an admin can change a users password from the admin machine, and then this password is synced with the users machine, so that the next time the user logs in, the new password is required, however the user has no access to this sheet since it is xlveryhidden within VBA. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel

[@markweldon8898]

Hey. Fantastic tutorial. Hit a bit of a problem and I cannot work out why as it was working perfectly well a few weeks ago? I seem to be getting a run time error 1004 application defined or object defined error on this piece of code. This Workbook.Sheets(SheetName).Range(CellAddress).Value=CellText For the life of me I cannot work out why?

[@ExcelForFreelancers]

Hi and thanks for your comment. When the bug appears hover over the SheetName, CellAddress and CellText and look for errors or issues. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel

[@jfourie43]

Brilliant!!! How can I insert/delete rows (not working with Workbook_Change - Application.Undo)? Is there a way around Data Sorting/ Filtering while logged in with other users or how does it work or work around it?

[@ExcelForFreelancers]

Hi and thanks very much Jean. For workbook sharing functionality, you would never want to delete rows and new rows must always be added at the end of the table. If you want to clear a row out, you would keep the first column data (usually an ID #) and then loop through each column in that row (say 2 to 15) by clearing out each cell individually. This will trigger the change event and also send that change (cleared cell) to all other users. Also keep in mind that on a database original data you never want to change more than 1 cell at a time. When it comes to sorting, you can do this with a list. A list is separate than the database whereas users can sort and filter lists which is completely independent from the original data. Users should never see or have access to the original data. I cover this extensively in my Mentorship program here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/ I hope this helps and thanks so much.

[@jfourie43]

@@ExcelForFreelancers Hi, It helps a lot, thank you. I bought your 200 workbooks and very nice to combine some of them, thanks so much for everything.

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Jean Fourie

[@bebecons.r.o.8081]

Hi Randy, thank you very much for this video. I have a question please. It works amaing. But for Line Input I have found ths information: "The Line Input # statement reads from a file one character at a time until it encounters a carriage return (Chr(13)) or carriage return-linefeed (Chr(13) + Chr(10)) sequence. Carriage return-linefeed sequences are skipped rather than appended to the character string." I have a workbook, where are also a long texts, for example email text and there are also these haracters. So the code read every time only the line, but when are there end of sentence, (Chr(13) + Chr(10)), it doesn´t work. It will update only the one row. Does exist any other Input, that can read also these characters? My text example is: "Fressen des Gewindes, oder "festbacken" der Schraubverbindung. Unter hoher Temperatur nicht ausreichend Molykorte. Gewindebschädigung beim Eindrehen. etc. " If I make it with your Sync function, it will appear by the second user only as ""Fressen des Gewindes, oder "festbacken" der Schraubverbindung. " It means only with first row. Do you have any idea how to do it? Thank you very much, Barbora

[@ExcelForFreelancers]

HI and thanks for the great question. What you can do is replace the carriage return character with a special character Before it is written to the text file such as ^. Then when the file is read from the txt file all of the ^ characters are replaced with the carriage return characters, returning the text back to its original state. I hope this helps and thanks so much.

[@bebecons.r.o.8081]

@@ExcelForFreelancers Thank you very much! It is great and helped me. Barbora

[@moriomakter3153]

Good video

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Moriom

[@rambler3059]

Hi Randy did you activate the macro that on close and then reopening of worksheet brings you only to the login page?

[@ExcelForFreelancers]

HI Rambler. I created a macro I believe on this one but I don't think I activated it. I did not want people to have to login when opening the workbook. That is something that should be activated when your workbook is ready to be released. I hope this helps.

[@rambler3059]

@@ExcelForFreelancers Yes seen it and put it in the right place was saying this for those that did notice it

[@ExcelForFreelancers]

Ok great, thank you so much :)

[@alexandrecote-lesage2503]

Hi. Can you specify how to activate the macro that brings you only to the login page at start? Thank you very much

[@eirabriennestark2166]

hello randy, your work is great! This is really helpful for me. However, I tried to run the excel file on Excel 2003 Version and its not allowing me to Log In even i put the correct username and password. But when I try to use this on Excel 2016 and 2007 the problem doesnt exist. Please help

[@ExcelForFreelancers]

Hi and thanks. I have not tested this on 2003, so i would suggest using it on 2007 or later. I hope this helps and thanks so much.

[@MihiraStage]

Hi Randy, 2 things... firstly a folder in my name is also created in the first instance itself, which means something is wrong somewhere. Secondly, when I click sync, it takes me to vb and highlights the line ---------Open LongFileName For Input As #1------ Please advise... Thanks

[@ExcelForFreelancers]

Hi it would be difficult to know the issues without seeing your code and workbook. Can you please upload screenshots of your code along with a detailed description in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 20,000 members who can help you with this. Thanks so much.

[@trentforsyth943]

Hi Randy, Amazing instructional video thank you! One questions, how do I enable the Workbook_SheetChange code to work across all my sheets except for 1, without having to paste this code into every sheet except for 1 in the workbook?

[@ExcelForFreelancers]

Hi Trent, thanks for your comment, there is no global worksheet change that I am aware of that would affect every page. I hope this helps and thanks so much.

[@trentforsyth943]

@@ExcelForFreelancers thanks Randy, am I understanding correctly then that I should copy and paste this into every sheet I want it to work on within my workbook? Thanks

[@ExcelForFreelancers]

Yes that is correct Trent. Thanks so much

[@akkintouch]

Dear Randy, is it possible to sync the formatting as well as formulas in the required range of cells??

[@ExcelForFreelancers]

Yes certainly. I am doing this exact thing in the application I am building in the Mentorship Program. Please join us here: bit.ly/MentorProgram_YTComment

[@akkintouch]

thanks Randy.. I tried this method, but the sync stops in the middle as it exits out. Don't know what the problem is. The txt files which are created are still inside each folder. Sometimes it works well.. no issues I have used sync on workbook open, workbook activate, worksheet activate, worksheet selectionchange. Does this have any issues? since I have used sync when I activate a worksheet the sync runs again and over writes the same value as the txt files are not deleted completely. I don't know why the txt files are not killed. Could you give me ur thoughts on this please?

[@omaraissa318]

If we transfer SynText to variant Array with the command like Arr =Split(SynText,","), we can easily Extract SheetNAme ,Target and Value

[@ExcelForFreelancers]

Thank you Omar. I did use this exact technique in other videos and I like it a lot. In the future I will be using it more as it uses less lines of code and is easier to understand. Much appreciated.

[@aldzreneparpow453]

Great work. so useful. can you teach me how to create if someone approves payment voucher and its appear login id and password. then after they login it appear their name . thanks in advance

[@ExcelForFreelancers]

Hi and thanks very much. This training may help you: kzbin.info/www/bejne/ZpLLiqKvarFmm8U Thank you for your Likes, Shares & Comments. It really helps.

[@besybesho6829]

hello Randy thank u for the training . i try to get this workbook and its not sending to my gmail. can u help me please?

[@ExcelForFreelancers]

Hi If you have tried to download this workbook using the links but had a problem please email [e-mail] . Please supply the email you used along with the workbook you are requesting. Thanks for your patience. Also please check out my newest Sharing & Sync add-in coming this Tuesday here: kzbin.info/www/bejne/fWq5onltrrNjhdE

[@mrinal106l]

Thanks again for the priceless knowledge sharing video. I would like to know how we can create username folder in google drive instead of shared folder. So that the sheet sync with Google drive instead of shared folder.Pls suggest. Thanks .

[@ExcelForFreelancers]

Hi and thanks Mrinal, you can download the free Google Backup & Sync application which will sync a folder on your computer with Google Drive. Any new folder that you create inside this 'Backup & Sync' folder will also automatically be created on Google Drive as well. I hope this helps and thanks.

[@mrinal106l]

@@ExcelForFreelancers Thanks for your response. Few companies doesn't allow to install any kind of application apart from excel . In that case is there any other way to do it. I have checked in Internet about the backup and sync add in but IT team doesn't allow to install. Kindly suggest.

[@ExcelForFreelancers]

Get your IT department to allow one of the many applications.

[@Kumar-nf6fj]

@@mrinal106l l

[@Caseasaurs698]

Hello there, I have copied and practically memorized this video, but I have a complex worksheet that I would like to add these username and password features along with syncing to. Would you be able to look at my file and see if its possible??

[@ExcelForFreelancers]

HI Steve, thanks for the information. There is no doubt we can help you with that. While I am unable to take on any projects I would be happy to refer this out to a qualified developer. Can you please email me all of your specifications in a detailed scope and I can forward it to a developer? [e-mail] Thanks so much.

[@nguyenngocvu9451]

Hello Can I ask? I have an excel file with vba, and I want to share it with many people for common use. For example, when this person logs in and edits, the file saved on the computer or drive will be updated. Thank you very much

[@ExcelForFreelancers]

Hi and thanks so much. Yes sure, you can do that. I have an updated video on that. The files are not shared however the data is. To share and sync your macro-enabled Excel workbook, please watch this training: kzbin.info/www/bejne/nJCUeaaIbphja6s

[@michaelfleming8454]

Hi Randy, I've learnt a lot from your videos, thanks heaps. I am having trouble with the Share and Sync workbook though. I am using a Google Drive shared folder for the sync files location, but there seems to be quite a delay from when one user updates a cell and the .txt file is generated for other users, to when the other users can see the .txt files. So the problem exists when two users make a change to the same cell within approx 10 seconds of each other. The time gap means that one user get left with outdated data. Hard to explain, but here goes: User1 updates $A$1 and User2 also updates $A$1 lets say 5 seconds later. User1's change is recorded in all users folders in the shared folder (Google Drive). but User2 makes a change also but hasn't yet received the change from User1 (due to Google Drive taking time to sync), so User2 only receives User1's change after his own, meaning that his data is now updated to User1's change (not the latest data). All other users (including User1) receive the change from User2 and are up-to-date. User2 has incorrect data. Any ideas would be greatly appreciated. Thanks, Michael F

[@ExcelForFreelancers]

Hi Michael, thanks for your question and I am happy to help. A few suggestions 1) Use Dropbox as it is lightning faster than Google Drive 2) Make sure you have viewed my newest training on Share & Sync as it fixes a lot of smaller issues here: kzbin.info/www/bejne/nJCUeaaIbphja6s 3) Since the last change always wins, before a user makes any kind of a change to a database, any changes, from other users are checked first (assuming their folder is updated) and if two users make a change to the same cell, the 2nd change will always replace the first. I hope this helps and thanks so much.

[@navwelchh]

Hi Randy - Thank you for your video, I find it very easy to create the macro using it. But I understand that the assumption is that the users work on the same page (tab). Can we have a situation where people use different tabs each for their own and not be able to see others' tab. So if a spreadsheet has John, Peter tabs. John can only see his tab and not Peter's. And vice versa. Thanks for your help!

[@ExcelForFreelancers]

HI Naveen thanks so much for your comment. Yes you can set up individual security on a per-user basis. I show you how to do that in this training: kzbin.info/www/bejne/ZpLLiqKvarFmm8U I hope this helps and thanks so much.

[@navwelchh]

@@ExcelForFreelancers Hi Randy, Thank you for your reply. In fact I used this training first! :).. But the thing is my excel file is on Onedrive. When I used the macro, the spreadsheet closes the already open tab. So if person A is already using a tab and if person B logs in, the tab for person A closes/hides and the tab for person B opens up. Is it possible that person A remains uninterrupted when person B opens the file. Thanks for your help!

[@ExcelForFreelancers]

Hi Naveen, each person must have their own, unique Excel application. This application does not have to be on onedrive. However the data that is stored in the application is shared using OneDrive, Google Drive or Dropbox. I show you how to do that here: kzbin.info/www/bejne/l3y5Zqabj9KceNU

[@navwelchh]

@@ExcelForFreelancers Thank you Randy! I will use this video to set up the macro

[@navaraklongsilpachai5582]

Hi Naveen, I'm facing the same issue. Can you resolve this?

[@woss2010]

I have a question that visual basic can add it same on Microsoft Word

[@ExcelForFreelancers]

HI woss, there is some integration with Word, as Word also has visual basic for some automation. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel

[@eziakaun]

Hye RANDY, at 1:00:00, that is for single change, what if we want to change multiple cell at the same time?like we add data in multiple row or column, so the data will add in the txt. Can we do that? TQ

[@ExcelForFreelancers]

Hi thanks Muhammad. When data needs to be shared and synced you must change only one cell at a time. For multiple cells, just run a loop through all of the cells you want changed, just as I do in data mapping. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel

[@eziakaun]

@@ExcelForFreelancers thank you randy, you the best.

[@eziakaun]

@@ExcelForFreelancers i have some request, can you make tutorial exporting/importing data from excel to online (google sheet, one drive etc), and make it live update. TQ

[@ExcelForFreelancers]

Hi Muhammad, I have done that already, so please search my channel for "Google" and you will find some great training videos. Thank you.

[@atiqsarwar4222]

Hi dear nice training video, I have one question, if two or users changed the same cell value what will happen?

[@ExcelForFreelancers]

Hi Atig, thanks very much for your question. If two users change the same cell, the newest change will be the one that is saved. Thanks so much.

[@atiqsarwar4222]

Thanks for much for reply, is this program convenient for multi user feeding?

[@atiqsarwar4222]

I like develop a program which is convenient for multi user feeding at same time, just like fee voucher feeding in software s

[@caseyhess8287]

Hello Randy, This is great stuff. I had a question of how to do this for a notes with extensive details and number of characters. This code works. However when the other user logs in, it wont sync past the first line in the text file. Any suggestions? Thank you again for all your great work and offering amazing content

[@ExcelForFreelancers]

Hi and thanks so much. Please check out an updated version of sharing and sync that is simpler and more effective here: To share and sync your macro-enabled Excel workbook, please watch this training: kzbin.info/www/bejne/nJCUeaaIbphja6s I hope this helps and thanks so much.

[@caseyhess8287]

@@ExcelForFreelancers I watched both of these and it has taken me to this area of need. The issue I am having is, once I run the macro, it does creates the .txt file as shown in your instruction. However, if I have Multiline and Enterkeyboard set to true used on my textbox in my user form, it will create the text file with everything I typed, but only upload the data before the line break, note everything after the semi colon. Hope this makes sense and again thank you for all the insightful content you are providing.

[@ExcelForFreelancers]

Hi Casey, you can loop through all of the lines in a text file using the code found here: stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba I hope this helps and thanks so much.

[@Kamy333]

Hi, Thank you for all your videos and your enthusiasm. I learn a lot. If I may suggest at around 1h20 one thing to make it easier was to use an array when you extract the data from text File separated by comma or any separator Dim SyncText As String, ArrText() As String, SheetName As String, CellAddress As String, CellText As String SyncText = "Contacts,$G$18,Woodlands Hills" 'comma separated or any separator ArrText = Split(SyncText, ",") SheetName = ArrText(0) CellAddress = ArrText(1) CellText = ArrText(2) Do you agree? Take care

[@ExcelForFreelancers]

Hi Kamy, I absolutely agree. When i made this video I had not used arrays so much, but now i use them so much, as you can see in my new series Please check out our School Management series in which I show you how you can create your own Excel applications, from scratch, in an amazing series right here: bit.ly/SchoolManagerSeries Also what I decided to do was to have the file name to contain the sheet name and the cell address and to make the contents of the text file only that that contains the cell data. I hope this helps and thanks so much for your feedback and suggestions.

[@Kamy333]

@@ExcelForFreelancers Thank you. I really appreciate you taking the time to answer everyone. and I did watch a few of your videos and enjoyed them a lot.

[@ExcelForFreelancers]

Thats great to hear. Thanks so much