Macro not functioning properly

1 Attachment(s)

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

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.

Quote:

Originally Posted by 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.

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

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)?

1 Attachment(s)

Busted

Quote:

Originally Posted by 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)?

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

Fergie

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...

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

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

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.

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?

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.

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

Code:

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.

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

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:

Code:

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

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

1 Attachment(s)

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.

Attachment 23310

For shading see:

Code:

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

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

Quote:

Originally Posted by RKFerguson

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.

1 Attachment(s)

Busted - Macropod

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

Code:

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

1 Attachment(s)

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:

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

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

Code:

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:

Code:

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

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

Code:

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 Sub

will 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

Code:

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

Thank you, very much.