PDA

View Full Version : Solved: make workbook close if in use by other user



jmaocubo
02-01-2013, 03:14 AM
Starting:
http://www.vbaexpress.com/forum/showthread.php?mode=hybrid&t=45172

Hi

I created a project where all actions are performed in userforms.
Is there any way when the workbook is opened by a user in his workpost pop up an alert to another user who is trying to open the workbook that it is open and can not make any changes? then this workbook.close (until no one is using it)


Thanks in advance

Miguel

Dave
02-02-2013, 07:53 AM
I don't get it... doesn't it just error if someone tries to open a workbook that's already in use? How are you opening these wbs... from XL? Dave

jmaocubo
02-02-2013, 10:47 AM
I don't get it... doesn't it just error if someone tries to open a workbook that's already in use? How are you opening these wbs... from XL? Dave

Hi Dave,

Thanks for the replay.

No it doesn't give you "Error" it just tells you that the wb is in use and if you want to open as read only. the problem is if someone press yes to read only, when he tries to save (vba code to save),it's a copy and not the real file that is save.

Dave
02-02-2013, 11:13 PM
How are you opening these wbs... from XL? Dave

jmaocubo
02-04-2013, 09:44 AM
From xlsm

users working: excel 2007 and excel 2010

Dave
02-04-2013, 10:52 AM
Here's a potential solution. Add some code to the "master" wb that saves the file to a backup file whenever the "master" file is saved. Then before accessing the "master" file, trial using the copyfile method to copy the backup wb to the "master" wb. If the "master" file is in use you will get error 71 and you can provide the user with a msgbox indicating that the file is in use. If the "master" file is not in use, it is replaced by the identicle backup file and the user can then proceed to access the "master" file. HTH. Dave

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
'source,destination,save
FSO.CopyFile "c:\test\backup.xlsm", "c:\test\master.xlsm", True
If Err.Number = 70 Then
On Error GoTo 0
MsgBox "Master File Open!"
Exit Sub
Else
'open master wb here
End If
Set FSO = Nothing

rcharters
02-04-2013, 12:16 PM
Try adding the following code to the ThisWorkbook module.

Private Sub Workbook_Open()
Dim bReadOnly As Boolean
bReadOnly = ThisWorkbook.ReadOnly
If bReadOnly = True Then
MsgBox "Workbook is Read-Only. File will be closed."
ThisWorkbook.Close SaveChanges:=False
End If
End Sub

jmaocubo
02-15-2013, 11:38 AM
Thanks Dave and rcharters.

both solutions work in my case