macros and vba in a shared workbook.

[Blake 7]

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

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("_RespondedInTimeByDept").PivotCache.Refresh
Application.AlertBeforeOverwriting = False
End Sub

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

Private Sub CommandButton1_Click()
Call Print_Sheet
End Sub

Private Sub CommandButton2_Click()
ActiveWorkbook.Close SaveChanges:=True
End Sub

Sub QuickHideUnhide()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Tab.Color = vbRed Then ws.Visible = Not ws.Visible
    Next ws
End Sub

[Domski]

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

[Blake 7]

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

[Domski]

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

Dom

[OnErrorGoto0]

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.

[Domski]

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

[Blake 7]

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.........

[OnErrorGoto0]

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

[Blake 7]

theres an avenue to explore!!!