Free Microsoft Excel 2013 Quick Reference

Sheet locked by user

We have several users that are trying to open excel from the start menu and
it is telling them the sheet is locked by another user. They can open in
read only, notify or cancel. These aren't shared documents on the network,
they are opening excel and loading the default template. We're experiencing
this all over our company.

We also have people opening documents from their personal network share with
the same message. The server shows no one else connected to the share but
them MOM show's no one else opening the files. It's as if Excel isn't
releasing the lock it places on the file when it is open.

Post your answer or comment

comments powered by Disqus
Had a file moments ago that was locked by a user who no longer had the file open. The file was out on a network share.

More strange, the user logged back in and tried to open the file and was told it was still open by him and that he could only open it READ ONLY!

Any idea what the problem was?

BTW, I opened the file perhaps 10-15 minutes later and it was no longer locked - spooky!

Excel .exe crahed whilst closing a file. Now when I try to open the file it says locked by< beth> which is me!. I have logged out , shut down rebooted emptioed temp files delted xlb files arghh.
I am on a peer to peer network running win me and using excel 2000.
Will had helped me solved the link to acxess problem and I need to be able to always have the file called the same name for acess to see it.
So though I am able to take a copy though then readable is not a complete solve as I need to be able to rename the openable file the same as the now locked file. And as it is locked I cannot delete it.

in win me I am not confident to do a dos deletion and my norpotons wipe did not assist

hello, here is the situation

i have got a workbook with 20 sheets which i always want to be protected...

now when the user inserts new sheets, the new sheet is unprotected which is fine..

however, when the user runs a macro in the workbook by default the macro calls the unprotect and protect procedures and all sheets including the new ones become protected..

is there a way to prevent this from happening ? i.e. new sheets created by the user will always be unprotected...

thank you

Very strange: Someone is trying to open an XLS but gets the msg that it is locked by one user while in fact it is opened by another!
When that 2nd user closes the XLS and the file is opened, the locked for editing msg is not displayed.
Two users had this problem - file was supposedly locked by always the same user while in fact it ws another user who was editing the document.
Any ideas???

I'm currently running a shared spreadsheet between 14 office managers. Whenever one manager is editing and another tries to open the document we get a "locked for editing by 'user'" message.

Is there anyway to change 'user' to the actual user id.

We have 250 users total. excel was installed from a network image.

Thank you for your time.

I have a macro (see below) that begins by unprotecting the active worksheet,
runs several subs, then protects the sheet again. The user runs the macro
by clicking on a graphic in a locked cell. The sheet is normally password
protected. I'm pretty sure that something odd has happened - I'm sure the
user could run the macro without having to give the password to unprotect,
and now the macro won't run without they do that. Also, when the macro
finishes, the sheet is protected, but doesn't seem to have password
protection - Tools|Protection|Unprotect just unprotects it without asking
for the password. Maybe I'm going nuts... Anyway - is there a way of
building the passowrd protection into the macro, and also, is what happens
in the description above the norm? I was SURE the macro would run on a
password protected sheet without the user being asked for the password.

Sub Main_MEMCARE()
End Sub

All help and suggestions gratefully received,



Dan E

I need a solution to unprotect by user if he suplied the password by typing in input box.

my code:--
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Report

    Dim tdate As String
    tdate = Format(Date, d - mmm - yy)

     If Not Intersect(Target, Range("E:E")) Is Nothing Then
        ActiveSheet.unprotect Password:="password"
      If Range("E11").Value = "Done" Then
       Range("F11").Value = tdate
        ActiveSheet.Protect Contents:=True, UserInterFaceOnly:=True
        ActiveSheet.Protect Password:="password"
        ActiveSheet.EnableAutoFilter = True
      End If
    End If

      If Not Intersect(Target, Range("J:J")) Is Nothing Then
        ActiveSheet.unprotect Password:="password"
      If Range("J11").Value = "Done" Then
       Range("K11").Value = tdate
        ActiveSheet.Protect Contents:=True, UserInterFaceOnly:=True
        ActiveSheet.Protect Password:="password"
        ActiveSheet.EnableAutoFilter = True
      End If
    End If

    Exit Sub
     MsgBox Err.Number & " " & Err.Description

End Sub
-- once it is locked .. when i try to edit something .. it says sheet is protected message..
what I need is to promt an input box to enter password..


Hello Dave,
Its clear the Each user will see the changes, but how can i give access rights to the their named sheet alone, i.e., they should not see the Other Sheets and also Master Sheets.
For Example,
If a User Named John Peter shares my work book
He should be able to access only that Particular Sheet "John Peter". if he updates the "John Peter" worksheet , it should be updated as same in the master sheet named as "Admin".
If it can be done, get me a sample excel template if possible.
Thanks for the reply.

