Thread Tools Display Modes
  #1   Report Post  
jscranton jscranton is offline
Junior Member
Posts: 1
Default 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 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?

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 Sub
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Track changes in a form made to text boxes, drop downs selected, [email protected] Microsoft Word Help 1 April 17th 09 07:22 AM
How do I modify the values in the text or frame box? Bobbie Microsoft Word Help 1 June 22nd 06 05:34 PM
Drop downs and Free text Darius Microsoft Word Help 2 February 17th 06 03:44 PM
The drop down form field is not showing drop downs. Why? mindysue Microsoft Word Help 1 May 26th 05 09:43 PM
drop down box values from excel list sallu Microsoft Word Help 1 May 18th 05 02:17 PM

All times are GMT +1. The time now is 07:35 AM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Word"