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



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

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

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?


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
Exit Sub
End If

End Su

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question