title: VBA Unwanted loop through worksheets
body: <p>I have used this site quite a bit but this is the first question i have posted, hopefully I can give enough detail. I cannot find any relevant answers because no matter what i search, I get various answers relating to looping code.</p>
  
  <p>Some background: 
  I have designed an excel document to track some items in my workplace (hereafter referred to as Master Document). As the previous tracker allowed users to edit anything at any time, I have used forms to ensure all information is entered correctly and stored securely. For each item in the Master Document there is a separate excel workbook (hereafter referred to as Item Document). </p>
  
  <p>There are a number of sheets in the Master Document which run code everytime they are activated (because they need to update).</p>
  
  <p>As there is some VBA code in every Item Document which is crucial in syncing data with the Master Document, I have added a Warning worksheet which is shown when the Item Document is opened without macros. This involved using the workbook open, before save and after save events to ensure only the Warning is shown without macros. Here is the code for each event (placed in ThisWorkbook Module obviously)</p>
  
  <pre><code>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  
      Auto_Open
  
      'This is for sync (Master Document checks for text file to see if any changes have been made to Item Document)
      If booChange = True Then
          Dim oFile As Object
          Set oFile = fso.CreateTextFile(strTextFile)
          SetAttr strTextFile, vbHidden
          booChange = False
      End If
  
      'Turn off Screen Updating
      Application.ScreenUpdating = False
  
      'Show warning sheet
      Sheets("Warning").Visible = xlSheetVisible
  
      'Hide all sheets but Warning sheet
      For Each sh In ThisWorkbook.Worksheets
          If Not sh.Name = "Warning" Then sh.Visible = xlVeryHidden
      Next sh
  
  End Sub
  
  Private Sub Workbook_AfterSave(ByVal Success As Boolean)
  
      'Show all sheets
      For Each sh In ThisWorkbook.Worksheets
          sh.Visible = xlSheetVisible
      Next sh
  
      'Hide the warning sheet
      Sheets("Warning").Visible = xlVeryHidden
  
      'Return focus to the main page
      ThisWorkbook.Worksheets(1).Activate
  
      'Turn on Screen Updating
      Application.ScreenUpdating = True
  
      ThisWorkbook.Saved = True
  
  End Sub
  
  Private Sub Workbook_Open()
  
      'Turn off Screen Updating
      Application.ScreenUpdating = False
  
      'Show all sheets
      For Each sh In ThisWorkbook.Worksheets
          sh.Visible = xlSheetVisible
      Next sh
  
      'Hide the warning sheet
      Sheets("Warning").Visible = xlVeryHidden
  
      'Return focus to the main page
      ThisWorkbook.Worksheets(1).Activate
  
      'Turn on Screen Updating
      Application.ScreenUpdating = True
  
      ThisWorkbook.Saved = True
  
  End Sub
  </code></pre>
  
  <p>And just for completeness, here is all code in Module1 of Item Document</p>
  
  <pre><code>'Declarations
  'Strings
  Public strSourceFolder As String
  Public strTextFile As String
  
  'Other
  Public fso As FileSystemObject
  Public booChange As Boolean
  Public wsFlow As Worksheet
  
  'Constants
  Public Const strURNSheetName = "Part 1 Plant Flow Out Summ"
  
  Sub Auto_Open()
  
      Set fso = CreateObject("Scripting.FileSystemObject")
      Set wsFlow = ThisWorkbook.Worksheets(strURNSheetName)
      strSourceFolder = fso.Getfile(ThisWorkbook.FullName).ParentFolder.Path
      strTextFile = fso.BuildPath(strSourceFolder, ThisWorkbook.Worksheets(strURNSheetName).Range("W2").Value &amp; ".txt")
  
  End Sub
  </code></pre>
  
  <p>When an item is created in the Master Document using the 'frmNewEntry' form the info is checked and entered into the Master Document then a template Item Document is opened and saved with a new unique filename. It is then unprotected, updated with the new information, protected, saved and closed. The Master Document is then saved. Code follows (edited to omit lengthy formatting and data entry):</p>
  
  <p>Form Code:</p>
  
  <pre><code>Private Sub btnSave_Click()
      'Values on form are verified
      'Master Document sheet is unprotected, formatted and data entry occurs
  
      'Clear Userform and close
      For Each C In frmNewEntry.Controls
          If TypeOf C Is MSForms.ComboBox Then
              C.ListIndex = -1
          ElseIf TypeOf C Is MSForms.TextBox Then
              C.Text = ""
          ElseIf TypeOf C Is MSForms.CheckBox Then
              C.Value = False
          End If
      Next
      frmNewEntry.Hide
  
      'Create filepaths
      Create_Filepath
  
      'Some hyperlinks are added and the Master Document worksheet is protected again
  
      'Create Flowout Summary
      Create_Flowout_Summary
  
      'Update Flowout Summary
      Update_Flowout_Summary
  
      'Turn on screen updating
      Application.ScreenUpdating = True
  
      'Update Activity Log
      Update_Log ("New: " &amp; strNewURN)
  
      Debug.Print "Before Save Master"
  
      'Save tracker
      ThisWorkbook.Save
  
      Debug.Print "After Save Master"
  
  End Sub
  </code></pre>
  
  <p>Module1 Code:</p>
  
  <pre><code>Public Sub Create_Flowout_Summary()
  'Create a new flowout summary from the template
  
      'Turn off screen updating
      Application.ScreenUpdating = False
  
      'Check if workbook is already open
      If Not Is_Book_Open(strTemplate) Then
          Application.Workbooks.Open (strTemplatePath)
      End If
  
      Debug.Print "Before SaveAs Create"
  
      'Save as new flowout summary
      Application.Workbooks(strTemplate).SaveAs fileName:=strFilePath
  
      Debug.Print "After SaveAs Create"
  
      'Close Document Information Panel
      ActiveWorkbook.Application.DisplayDocumentInformationPanel = False  'Doesn't seem to work
  
      'Turn on screen updating
      Application.ScreenUpdating = True
  
  End Sub
  
  Public Sub Update_Flowout_Summary()
  'Update the flowout summary for current call
  
      Dim wsURN As Worksheet
  
      Set wsURN = Workbooks(strFileName).Worksheets(strWsURNName)
  
      'Unprotect Flowout Summary worksheet
      wsURN.Unprotect "Flowout Summary"
  
      'Write values to flowout summary
  
      'Protect Flowout Summary worksheet
      wsURN.Protect "Flowout Summary", False, True, True, True, True
  
      Debug.Print "Before Save Update"
  
      'Save flowout summary
      Application.Workbooks(strFileName).Save
  
      Debug.Print "After Save Update"
  
      'Close Document Information Panel
      ActiveWorkbook.Application.DisplayDocumentInformationPanel = False
  
      'Turn on screen updating
      Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>Problem detail:
  When I create a new entry it is taking a very long time, I accidentally discovered that the Master Document is running the code in every sheet activate event (mentioned above) (I had a diagnostic msgbox in one of the sheets which mysteriously appeared when i created a new entry)
  I have therefore drawn the conclusion that the code is somehow activating every worksheet but have no idea why....</p>
  
  <p>Any help will be much appreciated, and if i have missed anything out that may help in diagnosing just let me know.</p>
  
  <p>EDIT: The other strange phenomenon is that this does not happen when I try to step through the code to find exactly where the activate events are being triggered.</p>
  
  <p>EDIT: Code in the worksheet activate event</p>
  
  <pre><code>Private Sub Worksheet_Activate()
  
      'Turn off Screen Updating
      Application.ScreenUpdating = False
  
      'Simply writes data to the sheet (excluded because it is lengthy)
  
      'Turn on Screen Updating
      Application.ScreenUpdating = True
  
      wsMyCalls.Protect Password:=strPassword
  
      Debug.Print "wsMyCalls"
  
      MsgBox "This sheet uses your username to display any calls you own." &amp; vbNewLine &amp; _
      "It relies on the correct CDSID being entered for owner." &amp; vbNewLine &amp; vbNewLine &amp; _
      "Regards" &amp; vbNewLine &amp; _
      "Your friendly spreadsheet administrator", vbOKOnly, "Information"
  
  End Sub
  </code></pre>
  
  <p>EDIT: I added some Debug.Prints to the code (above) and this is what i got.</p>
  
  <ul>
  <li>Before SaveAs Create </li>
  <li>After SaveAs Create</li>
  <li>Before Save Update</li>
  <li>After Save Update</li>
  <li>Before Save Master</li>
  <li>After Save Master</li>
  <li>wsMyCalls</li>
  </ul>
  
  <p>This shows that the code is executing between Debug.Print "After Save Master" and an End Sub. There is no code in there???</p>
  
  <p>Thanks</p>