Macro to check dropdown value and increment a counter

rkferguson · #1 01-03-2015, 08:44 AM

Looking for a way to build a macro to run "On exit" that checks the value selected in a dropdown, and then increments a counter based on that value. For example, a WORD template has several dropdown boxes and each box has three choices from which to select - A, B, or C.

If the user selects "A", a counter for "A" gets incremented by 1 eg., Cntr_A = Cntr_A + 1. Presuming a need for a variable to identify which dropdown/bookmark the macro is working. That is if the template has 50 dropdown boxes, it would not make sense to write a separate macro for each one. There must be a way to tell the macro which bookmark it is working.

Please provide sample code or pointers to code or reference material from which I can glean a method for solving this.

I am working with versions of Word ranging from 2003 to 2013.

Thanks much,

Ferg

gmayor · #2 01-04-2015, 06:22 AM

While what you ask is possible. It requires a lot of error checking to ensure that the count is a true reflection of the selections. It requires the user to tab through each to increment the values. Simply leaving the values at their defaults will not increment the count.

I believe it would make more sense to use a macro to count the values after they have all been completed. That way there should be no false values. e.g.

Code:

Sub CountValues()
Dim A_Count As Long
Dim B_Count As Long
Dim C_Count As Long
Dim oFF As FormField
    A_Count = 0
    B_Count = 0
    C_Count = 0
    For Each oFF In ActiveDocument.FormFields
        If oFF.Type = wdFieldFormDropDown Then
            Select Case oFF.Result
                Case "A": A_Count = A_Count + 1
                Case "B": B_Count = B_Count + 1
                Case "C": C_Count = C_Count + 1
                Case Else
            End Select
        End If
    Next oFF
    MsgBox "A - Selected " & A_Count & vbCr & _
           "B - Selected " & B_Count & vbCr & _
           "C - Selected " & C_Count
lbl_Exit:
    Exit Sub
End Sub

macropod · #3 01-04-2015, 03:08 PM

Cross-posted at: https://social.technet.microsoft.com...003?forum=word
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

rkferguson · #4 01-06-2015, 02:47 PM

Gmayor's macro works beautifully. Now I am wondering why I can't change the font color on the dropdown selection based on its value.

I tried oFF.Result.font.color = vbRed, for example, but got a compile time error of "Invalid Qualifier". Presumably because the way the code is getting to the dropdown value I cannot use the "." notation to manipulate it. Is there a decent text/reference that explains (with examples) how to properly set up and manipulate the font attributes for the dropdown value?

Thanks much,

Fergie

jjfreedman · #5 01-06-2015, 06:27 PM

The cause of the error is that oFF.Result is a String value, which has no attributes. That's why the "." notation doesn't work. The thing associated with the formfield that does have a .Font.Color attribute is the oFF.Range member.

If you just try to set the color, you'll get a runtime error saying that it can't be changed because it refers to a protected part of the document. To be successful, you have first unprotect the document, change the color, and then reprotect the document. Try this:

Code:

Sub CountValues()
Dim A_Count As Long
Dim B_Count As Long
Dim C_Count As Long
Dim oFF As FormField
    A_Count = 0
    B_Count = 0
    C_Count = 0
    For Each oFF In ActiveDocument.FormFields
        If oFF.Type = wdFieldFormDropDown Then
            ActiveDocument.Unprotect
            Select Case oFF.Result
                Case "A"
                    A_Count = A_Count + 1
                    oFF.Range.Font.Color = wdColorRed
                Case "B"
                    B_Count = B_Count + 1
                    oFF.Range.Font.Color = wdColorBlue
                Case "C"
                    C_Count = C_Count + 1
                    oFF.Range.Font.Color = wdColorGreen
                Case Else
            End Select
            ActiveDocument.Protect _
                Type:=wdAllowOnlyFormFields, _
                NoReset:=True
        End If
    Next oFF
    MsgBox "A - Selected " & A_Count & vbCr & _
           "B - Selected " & B_Count & vbCr & _
           "C - Selected " & C_Count
lbl_Exit:
    Exit Sub
End Sub

rkferguson · #6 01-06-2015, 06:41 PM

JJ,

The sample code doesn't show it, but the macro in the document does include code to unlock the document, then lock it again once the tallies are complete. The counters work just fine. I'm just not able to figure a way to access the text from the drop down to change the color.

Thanks much.

Fergie

gmayor · #7 01-06-2015, 10:55 PM

The macro is intended to be run separately. If you want a macro to run on exit from the form field(s) then run the following on exit from and on entry to each form field

Code:

Option Explicit
Public rngFF As Word.Range
Public fldFF As Word.FormField

Public Sub DropDownColour()
Dim bProtected As Boolean
    With GetCurrentFF
        If .Type = wdFieldFormDropDown Then
            'Unprotect the file
            If ActiveDocument.ProtectionType <> wdNoProtection Then
                bProtected = True
                ActiveDocument.Unprotect Password:=""
            End If

            Select Case .Result
                Case "A": .Range.Font.ColorIndex = wdRed
                Case "B": .Range.Font.ColorIndex = wdYellow
                Case "C": .Range.Font.ColorIndex = wdGreen
                Case Else
            End Select
            'Reprotect the document.
            If bProtected = True Then
                ActiveDocument.Protect _
                        Type:=wdAllowOnlyFormFields, _
                        NoReset:=True, _
                        Password:=""
            End If
        End If
    End With
lbl_Exit:
    Exit Sub
End Sub

Public Function GetCurrentFF() As Word.FormField
    Set rngFF = Selection.Range
    rngFF.Expand wdParagraph
    For Each fldFF In rngFF.FormFields
        Set GetCurrentFF = fldFF
        Exit For
    Next fldFF
lbl_Exit:
    Exit Function
End Function

rkferguson · #8 01-07-2015, 10:12 AM

So I was complicating things by trying to modify the "Counting" macro, to change font colors as well.

As I understand you suggestion, I should leave the counting macro alone (as I have it now, I am calling it via the quick access tool bar after the template is populated), and use the color changing macro on entry and exit from each bookmark/dropdown.

May I ask why it should run on both entry and exit? It seems to me, the uninitiated, that running it on entry would cause issues or at least redundancy. That is, if run "on entry" the macro will change the font color per the initial value of the drop down "result", and on exit, it would change the font color per the value the user selects from the drop down list.

Would it not be sufficient to just run it on exit? I am not challenging your guidance/advice, just trying to fully understand the logic.

Thank you, very much.

Fergie

gmayor · #9 01-07-2015, 11:08 PM

Correct, leave the counting macro as it stands.

You need to run the additional macro on entry also because you cannot guarantee that users will tab through the form. The on exit macro only fires when you tab out of the field. This will ensure the field is updated no matter where the user goes next - try it both ways and you will see what I mean.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Dropdown list, Macro	shield5	Excel Programming	7	10-27-2013 01:51 AM
macro not working (on click increment)	gsrikanth	Excel Programming	1	07-12-2012 07:40 AM
Check box macro help needed	Aflac	Word	4	03-24-2012 07:11 PM
Macro to populate a text form field based on dropdown selection	koloa	Word	0	10-20-2011 11:52 AM
Check Box Macro	Johnny thunder	Word VBA	12	04-07-2011 04:47 PM