I developed a time sheet system where employees sign in and out at the start and end of their shifts. Although the file is accessible on the network it is not a designated “shared” file. A problem occurs when the file is open on another computer.
Attempting to load a second copy of the time sheet causes a notice to appear stating that the file is in use by another user but still allows the user to open the file as read only. In our case, the users are quite content to hit any button to load the file as read only not knowing what this really means.
Once the time sheet system loads, they sign in or out of the system thinking that all is well when, in reality, the copy that was just saved did not update the main spreadsheet. A copy of the file is created and saved elsewhere (My Documents).
With a little VBA, it is possible to prevent this from happening. We determined that it would be best to send the user a message advising them that the system is open by another user and updates cannot be made until the other person is finished working with the system.
- The Solution:
Since the file is normally set to allow read / write access, we know that a “read only” version must imply that someone else is using the spreadsheet. We can use the ReadOnly property for the workbook to determine the status. If it is True, the workbook is a read only copy, otherwise it should have opened with normal access.
To prevent the user from getting access to the spreadsheet, paste the following code segment in the Workbook_Open procedure in ThisWorkbook:
Option Explicit‘ ===================================================Private Sub Workbook_Open()If Workbook.ReadOnly = True thenMsgBox “File is IN USE by Another User” & vbCrLf & _“Please contact the Administrator” & vbCrLf & _“For Assistance – Thank you!” & vbCrLf & vbCrLf & _“Click OK to Exit the System.”‘If more than one workbook is open we only want to close our‘copy – the user may be working on more than one workbook!‘If not, then the user likely opened our workbook, so we can quit‘all together.If Workbooks.Count > 1 then Workbook.CloseApplication.QuitEnd Sub
- What if Macro’s aren’t enabled
If Macros are not enabled, obviously the above code won’t execute, but there’s a trick to getting around that as well. Chip Pearson developed two routines to prevent an application from loading if macros are disabled. See our Excel Online Web Page for the link to Chip’s web site. You’ll find a very professional presentation of Chip’s work and the VBA is extremely well documented.
We’ll present this in a downloadable file once we get the feature installed on our site.