Copy protected sheet

[Per Claussen]

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

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

 

[NARAYANK991]

Hi ,

This is a standard problem ; executing code in an event driven procedure , which then triggers fresh events thus executing the event procedure again , which in turn triggers ....

To avoid this kind of recursive loops , always use an Application.EnableEvents = False in the procedure which has code that can trigger an event ; when exiting the section , use an Application.EnableEvents = True to turn on event detection again.

In your specific case , you have a Workbook_SheetSelectionChange event procedure , which is triggered whenever the activecell location is changed in any sheet tab ; if the code that is being executed within this procedure is going to change the activecell from one address to another , then it will trigger the Workbook_SheetSelectionChange event again , thus re-executing the procedure ; this will go on till Excel crashes.

Nothing is to be done with the event procedure you have posted ; what you should do is include the statements :

Application.EnableEvents = False
.
.
.
Application.EnableEvents = True

within your CreateNewPost macro , so that as soon as the macro starts executing , it disables event detection , and re-enables it only after it has completed all its activities , just before exiting.

Narayan

 

[Debraj]

for more detail.. Check this one..
http://roydebraj.wordpress.com/2013/10/28/working-with-worksheet_change-event/