Multi-user access to a VBA-application

**nymm** · 04-29-2011, 10:28 AM

The short story: What I have is VBA-application, imbedded in an otherwise empty MS Excel (2007) file . What I want is for multiple users to be able to open and use this VBA-application simultaneously from a shared network location.

The longer story: The application is used for financial simulations. Basically, the user selects a whole string of settings and parameters on a input form, the application does some calculations, and then it returns the corresponding financial data on a second user form. It’s made my work quite a bit easier in the past. So much so, in fact, that we’ve decided to make it available to all our employees. But as it turns out, transforming what was originally intended as a simple tool for personal use into a full-fledged, multi-user application, isn’t that simple after all.

Some background information: The project started out as a spreadsheet with some built-in automation through VBA-code. As I continued to expand on the latter, the former became largely irrelevant, until I ended up with a virtually independent VBA-application, which happens to be stuck in an otherwise empty Excel-file.

If I would have the time, I would of course start over, and recreate the tool as a stand-alone executable in VB or Java. But rewriting several thousand lines of source code is simple not an option at this time. (Note that this exorbitant length is mostly due to my lack of programming skill. It could easily be reduced in size by at least 80% by extracting the financial parameters – which are currently hard-coded – and retrieving this information as needed from a separate database or ini-file. I intend to do this, as soon as I figure out how.)

The fact that I’m stuck with MS Excel poses quite a few problems, but the main issue is that Excel files are unsuited for simultaneous access by multiple users, or so I've been told. The simple solution would of course be to send everyone a copy of the file. This is not an option, however, because the tool requires frequent updates in order to ensure the continued accuracy of the calculations, and we can’t afford the risk of people accidently using an outdated version, As such, airtight and fool-proof versioning is absolutely essential.

The current solution We're thinking about creating a script or batch file which would copy the most recent version (and, optionally, the supporting database or ini-file) from the server to the user’s temporary folder, overwriting any previous versions, and then open that copy. A link to this script would be placed in the software section of our intranet. I would work, I guess, but it’s far from elegant, and doesn’t completely eliminate the risk of people using the wrong version (e.g. by accessing an outdated copy though “recently used documents”).

The question: Should any of you have a better idea on how to achieve this, please feel free to enlighten me!

**tlafferty** · 04-29-2011, 10:44 AM

It may be possible to open the Excel file directly on the web page without users downloading and viewing in Excel natively...

**Roshan10043** · 04-29-2011, 12:11 PM

Hi,

Multiple users can simultaneously use the same file and will not face any issue untill and unless you are saving any information in the file. You can protect the file and allow everyone to open it in a read only mode. Ensure that the file is not in a shared mode. Hope this helps.

Regards
Roshan

**royUK** · 04-29-2011, 01:49 PM

What you need is to save the existing workbook as an addin, this can then be deployed on the server and each user could install & use the addin. You would need to either have a customised ribbon tab or a menu for access to the tools

**nymm** · 05-02-2011, 03:35 AM

First off, thank you all for the input!

It may be possible to open the Excel file directly on the web page without users downloading and viewing in Excel natively...

That sounds really interesting, but I have absolutely no idea how that could be achieved. Could you please elaborate?

Multiple users can simultaneously use the same file and will not face any issue untill and unless you are saving any information in the file.

I've already included some code to intercept and discard any attempt to save the file, and to prevent the "Save changes?" dialog on closing the file. Unfortunately, this doesn't prevent Excel from prompting the user to "Save As" when the file is opened as read only, or when it is accessed from a server location where the user has read only rights (as is my intention).

What you need is to save the existing workbook as an addin, this can then be deployed on the server and each user could install & use the addin.

I'd first have to learn how to create a tab or menu, and rework the interface a bit, but that sounds promising too. I'm just wondering whether this will be easy to distribute automatically and remotely. I really can't afford to rely on the individual users to install the addin themselves, let alone keep it up to date. Could be a versioning nightmare...

**Roshan10043** · 05-02-2011, 03:56 AM

I think you can still set a workaround something like when the user tries to save the file run a code which checks a password or a boolean value which needs to be set TRUE only when you want to save the file. If the boolean value is false then nobody can save the file.

Let me know if this sounds OK.

**royUK** · 05-02-2011, 03:59 AM

here's a comprehensive tutorial on creating an addin which can then be deployed on a network or installed on individual machines.

**nymm** · 05-02-2011, 04:31 AM

Whoa, you guys are fast!

Below is the code I currently use to prevent saving the file, using the password-protected setting "Protect Workbook Structure" as a flag. I'm sure there's a much more elegant way of doing this, but until I find it, this will do:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

   'prevent the user from saving unauthorized changes
    If ThisWorkbook.ProtectStructure = True Then
        Cancel = True
    Else
        ThisWorkbook.Protect ("mypassword")
    End If

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

    'prevent Excel from prompting to save the changes on closing the application
    ThisWorkbook.Saved = True

End Sub

This works as intended, neutralizing any unwanted attempt at saving. But as I mentioned, it does not prevent Excel from prompting the user to "click Ok and use Save As to save the file with a different name" whenever someone tries to save while in read-only mode. Which obviously isn't actually possible, because of the code preventing any attempt to save, including Save As. Which therefore ensures plenty of emails and phone calls from confused users...

here's a comprehensive tutorial on creating an addin which can then be deployed on a network or installed on individual machines.

Thank you for the resource! I'll look into it as soon as possible.

**snb** · 05-02-2011, 05:03 AM

Your last remarks are the best arguments in favour of RoyUk's suggestion.
An AddIn is the obvious method in this case (and intentionally designed for this purpose).

**nymm** · 05-03-2011, 08:01 AM

Judging from that tutorial, I'll have my work cut out for me, but it does indeed seem to be the best solution.

Thank you all for your advice!

Multi-user access to a VBA-application

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Multi-user access to a VBA-application

Re: Multi-user access to a VBA-application

Re: Multi-user access to a VBA-application

Re: Multi-user access to a VBA-application

Re: Multi-user access to a VBA-application

Re: Multi-user access to a VBA-application

Re: Multi-user access to a VBA-application

Re: Multi-user access to a VBA-application

Re: Multi-user access to a VBA-application

Re: Multi-user access to a VBA-application

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions