Per Claussen
Member
Hello experts, I'm using this code to protect and hide my formula cells. Works great until I starts a macro CreateNewPost for:
1. create new sheet as copy of a hidden template sheet
2. selecte a range in another sheet and copy the selection to the new sheet
Result: everything hangs a while, until Excel crashes.
Everything works fine if I disable the macro Sub Workbook_SheetSelectionChange.
Is there a way to change the macro to allow my CreateNewPost to execute?
The code below is from http://www.ozgrid.com/VBA/stop-formula-view.htm
1. create new sheet as copy of a hidden template sheet
2. selecte a range in another sheet and copy the selection to the new sheet
Result: everything hangs a while, until Excel crashes.
Everything works fine if I disable the macro Sub Workbook_SheetSelectionChange.
Is there a way to change the macro to allow my CreateNewPost to execute?
The code below is from http://www.ozgrid.com/VBA/stop-formula-view.htm
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rFormulaCheck As Range
On Error Resume Next
Sh.Unprotect Password:="password"
With Selection
.Locked = False
.FormulaHidden = False
End With
If Target.Cells.Count = 1 Then
If Target.HasFormula Then
With Target
.Locked = True
.FormulaHidden = True
End With
Sh.Protect Password:="password", UserInterFaceOnly:=True, AllowFiltering:=True
End If
ElseIf Target.Cells.Count > 1 Then
Set rFormulaCheck = Selection.SpecialCells(xlCellTypeFormulas)
If Not rFormulaCheck Is Nothing Then
With Selection.SpecialCells(xlCellTypeFormulas)
.Locked = True
.FormulaHidden = True
End With
Sh.Protect Password:="password", UserInterFaceOnly:=True, AllowFiltering:=True
End If
End If
On Error GoTo 0
End Sub