How to average values from 6 different drop downs in a separate text box?

[jscranton]

I have a form with six different content control fields (all drop downs
that allow a user to rate an employee from 1 to 5. Each has a uniqu
name.

What I would like to do is average all six values and put the result i
a text box elsewhere on the form. Ideally, this would updat
dynamically.

I tried initially to accomplish this with the code below. This almos
works. This creates a array for each of the six values [I have onl
pasted one below] and then populates the value with 1 to 5 depending o
the user's selection.

The problem is that the sub resets after each event (On Exit) so th
array only contains the most recently selected value.

Is there a fix to this or another way entirely to accomplish this task?


Code
-------------------

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)


Dim averageValues(0 To 5) As Integer
Dim i As Integer
Dim totalFields As Integer
Dim totalValue As Long



Select Case ContentControl.Title

'this is the first of six drop downs

Case "Project Knowledge"

If ContentControl.Range.Text = "Poor" Then

averageValues(0) = 1

ElseIf ContentControl.Range.Text = "Needs Improvement" Then

averageValues(0) = 2

ElseIf ContentControl.Range.Text = "Satisfactory" Then

averageValues(0) = 3

ElseIf ContentControl.Range.Text = "Good" Then

averageValues(0) = 4

ElseIf ContentControl.Range.Text = "Excellent" Then

averageValues(0) = 5

End If

'loop through the array and find all populated values
For i = 0 To 5

If averageValues(i) <> 0 Then

totalFields = totalFields + 1
totalValue = totalValue + averageValues(i)

End If
Next i


If totalFields <> 0 Then

MsgBox totalValue / totalFields
Else
Exit Sub
End If


End Su