Macro not functioning properly

[RKFerguson]

Hi folks,

I have a Word VBA macro used to tally and display some totals at the bottom of a document. It worked fine in earlier versions of Word but is having issues in Word 2016. Would appreciate any guidance you may offer. I have attached the macro for your perusal.

Thanks,

Fergie

[gmaxey]

Fergie,

I think we could peruse until the cows come home but without some clue as to what the entering arguments (text in the document, what you pass as arguments) it is hard to guess what the issues are. Step through the code one line at a time using the F8 key and see on which line it fails. Work from there.

[RKFerguson]

Fergie,

I think we could peruse until the cows come home but without some clue as to what the entering arguments (text in the document, what you pass as arguments) it is hard to guess what the issues are. Step through the code one line at a time using the F8 key and see on which line it fails. Work from there.

Hi Greg,

Thanks for the quick response.

The input arguments are "Y", "N", or "NA". But i

t isn't failing in that sense. The macro completes but it doesn't give the results it once did. Instead of the line displaying with specific colors for the numberals, it displays the line with random characters or portions of the string in colors.

Fergie

[macropod]

Without actually seeing the problem document, it can be difficult for anyone to diagnose the issue. Can you attach that document to a post with some representative data (delete anything sensitive)?

[RKFerguson]

Without actually seeing the problem document, it can be difficult for anyone to diagnose the issue. Can you attach that document to a post with some representative data (delete anything sensitive)?

Please see the attached. The input comes from the content controls at the right of each "paragraph".

Fergie

[macropod]

Since the document is in docx format and this format can't contain any macros, we are none the wiser as to what calls your displayTotal sub or what the variables passed to that sub might be. We need a document containing all the code that drives the process...

[RKFerguson]

Well that is interesting. I saved the file as .dotm but when I open it, and then open the VBA editor, there is no code.... I need to take a step back, lick my wounds, and see if I can get the file to the same point I had it prior to asking for help. Thank you

[gmayor]

Chances are that the code is in your normal template (or the template from which the document was created).

[gmaxey]

Ok. When I ran your code and error tripped on:

.Range(startingCount, newCount).Select

So obviously the right text or amount of text wasn't in the document. Yes, do as Paul and Graham have advised.

[RKFerguson]

Curious. Why would you get that error when you run the code, but I do not? I am not able to step through the macro using F8. When I try, my screen goes black for a second and I hear a beep, but nothing happens, no error messages. Could I have something hosed in my environment that would cause that behavior? The .dotm file that I attempted to upload has all of the code, some in "ThisDocument" and the rest in Module1. When you say your ran my code, did that .dotm file actually upload despite the error?

[gmaxey]

I don't have your .dotm file. The code I ran was the code I pulled from text in Sub Display Total.docx. I simply copied it from the text, pasted it in a Standard VBA module, passed 1, 2, 3, 4 as arguments and stepped through using F8. It error ed on the line indicated.

[gmayor]

According to my count, there are 111 content control text boxes, 1257 check boxes and 383 combo boxes none of them are titled or tagged and so it is difficult to imagine, without access to the rest of the code how those controls are addressed to give the required totals, without seeing all the code.There is also a typo in the line

Dim newCount, yStart, yEndm, nStart, nEnd, naStart, naEnd As Long

There should not be an 'm' at the end of the variable yEndm.

While looking at your sample form it struck me that you had many hundreds of untagged and untitled controls, which while permitted, does make things more difficult especially when data is to be extracted from the completed form. You also used combo boxes, where I suspect list boxes would have been more appropriate as you don't really want users adding their own responses to a form you wish to record totals from.

Changing hundreds of these items would be a long and thankless task, and is undoubtedly an issue that will also affect others so I have added the functionality in question to my Insert Content Control add-in which provides a wide variety of insertion and editing functions, which you may find useful.

[RKFerguson]

You have punctuated what I have expressed, I am not a VBA developer nor am I familiar with Word objects and content controls. I was in need of a way to tally 'Yes' and 'No' responses within the document and surmised that I could do so with the content controls. I did not think I needed to label them as I was only interested in the Yes/No responses at the time. I noticed the "m" in that variable Friday evening, correcting it did not change the results. I will take a look at your Insert Content Control add-in and I appreciate your feedback and guidance.