Ananth S Rajan


I have a an excel document where i want a formula to pick up a sheet name from another sheet where a user can input the required name. The sheets are named in months and their year, example; Mar06 Sep07 etc.

my current formula to caluculate a years worth of data is


This points to sheet name Mar06 cell F3 then adds its to sheet name Apr06 cell F3 and so on for the twelve months.

I want the user to be able to change the months calulated by typing in the month name without having to change every cell with this formula.

if i have a sheet called UPDATE where cell B9 has the value Sep07 which will be a sheet name, how do i write a formula to pick this up?

where this calulates a years data, you are correct to assume that on the sheet UPDATE, for this example, C9 will have Oct07, D9 will have Nov07 etc

I would like to copy data on a Master sheet to a Weekly sheet to retain the values for each week.
I have this code, but it doesn't find the date for some reason:

Public Sub copycolumns()

  Dim rng As Range
  Dim WEDate As String
  Dim Monthws As String
  Dim Weekws As String
  Monthws = "Master Monthly-Weekly Totals"
  Weekws = "Weekly"
  WEDate = Worksheets(Monthws).Range("B4").Value 'Get Week ending Date
  If WEDate = "1/8/2012" Then
    MsgBox "The Dates Match"
    MsgBox "The Dates Don't Match"
  End If
  With Worksheets(Monthws)
    'WEDate = Worksheets(Monthws).Range("B4").Value 'Get Week ending Date
    Set rng = Worksheets(Monthws).Range("D1:D128").Find("Weekly Running Total", LookIn:=xlValues)
    rng.Offset(2, 0).Resize(rng.End(xlDown).Rows + 3).Copy
    Application.CutCopyMode = False
  With Worksheets(Weekws)
    Set rng = .Range("A1:A53").Find(What:=WEDate, LookIn:=xlValues)
    If Not rng Is Nothing Then
       ActiveSheet.Cells(1, 0).PasteSpecial Paste:=xlPasteValues
       rng.Offset(1, 0).Resize(rng.End(xlDown).Row - 1).PasteSpecial (xlPasteAll)
    End If
 End With
End With
1) Read Date input (End of the week date) by User in B4 on the Master Monthly-Weekly Totals sheet
2) Copy Data in Column D
3) Go to Worksheet "Weekly" and find the same date input by the user, and paste the data to that column

I think the problem here is the Find(What:=WEDate), I've tried Find(WEDate), even tried Find(CDate(WEDate)), Excel always finds Nothing, so my paste code never executes.

I have a workbook on our local network which several people can access - although note that this is not a shared workbook - it can only be accessed by one user at a time.

Very rarely, when I make changes to the workbook and then click save, I get the following message:

"The file <INSERT NAME HERE>.xls may have been changed by another user since you last saved it. In that case, either save a copy or overwrite changes".

Please can anyone tell me why I get this message, and how do I prevent this happening? My guess is that we both tried to access the file at exactly the same time, therefore neither of us got the "workbook locked by user" message.



What I need to create is a pair of two sheets, that as they are copied and re-named the second sheet would be renamed by the first sheet's name with a prefix added.

So if there was a pair of sheets "sheet1" and "Psheet1", and they are copied at the same time and automatically renamed by excel to "sheet1 (2)" and "Psheet2 (2)" or the index of sheet(3) and sheet(4). The user could just rename the first “A” and the second would be renamed by the CONCATENATE function, adding P to the name of the first with resultant sheet name “PA”.

To complicate things, the users currently change the sheet name on the tab it-self, that name is inserted on a cell in the sheet by using the formula.


So there is actually no change on the work sheet to trigger the input. This is not a requirement but it would be nice that the only input would occur on the tab it self.


Hello all, thanks for your time in reading this. Here is my issue.

I have a userform with 3 refedit controls on it. The first control requires the user to specify a range on one worksheet. The second and third controls require the user to specify a range on a second sheet.

This is where the issue arises:

1. Assume the user runs the form while the first sheet is active.
2. The user specifies the first range with the first control and all is well.
3. The user switches to the second sheet.
4. The user specifies the second range and all is well.
5. The user clicks on the final refedit control ... and is taken back to the first worksheet.
6. They must then click on the second worksheet again to find the range they need to specify the last parameter before they can begin running the program.

This is pretty annoying. Is there any way to prevent Excel from pulling up the first worksheet until after my code is finished running so that my users don't have to re-select the second worksheet in between using controls?

