Avatar of Kane Hellewell
Kane Hellewell
 asked on

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.
Microsoft ExcelMicrosoft AccessDocument ManagementVisual Basic ClassicVBA

Avatar of undefined
Last Comment
Kane Hellewell

8/22/2022 - Mon
John

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 Hellewell

ASKER
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.
John

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).
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
Scott McDaniel (EE MVE )

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
hypercube

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Andy Cownie

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Kane Hellewell

ASKER
All good ideas for me to explore, thank-you very much :)