Also, I don't know that it matters at this point, but I was able to post the original .dotm last night. It is nearly 800 pages as it includes all of the security controls for interrogation. Why it was "valid" and the file I was trying to post wasn't valid is a puzzle. The file I tried to upload was a subset of one of the families of security controls, the "AC" family. It was taking a long time for the 800 page file to "process", so I thought I would create a separate file for each of the 18 families.

Fergie

[gmaxey]

Fergie,

We can all understand that you are not a VBA developer. You should understand that we are not mind readers or prophets. When you post and say my code doesn't work without making least attempt to explain what specifically doesn't work or what exactly it is supposed to do then you simply frustrate anyone was once or still willing to help you!

In the very beginning, I asked you what the entering arguments are. You replied "Y", "N", "N\A". Well from even a cursory look at the example of code you did provide anyone could tell the procedure take four arguments (not three) and there is absolutely nothing in that code that tallies anything.

[IMG]file:///C:/Users/gmaxe/AppData/Local/Temp/SNAGHTML5cabb1a.PNG[/IMG]

While Graham is correct about the benefits of properly titling and tagging CCs, it isn't absolute and certainly not required in the case of the document you posted the other day with the 1000+ CCs. Try this:

Sub ScratchMacro()'A basic Word macro coded by Greg Maxey, http://gregmaxey.com/word_tips.html, 12/2/2018
Dim lngY As Long, lngN As Long, lngNA As Long
Dim oCC As ContentControl
Dim oRng As Range
  For Each oCC In ActiveDocument.Range.ContentControls
    Select Case oCC.Type
      Case 8
        If oCC.Checked Then
          lngY = lngY + 1
        Else
          lngN = lngN + 1
        End If
      Case 3, 4
        Select Case oCC.Range.Text
          Case Is = "Y": lngY = lngY + 1
          Case Is = "N": lngN = lngN + 1
          Case Else: lngNA = lngNA + 1
        End Select
    End Select
  Next oCC
  Set oRng = ActiveDocument.Range
  oRng.Collapse wdCollapseEnd
  oRng.InsertBefore vbCr & "There are " & lngY & " checked or Y responses"
  oRng.Font.ColorIndex = wdBlue
  Set oRng = ActiveDocument.Range
  oRng.Collapse wdCollapseEnd
  oRng.InsertBefore vbCr & "There are " & lngN & " unchecked or N responses."
  oRng.Font.ColorIndex = wdRed
  Set oRng = ActiveDocument.Range
  oRng.Collapse wdCollapseEnd
  oRng.InsertBefore vbCr & "There are " & lngNA & " blank or invalid responses"
  oRng.Font.ColorIndex = wdGreen
lbl_Exit:
  Exit Sub
End Sub

[RKFerguson]

I attempted to post the .dotm containing all of the code but the interface flagged it as invalid. When you asked about input I listed the Yes, No, and NA because they are the only input from the User perspective. It is certainly not my intention to aggravate those from whom I am seeking assistance. The "scratch" macro you posted looks as if it would do what I need and I will study it and see if I can modify it to also color the cells according to content value. I will also have to study the "Case" function as I do not immediately understand how it discerns the selection. Also, am I correct in thinking that this macro will interrogate every content control in the document, even the checkboxes?

VR,

Fergie

[gmaxey]

The macro processes checkbox, comboboxes (which as Graham has stated in your case should be dropdown lists) and dropdown list. That's it.

I did not ask about input. The code you posted takes four arguments. I asked what those arguments were.

2018-12-02_12-34-56.jpg

For shading see:

