Run time error 1004 when running macro in shared workbook?

[richard.ca]

When running a macro to insert a new line in a table I get runtime error '1004' - application
or object definition error (translation from German).

The macro works perfectly well when the workbook is configured and saved as 'not shared'.

Problem is I can't get into the debugger to see exactly where it's crashing since VBA is
locked out when the workbook is shared.

Any ideas what's going on and what I can do about it?

Thanks...

 

[Sandeep Warrier]

Shared workbooks have many limitations. You can check out what can and cannot be done in a shared workbook using Excels Help.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Andrew Poulsom]

Some features aren't available in a shared workbook:

http://office.microsoft.com/en-us/excel/HP052010801033.aspx

 

[richard.ca]

Sorry for the very slow response, just got back from a long vacation!

I was very surprised (and frankly disasspointed) to discover so many restrictions with
shared workbooks. However I've done some digging around and I see that it's possible
to work around these restrictions by switching off sharing while using a restricted
feature. I've done a few tests using the following code:

    ' switch off workbook sharing
    
    Application.DisplayAlerts = False
    ActiveWorkbook.ExclusiveAccess
    Application.DisplayAlerts = True
    
    ' use feature not allowed in shared workbook
    ' etc...
    
    ' make workbook shared again
    
    Application.DisplayAlerts = False
    ActiveWorkbook.KeepChangeHistory = True
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, AccessMode:=xlShared
    Application.DisplayAlerts = True

This seems to work although I've yet to test it properly with several users. I've now
become a bit wary of workbook sharing and I'd be interested to know if anyone is aware
of any problems/pitfalls with the above method or if there are better ways.

Much appreciate your comments...

 

[Richard Schollar]

If this is a mission critical workbook I would be very wary of keeping it shared as Shared Workbooks are notorious for corruption at some point failing to work. MS is aware of this but has yet to implement a proper fix.