Shared Workbook Limits in Excel 2010

Shared Workbook Limits in Excel 2010

Last week, a client sent me a workbook that I created for them a couple of years ago. They were having problems with it, even though things had been going smoothly since we first installed it.

To see what was happening, I went into the Visual Basic Explorer, and clicked on the workbook name. Instead of prompting me for the VBE password, this error message appeared: Project Locked — Project is Unviewable.

Well, that’s not good news. It’s hard to troubleshoot the code, if you can’t get in to see it.

Excel error message: Project Locked: Project is Unviewable
Excel error message: Project Locked: Project is Unviewable

Project is Unviewable

My first guess was that the workbook was corrupted, but I checked the Microsoft site to see if there was any other explanation for this terse message.

I found this article: XL2000: “Project Is Unviewable” Error Message When You Try to View Project

Solve the Problem

Even though the article was written for Excel 2000, it solved my problem in Excel 2010. The project was unviewable because someone had saved the file as a shared workbook.

Of course, nobody knows how or when that happened, but we can make the file work properly again, by turning off the shared workbook setting.

To turn off Shared Workbook in Excel 2010:
  • On the Excel Ribbon, click the Review tab, and click Share Workbook

shareworkbook01

  • On the Editing tab, remove the check mark from “Allow changes by more than one user…”

shareworkbook02

  • Click OK, to close the dialog box.
  • Click Yes, to confirm the change.

After making that change, everything worked smoothly again, and business went back to normal.

Shared Workbook Limitations

A few years ago, I wrote about the limitations and problems that come with using shared workbooks in Excel 2003. There were lots of comments, with most people agreeing that shared workbooks cause trouble.

A few people were in favour of them though, and were willing to trade the limitations for the shared features.

It’s a couple of versions later, and there are still limitations and problems with shared workbooks in Excel 2010.

Nothing seems to have disappeared from the list of unavailable features, but I noticed that the following new limitations have been added:

In a shared workbook in Excel 2010 you cannot:
  • Create or apply slicers
  • Create or modify sparklines
  • Sort or filter by formatting

The previous restriction on creating lists was changed to a restriction on creating Excel tables. You can’t add them to a shared workbook, and you can’t share a workbook if it contains tables.

To see the full list you can visit the Microsoft page on shared workbooks, and read the list of Features That Are Not Supported.

Still Avoiding Shared Workbooks

So, even though Excel continues to get new features, nothing new is available in shared workbooks. I’ll continue to avoid them. How about you?

________

