Excel: VBA to preserve formulas with clear contents events from other users
<h2>The problem</h2>

<p>I manage a weighted decision matrix in an Excel file for a team of 15 people. There are 10 columns in the file that contain formulas. The formulas calculate a total weighted score based on the options users select in other cells. </p>

<p>Sometimes a user needs to remove an entry. Common removal methods for my team include selecting an entire row and doing 1 of the following:</p>

<ul>
<li>Pressing the delete key.</li>
<li>Right-clicking and selecting "Clear Contents".</li>
<li>Selecting "Clear Contents" from the Editing group on the Home tab.</li>
</ul>

<p>All of these methods also clear the formulas that calculate the weighted scores.</p>

<p>The methods described above are intuitive for most of my team, so I’d like to find a way to let people use these methods without clearing the formulas.</p>

<h2>The dream</h2>

<p>Allow users to clear everything <em>but the formulas</em> when they use any of the methods described above to clear cells.  </p>

<h2>Things I've tried</h2>

<ul>
<li><p>Locking just the formula cells. This prevents users from clearing the formulas, but they can’t clear contents in the other cells when they select the whole row because the formula cells in that row are locked.</p></li>
<li><p>The macro below. The macro will clear constants and spare the formulas, but I can't get it to trigger automatically when a "clear contents" event happens. Expecting all the members of my team to run this macro instead of using their regular method for clearing contents is a fool's errand I fear.</p>

<pre><code>Sub ClearOnlyValues()
  Dim Cel As Range

  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

  For Each Cel In Selection
    If InStr(Cel.Formula, "=") = 0 And Cel.Value &lt;&gt; "" Then
     Cel.ClearContents
  End If
  Next Cel

  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic

End Sub
</code></pre></li>
<li><p>Google. The only macros I could find all had to be run manually each time you want to clear contents and countless posts demo'ing the Go To Special method of clearing constants. See fool's errand above. If they can't clear cells intuitively, we'll descend into chaos. Cats and dogs living together. It'll be anarchy.</p></li>
<li><p>Advanced Excel users who are way smarter than me. They're all stumped.  </p></li>
</ul>

<p>If you're still with me, thanks for taking the time. </p>