Help with multiple users on XSLM file

[PivotMeThis]

This file is updated with new data once a week (by me) It is also updated by 6 people at least once a week. It is edited once a week by another individual.

How it works. I have a worksheet which comes from one of our systems. I run it once a week and copy this data over the existing data in my hidden worksheet. The next worksheet (ProjectData) is filled with formulas to gather specific project/contract information from the hidden worksheet and other formulas which calculate contract costs. The hidden worksheet contains about 1000 rows, the ProjectData sheet only contains 85 projects. These are projects which may have significant impacts to traffic and this information is being used on a website. There are multiple fields which must be updated each week by the 6 people mentioned above - this is information which does not reside in our databases. Other fields on this sheet are locked so they cannot be changed. And the worksheet is protected.

This file contains VBA and a couple of buttons for the editor. When text is changed the font turns blue so she can see what has changed. Once she's done editing she pushes a hidden button which turns the font back to black. Then there is another button that she pushes to copy specific rows of this data to another tab (MAP). This worksheet is used by our GIS team and used to populate the map that is on the website. This information is also being used by other offices for other performance measures and tracking information. Other code ensures that the file always opens to the ProjectData tab so that wrong worksheet is not inadvertently changed.

That being said, the 6 people in the field are complaining that they can't get in to do their work when they want to because one of the other 6 people are using it. I have been looking for a way to make this available but have run into trouble trying to use the "share workbook" function because of my macros. I also looked at SharePoint but found it would not work. I thought about Access and wondered if there was a way to link this information to a database with forms that could be filled out but have very limited Access experience and it didn't look like it would refresh the spreadsheet anyway.

I wonder if there are other options available to make it easier for the staff to use this file. I appreciate any help and/or advise you can give me.

Thanks

 

[Smitty]

Your sharing options are going to be relatively limited with a VBA enabled workbook, so Access might be a good choice. It's pretty easy to import data to a new table, then use the Form Wizard to create a bound input form for it.

And you can easily push/pull data to/from Excel.

 

[PivotMeThis]

Thanks Smitty, I'm playing around with Access to see what I can figure out. Can I push back specific fields or will it all go back as a spreadsheet? If it's the whole spreadsheet, then won't I lose my formulas? I haven't worked in Access in YEARS and it's really changed!!!

 

[Smitty]

You can set up a query to return just the fields that you want. If you put your access data into a table format, then when you paste from Access your formulas will automatically extend. You can also do a lot of calculations on the Access side if they're one-time only.

 

[PivotMeThis]

Ok thanks. I'm already running into trouble - the Project Number is unique to each row so I thought it would be a perfect primary key. I got an error message that it could not contain a null value. I don't know what this means, there are no blank rows. Could it be the format? A project number contains letters, numbers, dashes and parenthesis. Such as: XX-###-#(##)--#X-## (never mind about this - the darn thing had a few blank rows at the bottom!)

I think I'm going to have to track someone down around here to help me - I've got a lot of questions!

 

[Smitty]

As long as it's unique it could be a good primary key, just note that it won't be an auto-number field.

As for Access questions there is an Access forum here: Microsoft Access