Free Microsoft Excel 2013 Quick Reference

shared workbook keeps locking and "unsharing" - why?

We have a booking sheet on Excel, on a network shared drive. The workbook is
shared, but every now and then it changes to exclusive use.
We don't think anyone is doing it intentionally, but why is it happening??

Post your answer or comment

comments powered by Disqus
Hello All,

Tried looking through various posts but to no avail.

What I am trying to do is detect when a shared workbook is locked for editing and if so, having application wait until it is no longer locked, then proceeding with saving

so something like
if activeworkbook.islocked = true then Goto retry
problem is, .islocked doesn't exist.

I've tried
on error resume next
loop until activeworkbook.saved = true
but that doesn't work. same thing happens

Also. I am saving to the shared workbook progammatically from an application workbook, so there is no promtp asking user to wait, it just asks them to replace the destination file

for more detail, see this thread:

Thank you

I have a shared workbook and I am trying to create an easy way to input
a new row to enter data. The problem is that I have formula's, list
validation, and conditional formatting in all rows.

I created a macro that would copy row 3 and then 'insert copied cells'
and clear out the data. This works perfect when the workbook is not
shared. But when I share the book, the copy/insert works, but the
conditional format and list validation are gone.

Is there an easier way? Help!!

Message posted from

I have an Excel file that is shared with several users and that merges with
several Word documents. All users can use the Excel file, save whenever they
please, and observe changes made other users without having to save.

If one user is in a Word document, the Excel file cannot be saved by anyone
until the Word document is closed.

Ideally, I would like for everyone to have Word documents open, be able to
make changes to/save the Excel file simultaneously, and have the Word
document show the updated information without needing to be closed and
reopened (after upgrading to Office 2003, this is what happened; I don't know
why it stopped happening).

Hello and thanks in advance for your help.

I'm trying to set up an Excel spreadsheet that is going to be shared between a Mac and a PC over a Microsoft Server. This workbook is also password protected for read rights.

In doing some testing, I found that if I open the document on both a Mac and a PC and I make a change to the SAME row, SAME cell, and if I save using the PC first, when I go to save it using the Mac, I get a message explaining that a change was already made to that cell and it asks me which change to consider the final change. This is how it is suppose to work.

HOWEVER, If I save the file on the MAC first, I never get any message when I save using the PC. It simply overwrites whatever the Mac saved. Then If I try and save the Mac version again, I get a message saying that the File Was Not Saved.

I am using Excel 2008 on my Intel Mac 10.5.4
I am using Excel 2007 on my PC Windows XP

I saved the file as an xls since that's the format that works best with a Mac.

Can anyone help?

The users of a project I'm updating routinely have the main worksheet zoomed down to 75% in order to see all of the necessary columns on the screen at once. In order to avoid the "Not enough resources to display completely", I change the zoom to 100% while the userform is open, saving the previous zoom setting as a variable and restoring it once the userform is closed.

In Userform_Activate:
ZoomValue = ActiveWindow.Zoom
ActiveWindow.Zoom = 100
In Userform_QueryClose
This works fine until I share the workbook, at which time it stops resetting the zoom level when the form is closed. I've
experimented with a button to change the zoom settings while the userform is open and that works fine, so I know it's
possible to change the zoom programmatically within a shared workbook, so what would stop the last line of code from
resetting the zoom when the form is closed in a shared workbook?

Two random bits of possibly useful information: ZoomValue is declared as a Public Integer (in a separate module) and I also tried moving the code from Userform_QueryClose to Userform_Deactivate but it made no difference.


We have a worksheet, which contains charts and data which requires updating by different department.

However, when I have this work-book as a Shared workbook (Tools - share workbook), The charting and macro has all been deactivated.

Would anyone have any idea on how do I share a workbook - while being able to also update the Excel Charts within it ?

(Different user will be updating different charts, and there will be no conflict in between them)

Thank you very much

Su Han

Good day folks,

I have a situation in which I am in need of some assistance.

