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 it 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 LongThere 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 (https://www.gmayor.com/insert_content_control_addin.htm) 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.

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

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.

23310

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

[gmayor]

While Greg's macro calculates the whole document and colours cells, you can use the count part to update the counts in real time as the controls are changed (note they don't run the exit code until you actually click outside the control) if you add content controls at the end for the counts (see attached).

FWIW I used my add-in to convert the controls from combo boxes to list boxes and add the extra controls (and in the process found a bug in my add-in which I have fixed):banghead:

[RKFerguson]

Thank you folks very much. Especially for the sample code. I would like to change the color of the cells as the User makes their choice, but there are couple of things in the example macros I do not understand, as well as the VBEditor. What is the purpose of the DoEvents entry and the Lbl_Exit? Also, where in the document would I place the macro, in the document or in a module? Would it automatically run or need I launch it in some way?

Good to know your Add-In is fixed. I don't feel so bad leaning on you so much!

VR,

Fergie

[gmaxey]

Graham has already given you the code for changing the cells as the user makes selection (and exits the cell). It is in the ThisDocument module. I would have written it like this


Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean)
If oCC.Type = 3 Or oCC.Type = 4 Then
Select Case oCC.Range.Text
Case "Y": oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorGreen
Case "N": oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorRed
Case "NA": oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorBlue
Case Else: oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorWhite
End Select
UpdateTotals
End If
lbl_Exit:
Exit Sub
End Sub


When you don't understand the purpose of something. Select it e.g., DoEvents and press F1. That opens any associated help.

lbl_Exit is just a label. I use it and so does Graham. It is a coding style. When actually serving a purpose it may look like this:


Sub Demo()
Dim oRng As Range
Set oRng = ActiveDocument.Range
On Error GoTo lbl_Err:
'Force and error for demo
Err.Raise 6
lbl_Exit:
Set oRng = Nothing
Exit Sub
lbl_Err:
MsgBox Err.Number & " " & Err.Description
Resume lbl_Exit
End Sub

[gmayor]

Just for fun, I agree with Greg's modifications, though changing the code to


Option Explicit

Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean)
If oCC.Type = 3 Or oCC.Type = 4 Then
Select Case oCC.Range.Text
Case "Y"
oCC.Range.Cells(1).Shading.BackgroundPatternColor = &H50B000
oCC.Range.Font.ColorIndex = wdWhite
Case "N"
oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorRed
oCC.Range.Font.ColorIndex = wdWhite
Case "NA"
oCC.Range.Cells(1).Shading.BackgroundPatternColor = &HC07000
oCC.Range.Font.ColorIndex = wdWhite
Case Else
oCC.Range.Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
oCC.Range.Font.Color = &H808080
End Select
UpdateTotals
End If
lbl_Exit:
Exit Sub
End Subwill make the selections more readable and match the colours I used in the attachment I returned earlier.
The code that updates the totals in that attachment can also be tweaked further

Option Explicit

Sub UpdateTotals()
'A basic Word macro coded by Greg Maxey, http://gregmaxey.com/word_tips.html, 12/2/2018
'Graham Mayor - https://www.gmayor.com - Last updated - 04 Dec 2018

Dim lngY As Long, lngN As Long, lngNA As Long, lngNullorInvalid As Long
Dim oCC As ContentControl
For Each oCC In ActiveDocument.Range.ContentControls
If oCC.Type = 3 Or oCC.Type = 4 Then
Select Case oCC.Range.Text
Case Is = "Y": lngY = lngY + 1
Case Is = "N": lngN = lngN + 1
Case Is = "NA": lngNA = lngNA + 1
Case Else: lngNullorInvalid = lngNullorInvalid + 1
End Select
End If
DoEvents
Next oCC

ActiveDocument.SelectContentControlsByTitle("Not Selected").Item(1).Range.Text = lngNullorInvalid
ActiveDocument.SelectContentControlsByTitle("Yes").Item(1).Range.Text = lngY
ActiveDocument.SelectContentControlsByTitle("No").Item(1).Range.Text = lngN
ActiveDocument.SelectContentControlsByTitle("NA").Item(1).Range.Text = lngNA

lbl_Exit:
Set oCC = Nothing
Exit Sub
End Sub

[RKFerguson]

Thank you, very much.