title: Excel Tracking Changes VBA
body: <p>I have quite big excel with user forms and a lot of VBA going on. I have a problem with locking partially one worksheet and in the same time allowing VBA to track changes.
  At the moment I track changes using the code below - this code is sitting under Microsoft Excel Objects >> Sheet1:</p>
  
  <pre><code>Option Explicit
  Public preValue As Variant
  Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count &gt; 1 Then Exit Sub
      Target.ClearComments
  Target.AddComment.Text Text:="Previous Value was " &amp; preValue &amp; Chr(10) `&amp; "Revised " &amp; Format(Date, "mm-dd-yyyy") &amp; Chr(10) &amp; "By " &amp; Environ`("UserName")
  End Sub
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count &gt; 1 Then Exit Sub
  If Target = "" Then
  preValue = "a blank"
  Else: preValue = Target.Value
  End If
  End Sub
  </code></pre>
  
  <p>And another bit of code is sitting in the folder Forms ( where I created user form to pick up some details from users) and looks like that: </p>
  
  <pre><code>Dim myPassword As String
  myPassword = "123"
  Set wsUK = Worksheets("Sheet1")
  wsUK.Unprotect Password:=myPassword
  ' here there is a lot of code that throws data into Sheet1
  wsUK.Protect Password:=myPassword
  </code></pre>
  
  <p>The problem is that after the user form finished Sheet1 is partially protected, but I still allow users to change data in column H and P. When I try to do it I get Run-time error '1004' The cell or chart that you are trying to change is protected and therefore read-only.</p>