In this scenario, I have build a workbook that contains many tabs (say 35 worksheets).

My question is, can more than 1 person work on the file simultaneously?

I've tried the "shared workbook" in Excel and read all the help info. on it but it didn't seem to work. I had my colleague open the file (filename test.xls) when I was in the file already. We input several numbers on different tabs but the changes were not reflected on the file.

Any help would be greatly appreciated.

Thank you.

I have a shared workbook with autofilter and frozen panes and the "freeze"
moves around when the filter is used by users (it seems to move with the
filters). Although protecting the windows of the workbook solves the
problem, the format of the protected workbook is really not user friendly.
Kind of a small minimized window that you can't maximize and move around.
Because the settings of the users as well as the size of their monitor are
all different, sometimes it's impossible to see the tabs and there is no
horizontal scrolling bar. Basically solving one problem causes another. Is
there a way to solve one without causing the other?

I hope someone knows if this is possible. I went through all the steps to create a workbook, share it, copy it to the same file, and rename the copies. But when I tested it, I found out that the cells would not add to the main workbook, they would only enter one value.

In other words, I'm trying to let several people have their own copy of my workbook and access it from one network on our system. At the end of the week, I want to copy all of their numbers into one workbook so I can have one set of numbers for the week for a report to the boss. It is a very simple grid, dates on the left and categories on the top. However, if two users put a "1" in on the same date under the same category, when I merge the workbook it will only use one of the "1"'s-----I thought it would add them to show a "2" under that date and category.

Does anyone know if it is even possible to get these shared workbooks to merge and add all of the data in each individual cell??



Hi all!

Had a look in the historical posts on file locks etc but, found no solution to this little problem. A spreadsheet is shared on our Server 2003, we use Excel 2002 on XP workstations. The spreadsheet contains two worksheets and the Share Workbook settings, the following settings are checked:

'Allow changes by more than one user...'
'Don't keep change history'
'Auto Save every 15mins' with 'Save my changes and see others' changes'
'Ask me which changes win'
Print settings
Filter settings

After many days of happy saving.. . yesterday, the file locked and no one (4 users had opened it) could save the spreadsheet. An error appeared with:

This file is locked. Try the command again later.

I can't figure what could have caused this, the article below on MS didn't help either:

I could not find a solution and we had to save and started afresh by copying data into a new spreadsheet and setting configuration as above. Naturally, I would like to avoid this in the future and/or be able to rescue the problem without having to start afresh, any ideas would be more than welcome to avoid this in the future? Many many thanks.. .


I used the Tools/Share workbook option for a file on my company's share
drive. I can open the file just fine, however, when anyone else tries to
open it, they get the following message:

File is locked. In order to save changes to the file, you can either close
the file before making any changes and then re-open it, use Save As (File
menu) to save the file using another filename, or turn off Shared Workbooks
and then save the file.

Why are they getting this message?


I am using the shared workbook function and users get a "this file is
locked" error?

Why does this happen, is there good practice for sharing a file?



Hi all,

i have a workbook with lots of macros that password protect and unprotect sheets. Workbook works fine when it is unshared.

Problem is when i share the workbook and run the same macros i keep getting the error message "run time error 1004: Unprotect method of worksheet class failed"
Surely excel allows shared workbooks to run scripts with password protection? It's annoying because you can't debug in shared workbooks neither! Grrr...

Any help appreciated ASAP because i wanted to go live with my program this evening.



I have a shared workbook which is used for data entry. Once each person is done they press a button which I designed to then transfer the data from their individual worksheet. Here is where my issue is, I move the data to a "mass" sheet that keeps all the submitted data. This is how I originally planned to do this... until I found out you can't unlock or relock a sheet when a workbook is shared? I need to keep some of the sheets locked otherwise they could change stuff by accident or just do something incorrect. Any ideas for work around?