Sub ScratchMacro() 'A basic Word macro coded by Greg Maxey, http://gregmaxey.com/word_tips.html, 12/2/2018Dim lngY As Long, lngN As Long, lngNA As Long
Dim oCC As ContentControl
Dim oRng As Range
  For Each oCC In ActiveDocument.Range.ContentControls
    Select Case oCC.Type
      Case 8
        If oCC.Checked Then
          lngY = lngY + 1
        Else
          lngN = lngN + 1
        End If
      Case 3, 4
        Select Case oCC.Range.Text
          Case Is = "Y": lngY = lngY + 1: oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorBlue
          Case Is = "N": lngN = lngN + 1: oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorRed
          Case Else: lngNA = lngNA + 1: oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorGreen
        End Select
    End Select
  Next oCC
  Set oRng = ActiveDocument.Range
  oRng.Collapse wdCollapseEnd
  oRng.InsertBefore vbCr & "There are " & lngY & " checked or Y responses"
  oRng.Font.ColorIndex = wdBlue
  Set oRng = ActiveDocument.Range
  oRng.Collapse wdCollapseEnd
  oRng.InsertBefore vbCr & "There are " & lngN & " unchecked or N responses."
  oRng.Font.ColorIndex = wdRed
  Set oRng = ActiveDocument.Range
  oRng.Collapse wdCollapseEnd
  oRng.InsertBefore vbCr & "There are " & lngNA & " blank or invalid responses"
  oRng.Font.ColorIndex = wdGreen
lbl_Exit:
  Exit Sub
End Sub

[RKFerguson]

Roger that. I erroneously equated the question to the wrong code. I will chalk it up to Old Fart Syndrome. I do not multitask well these days. So it would seem that the code you did see did not include that which incremented the counters that equated to those arguments. I suppose when the .dotm file would not upload I should have copied all of the code into a .txt file and uploaded that, along with an explanation of how/when it was invoked. Duly noted. Thank you.

If I understand the example you provided, the macro is counting not only the combobox responses. I am trying to count the Yes, No, and NA responses in the combo boxes and any Comboboxes that were not selected. I do not need to count the checkboxes or the controls that capture comments from the user. I will study your example and use it to try to get what I need. Thank you very much for your help and your patience.

Fergie

[macropod]

Also, I don't know that it matters at this point, but I was able to post the original .dotm last night. It is nearly 800 pages as it includes all of the security controls for interrogation. Why it was "valid" and the file I was trying to post wasn't valid is a puzzle. The file I tried to upload was a subset of one of the families of security controls, the "AC" family. It was taking a long time for the 800 page file to "process", so I thought I would create a separate file for each of the 18 families.

In that case, you could add the file to a zip archive and upload that. We won't want 800 pages, though - even zipped that's likely to exceed the forum's upload limits by a considerable margin.

[RKFerguson]

I zipped the .dotm file that the interface deemed was invalid. It contains just the AC family of controls. I didn't see the full, 800 page file in my "Manage Attachments" queue nor did I see any errors when I uploaded it... Presume it failed due to excess size.


Fergie

[gmaxey]

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey, http://gregmaxey.com/word_tips.html, 12/2/2018
Dim lngY As Long, lngN As Long, lngNA As Long, lngNullorInvalid As Long
Dim oCC As ContentControl
Dim oRng As Range
  For Each oCC In ActiveDocument.Range.ContentControls
    Select Case oCC.Type
      Case 8
'        If oCC.Checked Then
'          lngY = lngY + 1
'        Else
'          lngN = lngN + 1
'        End If
      Case 3, 4
        Select Case oCC.Range.Text
          Case Is = "Y": lngY = lngY + 1: oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorGreen
          Case Is = "N": lngN = lngN + 1: oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorBlue
          Case Is = "NA": lngNA = lngNA + 1: oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorYellow
          Case Else: lngNullorInvalid = lngNullorInvalid + 1: oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorRed
        End Select
    End Select
  Next oCC
  Set oRng = ActiveDocument.Range
  oRng.Collapse wdCollapseEnd
  oRng.InsertBefore vbCr & "There are " & lngY & " checked or Y responses"
  oRng.Font.ColorIndex = wdGreen
  Set oRng = ActiveDocument.Range
  oRng.Collapse wdCollapseEnd
  oRng.InsertBefore vbCr & "There are " & lngN & " unchecked or N responses."
  oRng.Font.ColorIndex = wdBlue
  Set oRng = ActiveDocument.Range
  oRng.Collapse wdCollapseEnd
  oRng.InsertBefore vbCr & "There are " & lngNA & " NA responses"
  oRng.Font.ColorIndex = wdYellow
  Set oRng = ActiveDocument.Range
  oRng.Collapse wdCollapseEnd
  oRng.InsertBefore vbCr & "There are " & lngNullorInvalid & " blank or invalid responses"
  oRng.Font.ColorIndex = wdRed
lbl_Exit:
  Exit Sub
End Sub