0 thoughts on “Shared Workbook Limits in Excel 2010”

  1. Debra – I’m still a fan of shared workbooks, although my use is very specific. We have a group of folks who need to update the status on items they are responsible for. They never add or delete items, and there is only one person assigned to each item. Works like a charm!

  2. I’m wary of shared workbooks too. Got called in to “fix” one, rebuidling the spreadsheet from scratch was the only option.
    While I appreciate they may be okay in some scenarios, a problem is some users start using them, and recommending them to others, without being aware of the pitfalls when they go bad.

  3. We use shared workbooks in two flavours:
    a) The safe approach where each user updates details on their own worksheet about the serially-numbered prescription sheets used. This works as each user is responsible for different areas, and there is never any editing conflict.
    b) All the internet advice is to not allow editing by multiple users in the same area, and if still considering to think again! However we needed system for several clinicians requesting a test, staff arranging the tests and nurses interpreting the results. The solution I have adopted is that everyone starts with view-only rights, and a small table on a control worksheet sets out which users have been granted permission to edit certain parts of the data.
    A front userform menu is used to request permission for a task (eg add a new patient, or process an existing patient entry) which causes the worksheet to save itself so updating itself. If the relevant permission table entry is empty, this is filled with details on the computer and windows user plus a time stamp, and the workbook immediately resaved to lock-out anyone else who tries to obtain the same permission. The requested update is achieved by specific userforms, and when these are completed the spreadsheet is amended, and the workbook resaved with the edit permission cleared – so releasing the workbook for other users. With a relative small number of users, the occasional block for a couple minutes is not a problem for us.
    Obviously for a larger number of editors this would become unweldy and a database program such as Access would be easier, yet standard Office suite does no include this and we have but Excel as an option.

  4. I am trying to allow 50 people access to a shared workbook but they keep getting a shared violation error. This spreadsheet has just been moved from version2003 to 2010 could this be the problem. I really need this to work properly people are starting to lose faith in this. Help!

    1. Hi Andy.
      50 users for one shared workbook? Are you kidding?
      I think this is a case for something better designed for this situation, like MS Access.

  5. Taking this to the next step.. any ideas on improvements to Shared Workbooks in Excel 2013. We have six staff that need to work on one workbook with conditional formating. We had so many share violations with Excel 2010 we had to drop back to Excel 2003. It would be wonderful to upgrade to the functionality of 2013 if we can avoid the conditional formating problems..

  6. I have an issue regarding shared workbooks. Sometimes if a user updates the file and save the file become locked and if the same user closes and try to open the same file, it would only work as read only and then I would have to do a save as for others to have access to it.
    Note the user does not go and lock the file, I am trying to figure out why it is occuring. I don’t believe it is a dro.p in the network beacuse we have a stable infrastructure. Also the system administrater verified that the file is not opened by anyone on the network nad it is still lock by that user

    1. Try, when no one else needs to use the file, openining and taking off sharing (which saves file in process) and then reapplying shared status (file gets resaved) – easy to set up some on-sheet buttons to do this.
      Also have you looped through ActiveWorkbook.UserStatus to check what the file thinks it is being shared with or still opened under ? This does not always equate to the computers the file is physically currently open on. If you want some coded examples, email me on [email protected]

    2. Within a shared worksheet, only 1 person within the report can open, close, or save the report at a time. If anyone else tries to perform one of these actions while one of these actions are being performed; it results in the “file is locked” typed dialog box.

  7. Hi All,
    It seems that sharing a Workbook works better with pre-formatted files such as one with a worksheet that has a list of staff in one column while the rest of the other columns are left for editors(Time and attendants records). This will reduce the chances of more than once person editing the same row element at a time(often the problem). Editing the same row at the same time creates conflict and accepts the latest editor’s input – thereby over-writing other’s input.
    As mention in one post by David Ruben, It is best for users to work on separate sheet on a shared workbook, as that will avoid the save conflict. Sam

  8. One suggestion that I have tried and it assisted with a similar issue was to delete all the temporary files from a users computer. She had previously received a sharing violation error, and the system tried to recover the document, but the file it was trying to use had become corrupted. Each attempt after (including shutting down and pulling the power from the PC) to open the file resulted in her “no longer sharing” the document. I proceeded to delete all temp files and rebooted the system. This resolved the issues! For some reason, the corrupted file in the temp folder was the link that Excel used to open the shared document, once it was removed, she could access the “real” file from the server again.
    Environment: All users on Windows 7 in a formal network (Windows SBS 2011) using Active directory and logins. We have Office 2010.

  9. Debra,
    Your solution is fine, but I have an extra addition to it that I can’t seem to solve…
    I have an event that disables saving: BeforeSave() where I have set Cancel = True.
    This prevents the Unsharing from saving… Recap:
    1. I can’t see the vba code to remove the “Cancel = True” from the Before Save event, because the workbook is shared!
    2. I can’t Unshare because the “Cancel = True” in the Before Save event is preventing it…
    I am getting a little frustrated here… 🙂
    Regards,
    Frode Tveit

  10. I discourage the use of shared workbooks whenever possible. But unfortunately sometimes it is the only way. We (my company and department) has approximately 60 staffers and 3 managers spanning states for staff and 3 states for managers. Since we are not all in the same location, often times a shared workbook was the only way. However after having to reconstruct corrupted files from scratch I have finally been able to convinve management that making daily backups of each shared file is worth the time… a hassel, but worth it compared to recreating a massive file from scratch!

  11. Hi,
    do you know if its possible to share an excel file for multiple users so that they can only see, create, delete &modify the data they own. In other words, only that information that they fill in. But the master data could be seen by the Admin (=one person). Any hints how to execute a project like this? Thanks for any possible tips!

  12. With the shared workbook/file locking issue – we’ve found that if every user using that workbook opens it from within Excel (using file…open or file…recent) then we don’t get any locking problems. If one person opens it from Windows Explorer (either navigating to it or using a shortcut icon) then it’s just a matter of time before the file gets locked for everybody in it.

    1. The Windows Explorer Preview Pane might be the underlying reason for this, it seems the file preview is considered an ‘open’.

  13. Is there any way to lock certain cells/ranges of a worksheet within a shared workbook? I’m responsible for the numbers (multiple columns) while folks are responsible for the explanations (one column). I’d like to lock the numbers so folks can’t accidentally change numbers if they didn’t refresh the links to the backup data I supply. Any suggestions? Is this why the file keeps getting “locked” or “read only” when folks try to access it?

  14. We are having trouble with a shared workbook, when we set the updated to “automatically every 5 minutes even though we have “ok” it defaults back to 15 mins ?

  15. We just converted Excel files from 2003 to 2010 and are having trouble with shared workbooks. There are 3-4 users in the same file at a given time. When one person makes a change to the file, we should be able to see those changes when the other users save. We can see some of the changes, but other changes are only visible to the user who made them. It seems very sporadic. Has anyone else run into this or have a solution? We have no choice but to use a shared workbook for now, so not sharing them is not an option. Any help would be appreciated!

  16. 15 years ago I was involved with a shared file that kept corrupting. We fixed the problem by cleaning up some history feature in MS Excel. We agreed we would have to do this once a month. I can’t remember what we did, but it worked. I am thinking, I will make a new spreadsheet with the data, if I can’t find that feature.

  17. I have been trying to merge workbooks using Excel 2010, but every time I do that I get a warning saying:
    Workbooks to be merged must be copies made from the same shared workbook, and must maintain the change history for sufficient amount of time.
    Of course, I’m sharing copies of the main workbook and I made sure that the time is long enough.
    Anyone knows how to fix this?
    Your response is greatly appreciated.

  18. WE are using a shared excel worksheet that we keep on a driver so about 4 people can make updates to information.
    We all have 2010 Microsoft Version 14.7149.5000
    We all save as we go, but in the Am next day- updates made are not there.
    Thank goodness the lady make the majority of changes also saves it on her desktop. Could that be the problem.
    we all save as we go so the spreadsheet has the most updated information.

    1. With a shared workbook the file contains the current versions of all those working on it (so each users view of the file). If one user saves a copy to their desktop, that creates a single user snapshot of the file (so severs the other versions away).
      You’ll see this in the file size, the version on the desktop will be smaller than the shared network version because it doesn’t contain each users view of the file.
      If she then (presumably) opens her desktop version and saves that over the top of the shared version then of course changes will be lost.

  19. Ive come here not because I have share problems but because I am aghast that I cant do so many things in a shared workbook (with myself on a different device).
    I am struggling to contain my indignation that a company the size and history of Microsoft have so poorly implemented this feature in 2015. What a joke indeed, let alone the unfriendly navigation of windows 8. Well let me ask you what is the alternative? to email myself the spreadsheet?

  20. I work in a regulated enviroment (21CFR) and an audit trail is required on electronic files. Therefore, I use Excel’s “Share and track changes” to maintain an audit trail. Unless I go out and buy third party software (and validate it), I do not see an alternative.

  21. Namaskaram
    I have used for shared excel files. sometimes these excel reopened , this shared option is removed. how to stop this regards

  22. I have created a share&protected W/B and developed support project in VBA (VB6). It has been working for past two years while shared for about 40 users at the same department.
    By using ‘Protect Shared Workbook’ in excel Review ribbon and then use appropriate folder/file share and security featurs introduced for advanced sharing in Windows file manager interface, the problems can be controled. Note that makeing a back up file at “BeforeSave” event is mandatory, so as such back up file musy be led programatically to be stored in a separate sahred folder. In contrary to the folder of main shared file,the folder containg back up file must be shared so that the ‘file/folder delete’ is marked as ‘Deny’ed.
    All actions needed to be performed in main shared workbook, shall be made possible for protected&shared workbook regarding limitations outlined by Microdoft. This is also possible by appropriate use of sheet protect allowance choices together with cell protection Locked/Unlocked switch.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.