Sub SubmitData()
Dim iReply As Integer
Dim LR As Integer
Dim DestLR As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveSheet.Unprotect "XXXX"
ActiveWorkbook.Worksheets("SubmittedData").Unprotect "XXXX"

iReply = MsgBox(Prompt:="Do you wish to submit all data?", _
        Buttons:=vbYesNoCancel, Title:="Data Submission")
If iReply = vbYes Then
    ActiveWorkbook.Save ' This is done to refresh the master sheet if someone else has already submitted recently.
    ActiveSheet.AutoFilterMode = False
    ActiveSheet.Range("$A$2:$M$1000").AutoFilter Field:=1, Criteria1:="<>"
    LR = Range("A3").End(xlDown).Row
    If Not Sheets("SubmittedData").Range("A1").End(xlDown).Row > 100000 Then
        DestLR = Sheets("SubmittedData").Range("A1").End(xlDown).Row
        DestLR = 2
    End If
    Range("A3", "M" & LR).Copy
    Sheets("SubmittedData").Range("A" & DestLR).PasteSpecial Paste:=xlPasteValues
    Range("A3", "M" & LR).Delete
    Rows("4:1000").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.Goto ActiveSheet.Range("A3"), True
Else 'Cancelled
    Exit Sub
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveSheet.Protect "XXXX"
ActiveWorkbook.Worksheets("SubmittedData").Protect "XXXXX"

End Sub
Maybe I guess I keep the Master Data list on a different workbook? My problem is if I also use Conditional formatting to let them know if they already entered the data. I use this formula to verify using a named range on the "SubmittedData" worksheet....


I have a shared workbook put up in a shared folder and around five users have access to the file. The shared excel file is manipulated by another excel file using macros. I have just started testing the file with multiple users.

All of a sudden, the file gets locked and it gives the error message "This file is locked. Try the command again later." while trying to save the file. This is not happening because of multiple users trying to save the file simultaneously. I have copied the file locally to my desktop and I am getting the same error while saving.

As a solution, I have copied all the data from the locked file to a new file, made it shared and get going with my job. But my concerns are, On what scenario the file can get locked? How to prevent it from happening in the future when we are planning to scale up the number of access to around 50 users?

Hi all,

I have a problem I can't figure out. I have a protected worksheet with some macros running on it, and I have been requested to make the workbook shared. Trying to implement this, I keep getting the standard "1004 You cannot use this command on a protected sheet" error when applying an autofilter. Here is what is making this problem a little tricky:

- As the workbook is shared, I cannot do Protect UserInterfaceOnly because you can't change the protection settings on a shared workbook without unsharing it. Needless to say, I cannot unshare the workbook.

- When I protect the sheet, I do allow the user to use autofilter. Consequently, Sheet.Protection.AllowFiltering and Sheet.EnableAutofilter both are on. Still, I keep getting the error.

The failing line of code is:

shData.Range("_filterDataBase").AutoFilter lngField, strArg 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
where shData is the codename of the sheet, lngField is the number of the field and strArg is the filtering criteria.

My guess is, that the error comes from the fact that (for some silly reason) in VBA the .AutoFilter method is used to create a new filter, not only to use an existing one. The members of Sheet.AutoFilter.Filters collection are read-only, and cannot be used to modify the existing filter.

I would be very grateful if somebody could either clarify that using .AutoFilter on a protected worksheet is something that simply cannot be done, or point out what I am doing wrong. Also, if I am wrong in any of my above assumptions of "can't do" nature, please correct me.

Good morning everyone,

Using a piece of code I learned from the wonderful book 'Excel Hacks'. That when a user selects a locked cell it password protects the worksheet, and when they select an unlocked cell it unprotects it to allow editing. This works beautifully, but for some reason doesn't work in a shared workbook

    If Target.Locked = True Then 
        Me.Protect Password:="Secret" 
        Me.Unprotect Password:="Secret" 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The workbook I have it in also has 'Grouped' rows, which also are affected by the shared option.

Is there any way to allow the code and grouped options work in a shared workbook?


