MS Excel VBA Does Not Work in Shared / MultiUserEditing Mode

Question

Th VBA functions in my MS Excel project fail when the Excel Workbook is in Shared / MultiUserEdting Mode. As it is in Shared Mode I cannot debug the VBA to determine what is failing. If it helps the error message I get is "Run-time error - '1004'; Application-defined or object-defined error". My VBA sets numerous references of Worksheets and Ranges within Worksheets; and makes use of numerous global arrays to store function information. As such, I'm having difficulty identifying excatly which part of the VBA is failing. Can anyone help; e.g:

• Suggest the types or VBA object references that most commonly fail only when in Shared Mode;
• Suggest where I can read-up on Run-time error - 1004'.

I hope you can help and I look forward to hearing from you at your earliest convenience.

Regards Tenochtitlan

---

Tenochtitlan

Answer 1

This from the xl2003 help file lists possible problem areas...

"The following features can't be changed after a workbook is shared: merged cells, conditional formats, data validation, charts, pictures, objects including drawing objects, hyperlinks, scenarios, outlines, subtotals, data tables, PivotTable reports, workbook and worksheet protection, and macros."

The error 1004 is a generic error that generally doesn't pinpoint specific problems.
I don't share workbooks, but can't you unprotect your project before sharing, specify break on all errors and see what is going on then.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)

Answer 2

Thanks Jim,

With the information you provided, I've managed to sort-out the problem. As you suggest, it appears that attempting to set Hyperlinks in Shared Mode was causing the VBA to fail.

Just to confirm, it's not possible to view VBA in the VB Editor regardless of whether the project is protected or break on errors is specified. I did try following your suggestion. However, Excel simply does not let you in to the VB Editor when in Shared Mode.

Thanks again.

Regards Tenochtitlan

---

Tenochtitlan

Answer 3

If you want to use VBA on a shared workbook I would recommend moveing the code to an XLA (or another unshared workbook).

---

Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

Answer 4

Well it’s a big concern since everything today is about sharing data, multiuser editing and security. I would just wait for MS Excel to come up with something better but meanwhile I’m exploring other options such as http://www.collatebox.com/ .