title: Excel fields must add up to 100% and if not spread field values evenly if no entry made
body: <p>I have 5 fields that must equal 100%.</p>
  
  <pre><code>A | B
  -------
  1 | 20%
  2 | 20%
  3 | 20%
  4 | 20%
  5 | 20%
  -------
  Total = 100%
  </code></pre>
  
  <p>If the user clicks B2 and changes the 20% to 25% it should evenly disperse the remaining fields to equal 100% so it would end up like this:</p>
  
  <pre><code>A | B
  -------
  1 | 17.5%
  2 | 30%
  3 | 17.5%
  4 | 17.5%
  5 | 17.5%
  -------
  Total = 100%
  </code></pre>
  
  <p>I found a solution on an excel forum and they show to use this:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  Dim NewValue As Double
  Dim ControlRange As Range
  Dim Item As Range
  Set ControlRange = Range("A1:A4")
  
  If Not Intersect(ControlRange, Target) Is Nothing Then
      'The change occured in one of the cells we want to control
  
      NewValue = (1 - Target.Value) / 3
      For Each Item In ControlRange
          If Not Item.Address = Target.Address Then ' Don't change the cell that was changed
              Item.Value = NewValue
          End If
      Next
  End If
  
  Application.EnableEvents = True
  Set ControlRange = Nothing
  Set Item = Nothing
  End Sub
  </code></pre>
  
  <p>and I would assume that I would only have to alter these lines:</p>
  
  <p><code>Set ControlRange = Range("A1:A4")</code> and <code>NewValue = (1 - Target.Value) / 3</code></p>
  
  <p>Here is the kicker though.  I did Alt+F11, insert module, and pasted that in.  Then I exited to the doc, and did Alt+F8 to view macros and it doesn't show up.  </p>
  
  <p>I read that it won't show up if "private" is in the code but I removed and it still doesn't show.  </p>
  
  <p>How do I make this code apply to a document?  I nearly have the answer but can't find how to make it actually trigger.</p>
  
  <p><strong>EDIT:</strong>
  The advice given below helped to make the sub routine actually work which is great news.  However, I need help with what the function is to actually accomplish.</p>
  
  <p>What works is that if I enter a number into a field, it evenly disperses the % between the remaining fields.  But the goal is that if you do enter a value manually, it KEEPS that value untouched, and any remaining fields would evenly disperse.  Then if you entered into a second field, it keeps the first and second fields untouched.  Changing a third field would keep all 3 of those fields the same. I hope this makes sense.</p>