I have a workbook that needs to be shared by 20 people.

the users fall in to 3 catagories - mokeys, snakes and lions lets say.

the monkeys enter the data
the snakes check it say so on the sheet
then the lions check it all and use the information.

so far i have set the sheet up using 3 sheets hoping to protect the 2nd and 3rd sheet with different passwords so the snake's and lion's work is protected.

this works fine until i share the file; at which point all the protection is disabled and everything is completely locked!

please help! I am comfortable with programming and have worked in VB but am not any expert using this in excel to do commands outside of macro scripting!

hope someone can help!


My apologies to viewers who have read this post already. I forgot to put a title on my last post.

As part of a string of answers to a previous question (see “Why do I encounter problems running macros in a shared workbook?”), I’m left with the impression that I cannot successfully run macros in a shared workbook if I protect worksheets/workbooks. The reason I’d like to have worksheet protection is that I have many data forms set up and there are some data forms where I do not want users entering or editing existing data fields (sheet protection grays out the cells and the “New” button”). If anyone has successfully protected and unprotected a sheet using a macro in a shared workbook, I’d love to hear about it. I’ve tried setting up some very simple examples and have not been able to get them to work.

I read the Excel help menu on the limitations of workbooks and it does not say that you cannot use protection features on shared workbooks

(From the help menu) In a shared workbook, you cannot do the following:

· Assign a password to protect individual worksheets or the entire workbook. Protection that you applied before sharing the workbook remains in effect after you share the workbook.
· Change or remove passwords. Passwords that you assigned before sharing the workbook remain in effect after you share the workbook.
Write, change, view, record, or assign macros. However, you can record operations in a shared workbook into a macro stored in another workbook that isn't shared. In a shared workbook, you can run macros that were created before you shared the workbook; although if you run a macro that includes an unavailable operation, the macro stops running when it reaches the unavailable operation.

Any comments?

I've got a shared workbook that is very active with several users. For the most part everything is working great. There are several tabs for different functions/users and there is one tab in particular that the whole sheet keeps freezing where you can't scroll up and down. Any ideas why this keeps happening?

In our work environment, we use shared workbooks. When someone has the workbook open, it is locked to all other users. Sometimes, the user will forget, and leave the workbook open for an extended period of time. How can we tell which user has the workbook open at the time?

We have a shared workbook stored in a machine in LAN and 7 members want
to update the file at the same time.
But if 2 people try to save data at the same time
the shared excel file is getting locked with the error
"Unexpected file lock by the user.. "
"The file has been locked by the user.. "

Solution tried but not succeded:
Cleaned up all the temp files in the machine in which the shared excel is stored.

If anyone can suggest a solution for this then that would be a great help for us.

I was in search for the solution in so many forums and no one suggested a right solution for this.


We are using a shared workbook in an office with up to 25 users sharing
the workbook at any given time. Currently we are getting an error that
states "locked for editing by user - "XXXXXX" Read Only...."

The problem is that the username is generic and could by anyone. We
have had everyone log out of excel to clear it up, but it is still
giving us that same error. Does anyone have any suggestions as how to
"kick" whichever user is locking it up so that we can gain access again?
Our plan is that once we get in there we will have everyone correct the
username fiasco by placing unique info in there, but until then we are

I appreciate any assistance!

Oh yeah, it's Excel 2003.

three6ohchris's Profile:
View this thread:

I have a shared workbook that approximately 5 people use. Last week it
started displaying the message that is locked for editing by a user and could
only be opened in Read Only mode. The user listed is not using the shared
workbook. In fact, if he tries to open the document now it says he get the
same message that is is locked by him. If I could simply make a copy and
delete the old one, that would be great. However, attempts at deleting the
workbook fail as it thinks it's in use.

Any ideas on how to get around this would be appreciated. We're using
Office 2003 and the workbook in in a shared location on our corp. network.
It is not a rights issue on the server.

No luck finding an answer? You could always try Google.