PDA

View Full Version : Shared Workbooks - against all odds...



Kuttel
07-29-2014, 02:05 PM
Hi everyone,

I am about to start on a VBA project with a shared Excel workbook. I am aware of the limits of shared workbooks so I investigated a little bit and also made a few inquiries in other Excel and VBA forums and found that almost everybody disencouraged me to use shared workbooks as it is prone to cause trouble. I'd like to see if this is the same in this forum...
If there was anyone here willing to exchange some information and/or experience regarding following issues, I would we very happy :thumb

- AutoSave in small intervalls (<=1 min) to have a de-facto realtime updating functionality
- how to handle multiple user instances without unsharing the workbook
- detect changes after saving (=updating) in order to trigger the Worksheet_Change sub (existing work-around?)
- on detection of changes, play sound as a notification
- avoid loss of data when editing of the same cell by multiple users occurs (existing work-around?)
- reasonably protect worksheets against unwanted changes (in layout/structure etc.) but allow users and VBA-code to enter data.
(Imagine a list of line items contributed by different users. Editing/deleting of such a line item shall only be possible for the user who created the line item. Is there a way to achieve this?)

I will keep my fingers crossed and hope to find a comrade-in-arms when it comes to shared workbooks :friends:

Kuttel

Aflatoon
07-30-2014, 03:23 AM
almost everybody disencouraged me to use shared workbooks as it is prone to cause trouble.

I'm only surprised it was almost everybody. Avoid them like the plague. If you have to use Excel for this, rather than a database, I'd suggest using a workbook as the data store and having separate front end workbooks to read and write that data in code. It will be a fair amount of work though and a database program would make your life a lot easier.

Bob Phillips
07-30-2014, 04:07 AM
Don't touch them, they are evil, at some point they will corrupt and become useless (more useless than they started as), and you will become known as the fool who ignored all the good advice to avoid shared workbooks.

As Aflatoon says, a database is a better choice, but at the very least it is a good practice to separate the code and the data.