PDA

View Full Version : Prevent two network users from opening same workbook?



EdNerd
05-07-2007, 10:35 AM
If a workbook is already open and a user tries to open it across a
network connection, they get the "Read Only / Notify" dialog. Is
there a convenient way to detect that one user already has the
workbook open, and prevent a second user from opening the file at all,
not even as Read Only?

Ed

Simon Lloyd
05-07-2007, 10:58 AM
You could try this:

Option Explicit

Private Sub Workbook_Open()
If ThisWorkbook.ReadOnly Then
ThisWorkbook.Saved = True
Application.Quit
End If
End Sub

put this in the Thisworkbook module ( untested) and when the workbook is opened readonly it will quit.

Regards,
Simon

Bob Phillips
05-07-2007, 11:33 AM
Not Application.Quit, but ThisWorkbook.Close SaveChanges:=False

Simon Lloyd
05-07-2007, 02:53 PM
My apologies Bob, Application.Quit is brutal and unimaginative!

Bob Phillips
05-07-2007, 03:55 PM
It is very imaginative, just not sure it would be appreciated

geekgirlau
05-07-2007, 08:32 PM
It might be a good idea to display a message letting the user know why the workbook has suddenly closed on them ...

Simon, my personal preference is a small electric charge - not lethal of course, but enough to make the user's hair stand up. Now if I can only create an add-in for this :devil2:

Simon Lloyd
05-08-2007, 02:35 AM
GeekGirlau, it's entirely possible!, as you will be aware they are creating all sorts of useless USB plug in devices, i got given a cup warmer to keep my drinks warm while being on the computer - all it served to do was burn me as i put the cup back down whilst not really looking, so, i'm thinking a little mod here and there perhaps a finger clamp to prevent pulling away from the device and hey presto! you have a "do as i say or i make you incontinent" device!

Thoughtful regards,
Simon

For EdNerd

Option Explicit

Private Sub Workbook_Open()
If ThisWorkbook.ReadOnly Then
ThisWorkbook.Saved = True
Msgbox "Workbook is being edited Read Only mode not available, Workbook will now close!",VbInformation,"Mode Availability"
ThisWorkbook.Close SaveChanges:=False

End If
End Sub

jammer6_9
05-08-2007, 05:48 AM
Simon Lloyd, I copied your code. I have added it on my company Sales Analysis today and I almost get fired... just kidding... But unfortunately I am not successful to apply it. As I open the workbook as first user, File in Use dialog box popout. Is there additional setting that I need to do? Is it a network error? A system error of mine? I like the concept of letting only one user to open the workbook looking that I have a 90 mb file that is a little bit slow now and might be slower once another user opens it. Please :help ...

All I did is just copy and paste this code on WorkBook_Open Module

Option Explicit

Private Sub Workbook_Open()
If ThisWorkbook.ReadOnly Then
ThisWorkbook.Saved = True
Msgbox "Workbook is being edited Read Only mode not available, Workbook will now close!",VbInformation,"Mode Availability"
ThisWorkbook.Close SaveChanges:=False

End If
End Sub

Simon Lloyd
05-08-2007, 06:11 AM
As long as you paste it in to the ThisWorkBook module it should work fine!, you could change the first line to:

If ThisWorkbook.ReadOnly = True Then
but it should make no difference to the action!

Regards,
SImon

jammer6_9
05-08-2007, 06:34 AM
Yeah you're rcorrect. Aint no problem with the CODE. It was a user error. What I did is delete the Previous Workbook in the Network then Copy a new Workbook with your CODE and it's working fine now. Thanks this thread will help me a lot.


As long as you paste it in to the ThisWorkBook module it should work fine!, you could change the first line to:

If ThisWorkbook.ReadOnly = True Then
but it should make no difference to the action!

Regards,
SImon

lucas
05-08-2007, 07:46 AM
I need to clarify which workbook you put this code in...does it go in the file on the server or in the thisworkbook module of the workbook you are running that is trying to open a file on the server..?

jammer6_9
05-08-2007, 07:58 AM
The CODE is place on Workbook_Open Module of the Workbook which is located in the Network Drive...

lucas
05-08-2007, 08:17 AM
Thanks Jammer......
workbook open in the thisworkbook module of the file on the server...got it.