title: Set password when closing
body: <p>I'm trying to lock all sheets with a password when closing a workbook, but allow filtering and searching on tables.</p>
  <p>I managed to gather the following which works other than setting the password.</p>
  <pre class="lang-vb prettyprint-override"><code>Private Sub Workbook_BeforeClose(Cancel As Boolean)
  'Update by Extendoffice 2018/1/24
  Dim xSheet As Worksheet
  Dim xPsw As String
  xPsw = &quot;***&quot;
  For Each xSheet In Worksheets
      xSheet.Protect xPsw
  Next
  If ActiveSheet.Protection.AllowFiltering = False Then
      ActiveSheet.Protect AllowFiltering:=True
  End If
  End Sub
  </code></pre>
  <p>*** = password<br />
  The above auto locks worksheets and allows filtering as needed but doesn't set the password.</p>
  <p>Excel for office 365, win10 enterprise.</p>