Avatar of Kevin
KevinFlag for United States of America

asked on 

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

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?
Microsoft Excel

Avatar of undefined
Last Comment
Roy Cox
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Kevin
Kevin
Flag of United States of America image

ASKER

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

 User generated image
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?
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

Avatar of Kevin
Kevin
Flag of United States of America image

ASKER

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...

User generated image
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.
Avatar of Kevin
Kevin
Flag of United States of America image

ASKER

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...
Yeah that is surprising me as well.
SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Kevin
Kevin
Flag of United States of America image

ASKER

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...
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

As I said sharing files is a pain and should be avoided. Is there no other way you can collect the data?
Avatar of Kevin
Kevin
Flag of United States of America image

ASKER

Thank you both.  The first solution actually seems to give me what I need and the second was extra helpful as well.
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Pleased to help
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo