#1
|
||||
|
||||
Macro to check dropdown value and increment a counter
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 |
#2
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
||||
|
||||
Cross-posted at: https://social.technet.microsoft.com...003?forum=word
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
||||
|
||||
Macro to check dropdown value and increment a counter
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 |
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
Macro to check dropdown value and increment a counter
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 |
#7
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#8
|
||||
|
||||
Macro to check dropdown value and increment a counter
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 |
#9
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
Tags |
bookmarks, word 2003, word vba macro |
|
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 |