PDA

View Full Version : Restricting access to a user to update a cell that is being updated by another user



Poundland
11-02-2009, 06:32 AM
Apologies about the long title of this thread.

My issues is this,

I have a shared workbook, that has multiple users, each user has his or her own sections to input data into except in one sheet, where users are expected to copy and paste from their own sections into this sheet, my problem is how can I restrict access to a user from inputting into a cell when somebody else is in the process of inputting into the same cell but has not yet saved?

Any help would be appreciated.

lucas
11-02-2009, 08:30 AM
I think you will find that this is the least of your problems using shared workbooks.

It's not just my opinion:

http://www.vbaexpress.com/forum/showthread.php?t=28407&highlight=shared+workbook

http://www.vbaexpress.com/forum/showthread.php?t=27455&highlight=shared+workbook

Poundland
11-02-2009, 08:55 AM
Lucas,

Ok, I get the message that sharing a workbook is not the optimal method, and I have witnessed first hand and had to rebuild spreadsheets that have been shared and corrupted, but these have always been where the same sheet is used over and over again over a long period of time.

My workbook is newly created each week, so will only ever be 1 week old.

Perhaps I should be approaching this from a different angle, is there a way that I can show a user when they open the required sheet how many other users are in the same sheet and who they are?

lucas
11-02-2009, 09:19 AM
You could check to see if it's open before even trying:
Option Explicit
Sub callit()
Dim myPath As String
myPath = ThisWorkbook.Path & "\"
MsgBox WorkbookIsOpen("test.xls")
End Sub

Private Function WorkbookIsOpen(wbname) As Boolean
' Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function