Looking for opinions on Shared Workbooks

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
179
Hi, All...

Background: I have a LAN-based Shared workbook that occasionally becomes corrupted after users go in and change formatting and add highlights. When this happens, I need to do some manual workaround for my VBA to run the next day. The "shared" aspect is mandated by management and I do not have any control over that piece...grrr...arrgh...

I did a general search online for "what can cause a shared excel workbook to become corrupted" and found a couple of cut-and-dried responses:

"All shared workbooks become corrupted -- it's a poorly implemented feature. The solution is to not use shared workbooks."

"There are two types of shared workbooks, those that are corrupted, and those that soon will be!"

I am just curious if users on this forum have the same opinion of Shared Workbooks? And has anyone discovered the secret to preventing formatting changes from making the corruption happen in the first place?

Let the debate begin! :rolleyes:

Thanks for any input!
~ZM~
:cool:
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
My first computer Job was in the USAF correcting user damaged Spreadsheets.
They would delete formulas by entering data over the formula. Then in desperation after deleting the formula. Pull out their handy calculator to get the answer and put that in the cell till I was able to fix problems.

And these were not shared.

The way to solve these problems are to keep up to date backups.

And keep the backups some place where users cannot get to them.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
In my experience, one thing that can help is using worksheet protection, with passwords if necessary.
It's not foolproof, but it can help alot.

Another thing that might help - try spending time with the other users to see how they actually use the file.
It may be that you THINK you know how they SHOULD use it, but for all kinds of good and bad reasons they actually use it in completely unexpected ways.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
789
Office Version
2007
Platform
Windows
Hi ~ZM~

I also inherited some shared workbooks a while ago, and I can honestly say: BAAAAAAAAD. They are extremely prone to corruption/lock-out and it is a nightmare to recover from. I hope you get enough comments on here to take back to your management and show them how bad shared workbooks are!
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,266
Office Version
2013
Platform
Windows
Hi,

Speaking personally, I would always avoid shared workbooks. In situations where users needed to share data I would create template copies of a master workbook (database) & have these read write data to it. This approach requires some VBA but I did not experience any of the issues you describe.

You can read another opinion here:https://contexturesblog.com/archives/2008/11/18/avoiding-shared-workbooks-in-excel/

Dave
 
Last edited:

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
179
Thanks for the input, everyone!

I should also mention for clarity: this is a daily report that gets run via a VBA (read-only) template I created. The VBA generates a newly-created workbook each day, with new daily data pulled from a data text file. The VBA saves the previous day's report in an "archive" folder, then takes the new data and creates pivot tables, splits the data into separate tabs based on the billing rep name pulled into the report and re-saves the report so the reps can go in, pull their daily work and add notes, highlight and format as necessary and also allow management to go in and see the progress being made throughout the day if needed. There are usually between 10-13 reps each day who have access to the file, plus 2-3 managers.

At one point I discovered that one particular user was hiding columns - every day - and that was causing the file corruption EVERY DAY for a week. Once I realized, I told her to stop doing that and things got better...but now it's happening again (not every day) and there are no hidden columns. People use a lot of highlighting, which I saw somewhere else can cause problems as well but here is unavoidable since that's how they keep track of their finished items.

Again, thanks for everyone's input and I'm not looking for solutions (I know there aren't any for this) as much as just javing a general conversation on the subject. ;)

~ZM~
:cool:
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,665
Office Version
365
Platform
Windows
- give EVERY user their very own workbook that runs a macro to simply OPEN the master and pull the data etc into their workbook and close the master WITHOUT saving it
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
789
Office Version
2007
Platform
Windows
- give EVERY user their very own workbook that runs a macro to simply OPEN the master and pull the data etc into their workbook and close the master WITHOUT saving it
I don't think this meets the OPs need for managers to get real time updates on progress - they would only see the start of day position.
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,665
Office Version
365
Platform
Windows
@jmacleary is correct

- give EVERY user their own workbook that runs a macro to simply OPEN the master and pull the data etc into their workbook and close the master WITHOUT saving it
AND ...
- update the manager's workbook with data from individual workbooks (via macro) as often as is required

Structure of individual workbooks should be protected to prevent users merging cells, moving columns etc
 

Forum statistics

Threads
1,082,018
Messages
5,362,702
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top