Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shared Workbooks-shared headers and footers | New Users to Excel | |||
Shared VBA code between workbooks | Excel Programming | |||
Anyone ever try shared workbooks? | Excel Programming | |||
suddenly my excel workbooks are "shared workbooks" | Excel Discussion (Misc queries) | |||
Using VBA with Shared Workbooks | Excel Programming |