Multiple Users of .XLSM File Causing Runtime Error? RRS feed

  • Question

  • Hello All,

    I've recently developed a small program in VBA that runs in Excel.  The program performs simple file copy processes including sending files to a zip folder.  The program also reads a text file and uses that information to name the text file.  The user is prompted with a userform displaying a photo and the user selects between 2 options buttons to determine if the photo is black and white or colored (which is also part of the zip name).

    Runs great.  The only issue I have is it seems when more than one person is accessing the Excel XLSM workbook and running the macro a runtime error is encountered.  I have found this to be the case on a PowerPoint program I wrote that assembled PowerPoint presentations together - only 1 user can use the file at a time.  In both cases the files containing the macros are saved to a network location.

    Why is this and is there any way to code the file to avoid the error?



    • Edited by Grasor Sunday, June 5, 2016 4:11 AM
    Sunday, June 5, 2016 4:10 AM

All replies

  • As the workbook in question appears only to be a vehicle for carrying your code to perform assorted filing functions, could you not save the template as an Excel add-in and supply copies to your co-workers? Then they won't have to try and open a workbook that is intended for single user use/

    Graham Mayor - Word MVP

    Sunday, June 5, 2016 5:42 AM
  • As Graham, I'm assuming that you can dismiss user's changes in the workbook. I believe that you want to centralize the workbook in a single place so maintenance gets easier and I'm assuming that your option to supply each copy to each user is the last one. I would try:

    - Go to the file location in your network and change its access to Read Only.

    - If this doesn't work and it is a XLS, XLSM or XLSB, save as a XLTM template extension, so every time a user opens the file, Excel will generate a new copy in memory for the user.

    If this don't work, could you tell which line are you getting the runtime error and paste here the code? -

    Sunday, June 5, 2016 1:35 PM
  • The Excel Add-in option exists however I need to clear modifications to the software through our IT pro.  Also, I didn't mention it as I didn't think it pertinent at the time but as this is the initial release I prefer to keep it in the current format (*.xlsm) until I've resolved the user errors.

    I was out of the office when I received the bug report but when I attempted to replicate the issue I could not get a "runtime error" to reproduce.  However, I found some odd behavior.  When the macro encounters an unhandled error - it does not break (Options are Set to Break on Unhandled Errors).  Instead, the program goes back to a label I created to handle a different error earlier in the program (the label is "Retry:").  I had to add additional code to ensure that if an error is encountered and the code has already executed beyond the "Retry:" label that it throws a custom error message box and exits the sub. 

    I was able to resolve the error I believe the users were encountering after I enabled the Option to Break on ALL Errors and then addressed that line. 

    One follow up question: 

    1) Why is my error handling option acting this way?  I'm on a different system than I usually work on which is using Office 2013 instead of 2007.  On the 2007 version the macro breaks properly when an unhandled error is encountered.

    Monday, June 6, 2016 6:32 PM
  • Sir,

    Thanks, those are all great ideas.  I might incorporate this as an Add-in after I've hardened it enough to operate error free.  I usually let the users go at it for 4-6 weeks before calling a build "good." 

    For additional clarification - the macro does not actually make any changes to the workbook.  It is purely a vehicle to perform file operations.  The workbook does not change.


    • Edited by Grasor Monday, June 6, 2016 6:45 PM
    Monday, June 6, 2016 6:40 PM