I googled the crap out of this, but all I found was one guy asking the question in 2005 and nobody answered him. : /

Thank you in advance for your assistance.






I have a macro that loops through several workbooks, copies the information on the sheets into a master sheet, and then closes the workbooks. What I need to do is only copy the sheet that the user specifies instead of every sheet in the workbook.

Below is a copy of the subroutine that loops through and copies the data. Any help would be most appreciated.

Private Sub Transfer_Data()
    Dim FromRow As Long
    Dim FromSheet As Worksheet

    Workbooks.Open Filename:=FromBook
    For Each FromSheet In Workbooks(FromBook).Worksheets
        '- LOOP ROWS copy/paste to master sheet
        With FromSheet
            LastRow = .Range("C65536").End(xlUp).Row
            For FromRow = 2 To LastRow
                If .Cells(FromRow, "C").Value  "" Then
                    .Range(FromSheet.Cells(FromRow, 1), _
                        FromSheet.Cells(FromRow, NumColumns)).Copy _
                        Destination:=ToSheet.Range("A" & ToRow)
                    ToRow = ToRow + 1
                End If
        End With
    Workbooks(FromBook).Close savechanges:=False
End Sub

Could you please help, i want to know the way for hidding sheet tab by using vba.


Is it possible to use an event procedure to hide or unhide certain sheet(s) when a certain user opens an Excel file?

Using the User name field that appears in the General tab of the Options menu I was thinking I could have the spreadsheet know who the user was and ultimatly unhide a customized sheet for that user. Following is an example:

If the User name is John Smith, Sheet1 would unhide and all other sheets would hide.
If the User name is Jane Smith, Sheet2 would unhide and all other sheets would hide.
If the User name is undefined, Sheet3 would unhide and all other sheets would hide.

As I know the freezpanes property is to freez the sheet by tow lines (vertical and horizental)
Is there any oce to freez the sheet only by horeizental line.

And thank you for help.


I have a shared workbook (it also contains macros) on a network, when one of the user open it and try to save it, it gives error message.
"Unexpected file lock by user. Please try again...."

However when I save it, its ok before getting the error by other user but if the other user gets the error and after that I also receive the same error.

Please guide me what is the problem and how can I fix it.
I use Excel 2007.

Hello there,

Is there a way to compare two documents or sheets side by side and have excel tell you or highlight a cell if it doesn't match with the other cell in the other docoment or sheet.



All of a sudden the other day, I notice that when you go to a person's profile to try to "view posts by user," it just goes to the search page instead of the results.

For example, this is the link to view posts for myself

Doesn't work


[ This Message was edited by: Von Pookie on 2002-07-15 16:35 ]

[ This Message was edited by: Von Pookie on 2002-07-15 16:36 ]

Hello, I am looking for your help (newbie excel knowledge) on this:

I have this sheet called Budget :

A ..... B ..... C ..... M ..... Q .... R
1 | 1.1 ..... Production Services .... Mr. Tata ..... 24€ ..... 22€ .... 29.01
2 | 1.2 ..... Other Casts .... Mr. Mama ..... 23€ ..... 35€ ..... 22.02
3 | 1.3 ..... Prod. Loads .... Mr. Lolo ..... 25€ ..... 26€ ..... 21.01
4 | 1.4 ..... Prod. Transp .... Mr. Dodo ..... 21€ ..... 23€ ..... 21.01


45 | 25.1 ..... Prod. News .... Mr. Ilan ..... 24€ ..... 25€ ..... 3.09
46 | 25.1 ..... Other Services .... Mr. Hung See ..... 24€ ..... 25€ ... 1.09

I want to generate a new sheet ordered by date (R Column) like that:

3 | 1.3 ..... Prod. Loads .... Mr. Lolo ..... 25€ ..... 26€ .....
4 | 1.4 ..... Prod. Transp .... Mr. Dodo ..... 21€ ..... 23€ .....
TOTALS (of M and Q): 46€ ..... 49€

1 | 1.1 ..... Production Services .... Mr. Tata ..... 24€ ..... 22€
TOTALS (of M and Q): 24€ ..... 22€

and so on....

Anyone can help?
Please I'm desperate!

Does anyone know of a way to determine who the "user" is that has document
open when we get the "Can only open as read-only" message? I am on a network
and every computer has a unique identity. I checked with the first level of
IT and got nowhere.

Can excel files be opened in fixed screen size preset by user?

Can the window size be set by the user. So that the next time excel opens it
would open to the custom settings set by the user. The custom settings can be
changed by the user?

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