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

Discussion in 'Word Tables' started by jscranton, Oct 8, 2012.

  1. jscranton

    jscranton Guest

    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
    jscranton, Oct 8, 2012
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.