macros and vba in a shared workbook.

Dear All,

I was asked to create a complaints and compliments database in excel with the ability to produce some reporting.

So from the database tab I created various pivot tables. Which, as per the code below refresh upon activation

Please Login or Register  to view this content.

Private Sub Workbook_Open()
Worksheets("Home Page").Activate
End Sub

The workbook also contains various other macros.

The problem is that the workbook must be Shared so as that people can access it and update it at the same time.

To my annoyance, and even more so to that of my boss. I have discovered that these macros do not work on a shared workbook.

Does anyone know how i can overcome this.?

Thanks alot

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Nothing I can see there that shouldn't work in a shared workbook. Not that I use them though as they are pure evil

Dom

Just found this on contextures. Should have googled this first!! oh well. can always rely on sumproduct for reporting!!

Too Many Missing Features
After you share a workbook, many of Excel's features can't be used. There's a list of unavailable features for Excel 2003 on the Microsoft site, and in Excel's help. For example, you can't add any of the following features, and in some cases you can't even change the existing items:

•Conditional Formatting
•Data Validation
•Lists
•Protection
•Pivot Tables

Sorry I thought it was the lower 3 of the macros that you were referring to.

Dom

Your options are basically:
1. Don't use shared workbooks
2. Add code to unshare the workbook, refresh the pivot table, then reshare it. No idea how/if this would work if other users are in it.

Originally Posted by OnErrorGoto0

2. Add code to unshare the workbook, refresh the pivot table, then reshare it. No idea how/if this would work if other users are in it.

I think they would need to close and re-open the workbook to regain shared access.

Dom

hi guys - guess the reporting (Generally counts of this that the other by dept by month will have to be formula based) and some of the funtionality required will be lost. back to the drawing board.........

You could put the reporting (or data entry) part in a separate workbook?

theres an avenue to explore!!!