What are my options on multiple users accessing and editing a MS Excel 2010 workbook on a network?

I am not an expert but I have self taught myself some VBA.

I work in a paper based office and I am currently making a large MS Excel workbook, with 5 sheets, to use as a control point for the paper files. This means all the data also has a diary date attached and the data is edited regularly (such as when work is complete/diary to check the file needs changing etc).

I have used VBA and an auto_close macro so the workbook data re-orders itself across all sheets via the date it needs to be worked and then saves, so that the next person in the document can work the next task in date order - kind of like a diary system. I'm also considering (probably ambitiously)  if I can use VBA to link up the diary dates on the workbook with outlook to save time.

I want to role this out to an office of 20 and have considered sharing the document for "collaboration" but this seems like a gateway to hell when I have read up on it. I have also considered SharePoint, but trying to convince my IT department to shell are for this is unlikely. I have also considered MS Access but I think I would have the same issue. An Intranet option seems the most likely work-around from what my un-IT-educated internet trawling brings up.

Does anyone have any suggestions so that each user can edit the document without others being locked in it whilst the file retains the macro function?

Thank-you for any advice.
Kane HellewellAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnBusiness Consultant (Owner)Commented:
Please give some consideration to using Office 2016 for this task. Microsoft has added collaboration to Word and Excel (and possibly others).

I am very new to Office 2016 (it is new), but collaboration (more than one person having the same document open) is a feature of new the new Office suite.
Kane HellewellAuthor Commented:
Thanks John,

I'll take a closer look at 2016, but ultimately, it will be down to my IT department as to whether this is possible or not. I was hoping I could find a work-around without asking my business systems dept. to update the companies version of MS Office.
JohnBusiness Consultant (Owner)Commented:
Please let us know. Office 2016 is a good match with Windows 10, which is why I suggested it. Office 2010 wants to keep the document locked (I have Office 2010 at clients).
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I have also considered MS Access but I think I would have the same issue
So that you're aware, if you decide to move this to Access, you need a single Full version of Access for your development, but your users only need the free Access Runtime. You can develop the application and deploy it to your users, and they do NOT need anything other than the Runtime to run it. Access can handle multiple users (if configured and deployed correctly), and is generally a better choice than Excel if you must support multiple users.
Fred MarshallPrincipalCommented:
My experience is quite dated on this subject.  Nonetheless, in the past we wanted to have http-based interfacing for the users.  That gets away from running app's on all the computers.  But, right now, I can't tell you how to implement it.    

Long ago we used PageMaker which made publishing a database via http quite easy.
At that time, Microsoft required a Server to support Access to do the same sort of thing.
My sense is that the world has moved on.....

Anyway, you might ask and consider if an http option isn't something worth considering?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andy CownieImplementation SpecialistCommented:
It sounds like a split Access Database would do the trick. This is what I have done recently and works very well, with no sharing issues unless 2 people try to update the same record in the database at the same time, which can also be avoided.

Basically you have one backend Access database which stores all the data, and each user has a frontend file that links to the backend in realtime and updates with any changes they make. It's not at all difficult to implement and once you create the database, access has a wizard to split it for you.

Also I found that having a form show only the relevant data on the screen at one time made it much easier for the end users who are not at all tech-savvy. excel confused them to no end. We have 60 people accessing it at a time with no issues.
Kane HellewellAuthor Commented:
All good ideas for me to explore, thank-you very much :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.