Shared Workbooks & Code
 

Hi, I have a rather large workbook (some 150 sheets) that requires sharing as multiple users will be inputing data.

However, currently the workbook is protected (except for some cells that can be edited). There are also some hidden columns depending on a cell value, as well as grouped rows.

I started to share the workbook and kept getting runtime errors.
The first error was: Method protect of Object '-worksheet' failed.
This relates to the following code, which I keep in ThisWorkbook - workbook open. This is a peice of code that enables outlining in a protected workbook/worksheet, which is otherwise not permitted in protected sheets.

Dim wksht As Worksheet
For Each wksht In ThisWorkbook.Sheets
With wksht
.Unprotect "*****"
.EnableOutlining = True
.Protect "*****", Contents:=True, userInterfaceOnly:=True
End With
Next wksht

The second error I got was: Hidden property of the range class?!?!
It relates to the following code, which looks up a cell (either 'Yes' or 'No') to either hide or unhide the columns.

If Sheets("Start").Range("U4").Value = "OFF" Then
Sheets("FTE Forecast").Columns("R:AD").EntireColumn.Hidden = True
Else
Sheets("FTE Forecast").Columns("R:AD").EntireColumn.Hidden = False
End If

Once these two peices of code are removed the share file works properly.
My question is - is there anyway I can create a work around so that I can still have the same capability/functionaility with different code, so that a shared workbook that is protected can have hidden columns and grouped rows?

Thanks in advance.

Don't worry. I found some code that let's you enable grouping/outlines as well as hidden columns/rows in a shared and protected workbook.

Here it is for those interested:

Private Sub Workbook_Open()

' check shared and stop sharing
With ActiveWorkbook
If .MultiUserEditing Then
Application.DisplayAlerts = False
.ExclusiveAccess
Application.DisplayAlerts = True
End If
End With

'enter code to enable outlining
'enter code for hidden columns/rows

'resave with sharing switched on
With ActiveWorkbook
Application.DisplayAlerts = False
.SaveAs Filename:=.FullName, AccessMode:=xlShared
Application.DisplayAlerts = True
End With

End Sub