Shared Workbooks & Code

[garygoodguy]

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

However, currently the workbook is protected (except for some cells tha
can be edited). There are also some hidden columns depending on a cel
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
protected workbook/worksheet, which is otherwise not permitted i
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' o
'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 work
properly.
My question is - is there anyway I can create a work around so that
can still have the same capability/functionaility with different code
so that a shared workbook that is protected can have hidden columns an
grouped rows?

Thanks in advance

 

[garygoodguy]

Don't worry. I found some code that let's you enable grouping/outline
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