Excel Prevent Access on READ ONLY Notice

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 then
MsgBox “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.Close
Application.Quit
End 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.

Advertisements

About excelytics

Webmaster and Founder of Excelytics. Established 22-Jun-2010.
This entry was posted in Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s