Userform Network access issue

Hi All.

This issue I can't resolve. Please bear with me while I describe the issue:

The Situation
I have a large project built in Excel - it is a complex statistical model with a userform GUI. It consists of several VBA modules. The overall size of the .xlsm file is about 2Mb. The VBA files are password-protected in VBEditor.

Of the VBA files, the largest BAS is 36Kb and the largest FRX is 141Kb. The userform contains 506 controls. Everything compiles fine.

The workbook is arranged such that only one sheet is visible, containing a launch button for the tool's GUI, which automatically initializes when the work book opens via the Workbook_Open procedure in the ThisWorkbook module.

During the loading of the userform, it accesses a network drive which pulls in information for the form.

The Problem

When two people try to use the form simultaneously, the form will crash for one user saying the shared drive is in use and cannot be accessed. Is there a way to stop this? We are rolling out to a large group and this will cause lots of issues.

THank you!

Hi and welcome

Originally Posted by jbob

During the loading of the userform, it accesses a network drive which pulls in information for the form.

Where is it pulling this information from on the network? Another Excel file? Does it open that data source as read only?
Could we see the code that does that?

I work with a very similar system and to get around these issues (when opening the source as read only doesn't work) I store the data I need for combobox lists etc. in a database and import them during the workbook open event.
That way when I need to make changes to the list I just have to change the detail in the database then all user will receive it when they next open the Excel file.

Beth.

Originally Posted by jbob

When two people try to use the form simultaneously, the form will crash for one user saying the shared drive is in use and cannot be accessed. Is there a way to stop this?

What you probably need is a quasi-locking mechanism that allows users to wait until the file is available on the network drive. Two such scenarios are (pseudo-code):
1. Wait until the file is available:

Please Login or Register  to view this content.

2. Create a temporary 'Canary' file that indicates the file is busy:

Please Login or Register  to view this content.

2A. Create two temporary 'Canary' files that indicate the file is busy (there may be a race condition that causes Scenario 2 above to fail):

Please Login or Register  to view this content.

Lewis