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 unique name.
What I would like to do is average all six values and put the result in a text box elsewhere on the form. Ideally, this would update dynamically.
I tried initially to accomplish this with the code below. This almost works. This creates a array for each of the six values [I have only pasted one below] and then populates the value with 1 to 5 depending on the user's selection.
The problem is that the sub resets after each event (On Exit) so the 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 Sub