Formulas across sheets not calcing

[Thackers]

Hi,
All of a sudden my workbook won't calc - specifically, formulas referring to data from other sheets within the workbook won't calc (irrespective of whether its set to manual or auto calc). I've been working on this workbook for weeks and it has just happened today out of the blue.

I guess today I have done the following:
- Create a macro which applies to some of the sheets when they are activated, which "protects" them, but also allows the group/outline functionality to work (using an "enable outlining" code).
- Add in the grouping/outlining mentioned above.

Any ideas of the reason for this and more importantly, a solution?? I am completely screwed if I can't resolve it.

 

[Jerry Sullivan]

It's possible that your event code is causing the problem (for example, continuously looping so no other operations can take place).

I suggest you start by setting Application.EnableEvents=False

If you still have the problem, try to isolate the problem by:
Trying the workbook on another computer
Copying individual sheets into a new book and seeing if just one sheet is causing the problem.
Opening a new workbook and seeing if it has the same problem


Let us know if those steps provide more clues.

 

[Thackers]

Some of my code seemed to be the problem so I have removed it (although that code was in place in order to allow outlining to work when the sheets were locked). I have just changed the bracketed term below from True to False.

Private Sub Worksheet_activate()
Protect Password:="", UserInterfaceOnly:=True
EnableOutlining = [False]
End Sub

But I am now searching for a new solution as to how the user can hide/unhide rows and columns when the sheet is locked (someone suggested something at a high level but I couldn't work it out).

 

[Jerry Sullivan]

But I am now searching for a new solution as to how the user can hide/unhide rows and columns when the sheet is locked (someone suggested something at a high level but I couldn't work it out).

Try removing the Worksheet.Activate code you have and pasting this code into the ThisWorkBook module

Private Sub Workbook_Open()
    Const strPW As String = "MyPassword"
 
    With Sheets("UserEntry")
        .Unprotect Password:=strPW
        .Protect Password:=strPW, UserInterfaceOnly:=True, _
            DrawingObjects:=True, Contents:=True, Scenarios:=True
        .EnableSelection = xlNoRestrictions
        .EnableOutlining = True
    End With
End Sub

 

[Thackers]

Thanks - I have now added some additional code to allow the user to hide/unhide rows and columns as well:

AllowFormattingColumns:=True, AllowFormattingRows:=True

One more thing - the user will be altering the name of sheets, so I can't have the sheet names in the coding. Can anyone suggest coding to allow this locking process to apply to all sheets irrespective of their names?

 

[Thackers]

I've worked out the answer based on existing code I have elsewhere - for anyone interested I have used the following code:

Private Sub Workbook_Open()
Const strPW As String = ""
Dim sh1 As Worksheet
Dim sh As Worksheet
Set sh1 = ThisWorkbook.ActiveSheet
For Each sh In ThisWorkbook.Worksheets
With sh
.Activate
.Unprotect Password:=strPW
.Protect Password:=strPW, UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
.EnableSelection = xlNoRestrictions
.EnableOutlining = True
End With
Next
sh1.Activate
End Sub

Thanks for your help guys!

 

[Jerry Sullivan]

Glad that you were able to come up with a solution.

One minor improvement...
You don't need to activate each sheet during the process.
So you can simplify your code to...

Private Sub Workbook_Open()
    Const strPW As String = ""
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        With sh
            .Unprotect Password:=strPW
            .Protect Password:=strPW, UserInterfaceOnly:=True, DrawingObjects:=True, _
                Contents:=True, Scenarios:=True, AllowFormattingColumns:=True,  _
                AllowFormattingRows:=True
            .EnableSelection = xlNoRestrictions
            .EnableOutlining = True
        End With
    Next
End Sub

 

[Thackers]

Excellent thanks!