Shared Personal workbooks

[A Guy Named Robby]

In the office in which I work, many employees are using a shared personal workbook. Here's how that is set up: On a network drive, there is a PERSONAL.xlam file. A shortcut to that is on everyone's XLSTART folder. The file is updated frequently, and that was the best way I could figure out how to serve our needs. It works perfectly except for one thing.

If someone opens an Excel workbook first thing in the morning and stays in there, PERSONAL.xlam is locked up from being updated until they close out of it, which tends to not be until the end of the day. Is there a solution to this? I mean, other than asking that person to close out of Excel of course.

Or if someone has another solution for this situation, I'm welcoming any suggestions too.

 

[gallen]

Your issue is because of the shortcut. If the file has no access control then everyone can open it and modify it. Could you not get the network admin to set the permissions so that only those who need 'write' access have it and rest have just 'read only'?

 

[A Guy Named Robby]

I'm not sure how easy it would be to get that done, unfortunately. Is there a way with VBA or something to force the workbook the open in read-only mode without an alert?

 

[A Guy Named Robby]

I can't edit my last post, or else I would have.

I found a nice workaround. This will open it in read-only mode for everyone but me, or anyone with the same name as me, at least.

Private Sub Workbook_Open()
If Application.UserName <> "A Guy Named Robby" Then
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
End If
End Sub

 

[LockeGarmin]

Nice Find!

A couple of years ago I ran into this same issue, and what I ended up doing was always keeping my shared workbook as readonly, and then having it delete the workbook itself, resave itself, and then mark the file as readonly. I ran into an issue a couple of times where it would refuse to save and I had to do some manual work of saving it with a different name and having the person get out of excel. It was a lot more code then your solution so I hope that it turns out that this is fool proof!

Anyway the main reason I commented was to say that you can get around someone having the same name as you by using the following instead of Application.UserName. It might be overkill or it could be handy to know.

Environ$("UserDomain") & "/" & Environ$("UserName")