Only one user to edit workbook at a time

[tarajess]

Hello
I'm trying to set up an excel sheet on a sharepoint site. Many users will have access to it, but I only want to permit one user to edit it at a time. Others warned "Read Only" sheet "......xx....." in use by "........ yy......."

I know I've seen it before, but for the life of me can't remember how to do it (and I've just gotten Office 2007 now to throw into the mix!).... I think it was done in a macro before???? Anyone got any suggestions?

Thanks!

 

[dave3009]

I think you want to share the workbook, see here

http://office.microsoft.com/en-us/excel-help/use-a-shared-workbook-to-collaborate-HP010096833.aspx

 

[tarajess]

Thanks Dave, but sharing a workbook allows the ability to edit simultaneously. I tried that one first!!

We only want one person to edit it at a time and the rest to get a warning that it's in use and then get the option to either wait for a notification that it's free (user has closed it) or ability to read only.

I'm using Office 2007 on an XP OS.

 

[arkusM]

I assume that Sharepoint does something different than the default Excel behavior of only allowing one person to have Write access the file?

Would that be a sharepoint setting?

 

[tarajess]

Thanks - after a lot of digging, I discovered it was Sharepoint. Thanks to all for prompt replies and suggestions

 

[Smitty]

Welcome to the Board!

You can set Read/Write access programmatically:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> SetAsReadOnly()<br>    <SPAN style="color:#007F00">'   Test for PC User Name</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> strUser <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>        strUser = Environ("USERNAME")<br>        <SPAN style="color:#007F00">'   MsgBox strUser</SPAN><br>        <br>     <SPAN style="color:#007F00">'  Set Read only File Access for each Office's specific version</SPAN><br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> strUser<br>        <SPAN style="color:#007F00">'   Full Workbook Access</SPAN><br>        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "YourUserName", "AnotherUser"<br>            <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook.ReadOnly Then _<br>                ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite, WritePassword:="admin"<br>        <SPAN style="color:#007F00">'   Limit Access</SPAN><br>        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <> "YourUserName"<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> ActiveWorkbook.ReadOnly Then _<br>                ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly, WritePassword:="admin"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[scottylad2]

Welcome to the Board!

You can set Read/Write access programmatically:

Sub SetAsReadOnly()
****'** Test for PC User Name
****Dim strUser As String
********strUser = Environ("USERNAME")
********'** MsgBox strUser
********
**** '**Set Read only File Access for each Office's specific version
****Select Case strUser
********'** Full Workbook Access
********Case Is = "YourUserName", "AnotherUser"
************If ActiveWorkbook.ReadOnly Then _
****************ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite, WritePassword:="admin"
********'** Limit Access
********Case Is <> "YourUserName"
************If Not ActiveWorkbook.ReadOnly Then _
****************ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly, WritePassword:="admin"
****End Select

End Sub

HTH,

I have a similar requirement to this old post. I have a workbook on a shared drive, it's accessed by multiple persons daily. Is there a way to have some code that would mean something like, the sheet is being used in edit mode by (username) if you require to make changes please try again late, otherwise this book will be read onlyr.

Id like it read only with no option to save a copy because that's whats happening just now and we're eventually running with more than one workbook with users making several copies