Solved

Excel 2010: Default actions and message boxes depending on whether file is in use.

Posted on 2016-07-25
12
46 Views
Last Modified: 2016-08-10
I would like to use VBA code to do the following when a spreadsheet is opened:
  1. If the file is already in use by someone else, have a messagebox pop up and say something like" User nnnnn is using this file right now, and you must wait until it is not in use to enter your information."  Then when the user clicks "OK" Excel would close.  The user WOULD NOT get the normal message about the file being in use and asking whether or not they want to open it read-only or be notified when it is available.
  2. If the file is NOT already in use, just have a messagebox that says to make the desired data entry and save and close the file, as others need to input information also.

Can this be done?
0
Comment
Question by:Kevin
  • 5
  • 4
  • 3
12 Comments
 
LVL 17

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41728858
Hi try this

Option Explicit

Private Sub Workbook_Open()
    Application.DisplayAlerts = False

    If ThisWorkbook.MultiUserEditing = False Then
        MsgBox "You may continue to add data, but please close when finished. Others need to use it.", vbInformation, "File not shared"
    Else
        MsgBox "This workbook is already in use. ", vbCritical, "Closing"
        ThisWorkbook.Close False
    End If
    Application.DisplayAlerts = True
End Sub

Open in new window

0
 

Author Comment

by:Kevin
ID: 41729321
Thank you for the help.  The first part worked fine and gave the "please close when finished" message.  But when I opened the file on a second computer, I still get the standard dialog box

 Excel locked file message
which still allows the second user to open the file.  We want to prevent two people from being in the file at the same time.

Any ideas whether or not the standard Excel dialog box above can be bypassed?
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41729436
What about this?

Private Sub Workbook_Open()
Application.DisplayAlerts = False
If ThisWorkbook.ReadOnly Then
   MsgBox "The file is already open. Try again later...", vbInformation
   ThisWorkbook.Close False
End If
Application.DisplayAlerts = True
End Sub

Open in new window

0
 

Author Comment

by:Kevin
ID: 41729450
Neeraj,

That doesn't work either.  It seems like maybe the DisplayAlerts flag only works after the file is opened.  And that happens after the default opening Excel message...

File already open
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41729464
I tested the code and it works at my end.

Make sure Application.EnableEvents is set to True
In Immediate window type ?Application.EnableEvents and press Enter, you should get True

Edit: I think you already told that the first part worked for you that means Application.EnableEvents is set to True otherwise the workbook open event wouldn't have been triggered.
0
 

Author Comment

by:Kevin
ID: 41729496
Enable Events is true.  I am testing with two PCs on my network.  I open the file and get the normal message that says to quickly enter data and close.

While the file is open, I use another PC on the network to open the file.  But I still get the default message about opening the file that is use by another user BEFORE it opens and I get my VBA code to execute.

I am not sure why it is working for you but not for me...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41729507
Yeah that is surprising me as well.
0
 
LVL 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 500 total points
ID: 41729841
Sharing workbooks is always problematic and should be avoided. I think the reason the code does not run when you open the workbook as second user  is because as far Excel is concerned that event has already run.

This code will show who is using the workbook whilst shared.
Option Explicit

Private Sub Workbook_Open()
    Application.DisplayAlerts = False
    If ThisWorkbook.MultiUserEditing = False Then
        MsgBox "You may continue to add data, but please close when finished. Others need to use it.", vbInformation, "File not shared"
    Else
        MsgBox "This workbook is already in use. ", vbCritical, "Closing"
        Application.Dialogs(xlDialogFileSharing).Show
        ThisWorkbook.Close False
    End If
    Application.DisplayAlerts = True
End Sub

Open in new window


Also, this code can be useful to close a workbook if a user leaves it open.First of all set a time limit eg 10 minutes .
Private Sub Workbook_Open() 
    EndTime = Now + TimeValue("00:10:00") 
    RunTime 
End Sub 

Open in new window


This resets the timer if a change in the workbook occurs
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If EndTime Then
        Application.OnTime _
        EarliestTime:=EndTime, _
        Procedure:="CloseWB", _
        Schedule:=False
        EndTime = Empty
    End If
    EndTime = Now + TimeValue("00:10:00")
    RunTime
End Sub

Open in new window


If no changes have occurred then this runs
Public EndTime 
Sub RunTime() 
    Application.OnTime _ 
    EarliestTime:=EndTime, _ 
    Procedure:="CloseWB", _ 
    Schedule:=True 
End Sub 
 
Sub CloseWB() 
    Application.DisplayAlerts = False 
    With ThisWorkbook 
        .Saved = True 
        .Close 
    End With 
End Sub 

Open in new window

0
 

Author Comment

by:Kevin
ID: 41730142
Thanks Roy. I just recently saw how to allow "Share Workbook" from the Review tab on the ribbon.  So I set it to share that way so that they macro could tell who else had the file open, even if it was to only close the file afterwards.  The problem with that, is that even if I am the First and Only person to open the file that way, it thinks that it is in a "shared state" (i.e. open somewhere else) and will then close...
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41730595
As I said sharing files is a pain and should be avoided. Is there no other way you can collect the data?
0
 

Author Closing Comment

by:Kevin
ID: 41750867
Thank you both.  The first solution actually seems to give me what I need and the second was extra helpful as well.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41751500
Pleased to help
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now