VBA Express
Macro to highlight if certain Word table cells are blank [Archive] - VBA Express Forum

PDA

View Full Version : Macro to highlight if certain Word table cells are blank



g8r777
07-29-2011, 02:39 PM
I have created a form using tables and content controls in Word. I am now trying to have some kind of code that fires when the Word document opens that highlights all of the cells that are blank.

I have a number of issues. Not all blank cells need to be highlighted. The only cells that need to be highlighted are cells that have content controls in them that haven't been touched.

For example, the cells that have rich text or plain text content controls need to be highlighted if no text has been entered. I have already set the placeholder text to a single space so the cell looks blank and doesn't say "Click here to enter text.".

The cells that have drop down content controls should be highlighted if they say "Select One" which is my first entry and what the drop down defaults to.

Ideally some kind other code would fire upon the exiting of the content control that would detect if the control has been changed from the defaults and if so, remove the highlight color.

I'm not sure if this is possible.

I would appreciate any help.

Thank you,

Brian

gmaxey
07-29-2011, 07:24 PM
Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim oTbl As Word.Table
Dim oCell As Word.Cell
Dim oCC As ContentControl
Set oTbl = ActiveDocument.Tables(1)
For Each oCell In oTbl.Range.Cells
If oCell.Range.ContentControls.Count > 0 Then
For Each oCC In oCell.Range.ContentControls
If oCC.ShowingPlaceholderText = True Then
oCell.Shading.BackgroundPatternColor = wdColorBrightGreen
'or
'oCell.Range.HighlightColorIndex = wdBrightGreen
End If
Next oCC
End If
Next oCell
End Sub
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim oRng As Word.Range
If ContentControl.ShowingPlaceholderText = False Then
Set oRng = ContentControl.Range
oRng.Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
End If
End Sub

g8r777
08-01-2011, 07:50 AM
I tired this but keep getting the follwoing message:


Compile error in hidden module: ThisDocument

Any suggestions?

Thank you,

Brian

gmaxey
08-01-2011, 08:03 AM
Brian,

You pasted the code in the projects ThisDocument module? It runs here with no issues. Open the VBA editor, open the ThisDocument module, click Debug, then complile project. What is reporting the error?

g8r777
08-01-2011, 08:39 AM
Greg,

The compile error is with the following code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)

The code Document_ContentControlOnExit is an ambiguous name.

Here is the complete code including the code you provided for the highlighting:

Private Sub ResetButton_Click()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType <> wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:="xxxxxx"
End If
For i = 1 To oFld.Count
With oFld(i)
.Select
If .Name <> "" Then
Dialogs(wdDialogFormFieldOptions).Execute
End If
End With
Next
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="xxxxx"
End If

End Sub


Private Sub Document_ContentControlOnExit( _
ByVal CC As ContentControl, Cancel As Boolean)
If CC.Tag = "longname1" Then
If (Not CC.ShowingPlaceholderText) And _
Len(CC.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line."
CC.Range.Select
End If

End If

If CC.Tag = "longname2" Then
If (Not CC.ShowingPlaceholderText) And _
Len(CC.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line."
CC.Range.Select
End If

End If

If CC.Tag = "longname3" Then
If (Not CC.ShowingPlaceholderText) And _
Len(CC.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line."
CC.Range.Select
End If

End If

If CC.Tag = "shortname" Then
If (Not CC.ShowingPlaceholderText) And _
Len(CC.Range.Text) > 20 Then
MsgBox "Short Name must be 20 characters or less."
CC.Range.Select
End If

End If
End Sub

Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim oTbl As Word.Table
Dim oCell As Word.Cell
Dim oCC As ContentControl
Set oTbl = ActiveDocument.Tables(1)
For Each oCell In oTbl.Range.Cells
If oCell.Range.ContentControls.Count > 0 Then
For Each oCC In oCell.Range.ContentControls
If oCC.ShowingPlaceholderText = True Then
oCell.Shading.BackgroundPatternColor = wdColorBrightGreen
'or
'oCell.Range.HighlightColorIndex = wdBrightGreen
End If
Next oCC
End If
Next oCell
End Sub

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim oRng As Word.Range
If ContentControl.ShowingPlaceholderText = False Then
Set oRng = ContentControl.Range
oRng.Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
End If
End Sub


I tried including your new Private Sub code as an additional If statement in the existing Document_ContentControlOnExit sub but get a 424 error by doing that.

I have also tried changing the name of either of the Subs but either get errors or code that doesn't do anything.

Thank you for your help,

Brian

Frosty
08-01-2011, 01:28 PM
Ambiguous name because you have two subroutines named Document_ContentControlOnExit.

You should be able to take the

Dim oRng As Word.Range
If ContentControl.ShowingPlaceholderText = False Then
Set oRng = ContentControl.Range
oRng.Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
End If

And put it at the bottom of your other function, and have it work.

At the very least, it should compile... and then you may need to set a breakpoint, step through, and see what line (if any) is generating an error. "424 error" is not enough for us to troubleshoot, based on just seeing code. The above code will fail if the content control is not in a table cell, for example.

Frosty
08-01-2011, 01:53 PM
Whoops, it won't work as written because you have the content control named "CC" in one routine, and "ContentControl" in the other...

The proper if statement in the first OnExit routine would be...


If CC.ShowingPlaceholderText = False Then
Set oRng = CC.Range
oRng.Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
End If

It will still generate an error if you have content controls not in a table cell.

gmaxey
08-01-2011, 02:25 PM
It looks like Jason has answered this.



Greg,

The compile error is with the following code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)

The code Document_ContentControlOnExit is an ambiguous name.

Here is the complete code including the code you provided for the highlighting:

Private Sub ResetButton_Click()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType <> wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:="xxxxxx"
End If
For i = 1 To oFld.Count
With oFld(i)
.Select
If .Name <> "" Then
Dialogs(wdDialogFormFieldOptions).Execute
End If
End With
Next
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="xxxxx"
End If

End Sub


Private Sub Document_ContentControlOnExit( _
ByVal CC As ContentControl, Cancel As Boolean)
If CC.Tag = "longname1" Then
If (Not CC.ShowingPlaceholderText) And _
Len(CC.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line."
CC.Range.Select
End If

End If

If CC.Tag = "longname2" Then
If (Not CC.ShowingPlaceholderText) And _
Len(CC.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line."
CC.Range.Select
End If

End If

If CC.Tag = "longname3" Then
If (Not CC.ShowingPlaceholderText) And _
Len(CC.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line."
CC.Range.Select
End If

End If

If CC.Tag = "shortname" Then
If (Not CC.ShowingPlaceholderText) And _
Len(CC.Range.Text) > 20 Then
MsgBox "Short Name must be 20 characters or less."
CC.Range.Select
End If

End If
End Sub

Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim oTbl As Word.Table
Dim oCell As Word.Cell
Dim oCC As ContentControl
Set oTbl = ActiveDocument.Tables(1)
For Each oCell In oTbl.Range.Cells
If oCell.Range.ContentControls.Count > 0 Then
For Each oCC In oCell.Range.ContentControls
If oCC.ShowingPlaceholderText = True Then
oCell.Shading.BackgroundPatternColor = wdColorBrightGreen
'or
'oCell.Range.HighlightColorIndex = wdBrightGreen
End If
Next oCC
End If
Next oCell
End Sub

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim oRng As Word.Range
If ContentControl.ShowingPlaceholderText = False Then
Set oRng = ContentControl.Range
oRng.Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
End If
End Sub


I tried including your new Private Sub code as an additional If statement in the existing Document_ContentControlOnExit sub but get a 424 error by doing that.

I have also tried changing the name of either of the Subs but either get errors or code that doesn't do anything.

Thank you for your help,

Brian

g8r777
08-01-2011, 02:48 PM
The code works with the following caveats.

I have to fire the macro manually by going to the developer tab, selecting the macro and clicking on Run. This is fine for now but ultimate goal is to have everything done automatcially. If a user of the form forgets to fill something in I want it highlighted to bring to their attention and be printed so the next person can see what is missing.

Secondly, this only highlights the empty rich text content controls in the first table. I have multiple tables and would like the empty content controls in all of them highlighted.

I'm also not sure if the code provided will handle my desire to highlight drop down content controls that haven't been changed from the default value of "Select One".

Any thoughts Greg or Jason?

Thank you,

Brian

gmaxey
08-01-2011, 03:00 PM
Done automatically when?

To process all tables instead of just table 1 then:

Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim oTbl As Word.Table
Dim oCell As Word.Cell
Dim oCC As ContentControl
'Set oTbl = ActiveDocument.Tables(1)
For Each oTbl In ActiveDocument.Tables
For Each oCell In oTbl.Range.Cells
If oCell.Range.ContentControls.Count > 0 Then
For Each oCC In oCell.Range.ContentControls
If oCC.ShowingPlaceholderText = True Then
oCell.Shading.BackgroundPatternColor = wdColorBrightGreen
'or
'oCell.Range.HighlightColorIndex = wdBrightGreen
End If
Next oCC
End If
Next oCell
Next oTbl
End Sub

g8r777
08-01-2011, 03:10 PM
Ideally I would like the macro to run upon opening the document, highlight all content controls (since they will either be empty for rich text or default value of "Select One" for drop downs) and stay highlighted until something is entered or one of the drop down choices is selected.

If this was unclear in my original post and requires a completely different approach I apologize.

Thank you,

Brian

gmaxey
08-01-2011, 04:19 PM
Put ScratchMacro in a standard module and call it from a Sub Document_Open() procedure in the ThisDocument module.

g8r777
08-02-2011, 09:25 AM
All of this has worked perfectly for the text content controls. The functionalily is exactly what I need.

I have tried to add to the code to highlight cells with drop down content controls that haven't been changed from the first list entry of "Select One".

Here is the complete code with the section I added:

Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim oTbl As Word.Table
Dim oCell As Word.Cell
Dim oCC As ContentControl
Dim oLE As ContentControlListEntry
Dim oControls As ContentControls
Dim j As Long
'Set oTbl = ActiveDocument.Tables(1)
For Each oTbl In ActiveDocument.Tables
For Each oCell In oTbl.Range.Cells
If oCell.Range.ContentControls.Count > 0 Then
For Each oCC In oCell.Range.ContentControls
If oCC.ShowingPlaceholderText = True Then
oCell.Shading.BackgroundPatternColor = wdColorBrightGreen
'or
'oCell.Range.HighlightColorIndex = wdBrightGreen
End If

Next oCC
End If
For j = 1 To oControls.Count
If oControls(j).Type = wdContentControlDropdownList Then
If oLE = oControls(j).DropdownListEntries(1) Then
oCell.Shading.BackgroundPatternColor = wdColorBrightGreen
End If
End If
Next oCell
Next oTbl
End Sub


My added code is in red.

By adding this I get a Compile Error: Invalid Next control variable reference which highlights the oCell variable at the end.

Any suggestions?

gmaxey
08-02-2011, 09:47 AM
Brain,

Any suggestions...? Yes, look at your code. You initiate a For j loop and then point to a Next oCell. Therein lies part of your trouble.

If your dropdowns are showing placeholder text (i.e., is "Select One" defined as the placeholder text?) Then the code I gave you at the start should work.

To see if a dd is displaying a certain value you need something like:

Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim oTbl As Word.Table
Dim oCell As Word.Cell
Dim oCC As ContentControl
Dim oLE As ContentControlListEntry
Dim oControls As ContentControls
Dim j As Long
Set oTbl = ActiveDocument.Tables(1)
For Each oTbl In ActiveDocument.Tables
For Each oCell In oTbl.Range.Cells
If oCell.Range.ContentControls.Count > 0 Then
For Each oCC In oCell.Range.ContentControls
If oCC.ShowingPlaceholderText = True Then
oCell.Shading.BackgroundPatternColor = wdColorBrightGreen
'or
'oCell.Range.HighlightColorIndex = wdBrightGreen
End If
'You shouldn't need this if the dropdown is showing placeholder text. Is your placeholder text defined as "Select One"
If oCC.Type = wdContentControlDropdownList Then
If oCC.Range.Text = "Select One" Then
oCell.Shading.BackgroundPatternColor = wdColorBrightGreen
End If
End If
Next oCC
End If
Next oCell
Next oTbl
End Sub


Send me your document.

Frosty
08-02-2011, 09:47 AM
I always defer to Greg on content control stuff, as I don't have much (any) experience with them, really. However, this is a concept issue, which I can help with (or, at least try to).

I suggest indenting appropriately (I know it doesn't necessarily translate well in the vba tags here, but your compile issue would become clear if you indented your own code correctly-- you're missing a Next command). Of course, your code would shortly break thereafter for a couple of reasons.

You should also read up on how these logic structures work, or you're going to continue either a) having people write all the code for you or b) making these conceptual mistakes.

You have to understand a little bit of what is going on to be able to modify it. Why are you adding in a new For j=1 to oControls.Count loop? To go iterate through all the controls where?

You are already iterating through each contentcontrol (that's your For Each oCC in yada yada loop).

If you want to check the type of one of those content controls, you need to do it within that loop.

Also, where did you come up with oControls? Nowhere is that set. Dimensioning something is not the same as setting. Dimensioning says "hold this space in memory, I may use it later"... whereas Setting (or "Letting" technically, in some cases) is "use that space for this particular thing here"

I don't mean any of this to sound overly harsh. But it will be very helpful if you read about the two different types of "For" Loops (The differences between For Each... Loops and For...Loops are subtle, but significant).


Dim x as Integer
Dim oPara as Paragraph
For x = 1 To ActiveDocument.Paragraphs
Set oPara = ActiveDocument.Paragraphs(x)
oPara.Alignment = wdAlignParagraphsCenter
Next
will cycle through all the paragraphs in the document and center align each of them... However, that is slightly different than...

Dim oPara as Paragraph
For Each oPara in ActiveDocument.Paragraphs
oPara.Alignment = wdAlignParagraphCenter
Next
Although the same thing is accomplished.

Notice that one you have to actually set your dimensioned variable in one, whereas in the other, it is done for you. But it is still done.

I can post you the solution, but I think you may be able to sort this out on your own, since you're so close already.

Frosty
08-02-2011, 09:56 AM
Greg: sorry for any derailment, you know I like to try to figure out how to explain the concepts. And we posted at the same time, of course :)

gmaxey
08-02-2011, 10:13 AM
Jason,

"..... sound overly harsh." Perhaps I sounded a little "frosty." ;-)

BREAK

Brian,

If you happen to test Jason's examples of For Each and For x = 1 to ... you will need to add .Count at the end of the line and remove the "s" from wdAlignParagraph(s)Center.

Also when using For x = 1 ... to, you often don't need a variable at all and you will learn that if it often better to start with the last member of the collection and work back to 1.

Sub ScratchMacro()
Dim i As Long
For i = 1 To ActiveDocument.Paragraphs.Count
ActiveDocument.Paragraphs(i).Alignment = wdAlignParagraphCenter
Next
End Sub

Sub ScratchMacroII()
Dim i As Long
For i = ActiveDocument.Paragraphs.Count To 1 Step -1
ActiveDocument.Paragraphs(i).Alignment = wdAlignParagraphRight
Next
End Sub

g8r777
08-02-2011, 11:10 AM
Attached is the checklist I am working with. Most of what I need to accomplish is happening correctly.

The drop down selection "Select One" is actually the first list entry and not the placeholder text. I did this because I was able to code a clear form macro that would set all drop downs to a specific list entry. You will see that in the code in Module1.

The only other clearing code I could come up with would reset any modified placeholder text back to "Click here to enter text."

The current code will unlock a protected document just fine but I cannot get the document to reprotect. I'm sure I am missing something simple somewhere. I also realize that it would probably be cleaner to create a macro with the unprotect/reprotect code in it and keep calling that macro instead of repeating the code. I was trying to step through where my reprotection was failing first before I did but cannot figure out why the reprotection isn't happening.

The only other funcationality I would like that I haven't begun to tackle yet is the date text fields at the bottom do not need to be highlighted if blank so they would be an exception to the ScratchMacro code.

Also, for the "Voting Authority" dropdown, if "None" or "Shared" are selected the text content control on the following line should be highlighted until something is entered. If "Sole" is selected then that text content control should not be highlighted as that field isn't relevant at that point. This is essentially a conditional formatting issue for that text field only.

Greg and Jason, you two are VBA geniuses and I truly appreciate all your help so far.

I am by no means an expert and am barely a novice (as is blatantly obvious by this point). You guys have helped me out immensely.

Thank you,

Brian

Frosty
08-02-2011, 12:15 PM
Greg: Ack, thanks for fixing the code. Always dangerous to type stuff from memory. If only that vba tag compiled the code as well as formatted it!

Brian: no problem. Just keep trying to learn. The help file in Word can actually be very useful, once you understand some of the basics. After the basic concepts, everything else is kind of google-able.

gmaxey
08-02-2011, 12:55 PM
Brian,

I don't really know where to begin and I can't hope to pinpoint just where you when wrong.

Somehow, someway you managed to delete the Placeholder text style from your dropdown list. This is why the code I gave you wasn't working.

If you open a new document, insert a dropdown and add items you will notice that if you select the first item "Choose an item" it will appear light grey. This is a result of the placeholder text style.

I had to go through and recreate all of your dropdowns so the Placeholder text would work as designed. You will have to go through and repopulate them.

In the future if you want to change the "first item" then first change the placeholder text using the Design Mode. This is a bit tricky and probably where you went wrong. It is best to start in the existing placeholder text (i.e., at the second letter) make your change and then delete the preceding letter.

Also you should use a template (not a document) for this sort of thing.

Your revised template is attached.

Edit. No it isn't. For some reason I can't attach a template so you will have to save the attached as a tempalte.

g8r777
08-02-2011, 02:46 PM
I have modified the protection code as follows:

If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:="12345"
End If
Call FlagEmptyCCs
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="12345"
End If

The previous code would unlock the document and the document would stay unlocked. The second section of code needed to be changed to see if the doucument was unlocked and if it was, then lock it. The way the code was writting was asking if the document was locked (ProtectionType <> wdNoProtection) to then lock it. A bit redundant.

BREAK

Greg,

The reason I had the drop downs the way I did was because I cannot come up with the proper code for the ClearFields macro to clear the drop down list without resetting its placeholder text back to Choose an Item.

The best I could come up with was to have Select One be one of the list entries and to have code that would set each drop down list back to the first list entry.

You helped me with the code in a previous thread linked below.

http://www.vbaexpress.com/forum/showthread.php?t=38257

I have tried modifying your ClearFields code by adding another case for drop down lists as shown below.

However, when this code runs I get a runtime error 6124 saying I'm not allowed to edit this selection because it is proteced.

The debugger highlights the second oCC.Range.Text = "" code that I added.

I have tried including the unprotect code at the beginning of the macro but that doesn't fix the issue. Also I'm not sure why I don't get the same error for the first case with Rich Text controls. They clear fine.

Sub ClearFields()
Dim oILS As InlineShape
Set oCCs = ActiveDocument.Range.ContentControls
Dim i As Long
For Each oCC In oCCs
Select Case oCC.Type
Case wdContentControlRichText
oCC.Range.Text = ""
End Select

Select Case oCC.Type
Case wdContentControlDropdownList
oCC.Range.Text = ""
End Select

Next oCC
For Each oILS In ActiveDocument.InlineShapes
If oILS.Type = wdInlineShapeOLEControlObject Then
If TypeOf oILS.OLEFormat.Object Is MSForms.OptionButton Then
oILS.OLEFormat.Object.Value = False
End If
End If
Next
Call FlagEmptyCCs
End Sub

I love how you take 2 steps forward and then one back. This is frustrating.

In a bit of good news, I did figure out how to not highlight specific fields by adding the code below. Now I just need to work with the conditional formatting issue.

Sub FlagEmptyCCs()
Dim oTbl As Word.Table
Dim oCell As Word.Cell
Dim oCC As ContentControl
Dim oCCs As ContentControls
Set oTbl = ActiveDocument.Tables(1)
For Each oTbl In ActiveDocument.Tables
For Each oCell In oTbl.Range.Cells
If oCell.Range.ContentControls.Count > 0 Then
For Each oCC In oCell.Range.ContentControls
If oCC.Tag = "Date1" Then
oCC.Range.Shading.BackgroundPatternColor = wdColorWhite
Else
If oCC.Tag = "Date2" Then
oCC.Range.Shading.BackgroundPatternColor = wdColorWhite
Else
If oCC.Tag = "Date3" Then
oCC.Range.Shading.BackgroundPatternColor = wdColorWhite
Else
If oCC.ShowingPlaceholderText = True Then
'oCell.Shading.BackgroundPatternColor = wdColorAutomatic 'wdColorRose
'or
oCC.Range.Shading.BackgroundPatternColor = wdColorRose
End If
End If
End If
End If
Next oCC
End If
Next oCell
Next oTbl
End Sub

gmaxey
08-02-2011, 03:11 PM
Brian,

I forgot to add the code to select the first listentry. You can't delete text in a dropdown with VBA anymore than you can in the document using the mouse. The range must be one of the available items.

What I did was create new dropdowns that use "Select One" as the placeholder text. This is set using the Design Mode and you must ensure that you preserve the Placeholder Text style (if you don't like the grey look then modify the style).

I guess I forgot to add the code because if you used a template to create new documents rather than a document to create new documents then you wouldn't need the ClearFields code at all (or I wouln't think you would).

BTW, I changed one of your CCOnExit Cases to show you how to use "CANCEL" vice CC.Range.Select if the entry is too long.

See the attached file and save it as a template (.dotm extension). When you want a new document create it from the template.

Send me an e-mail (use the feedback link on my website) and I will send you the template file.

gmaxey
08-02-2011, 03:44 PM
Select Case statement make it easier:

Select Case CC.Tag
Case "Date1", "Date2", "Date3"
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
Case Else
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End Select

g8r777
08-02-2011, 04:18 PM
Perfect. I tried Case but couldn't get it quite right (the story of my coding life) and defaulted to the way I did it which worked. I was putting Or between each case instead of a comma and used Else instead of Case Else.

I knew Case would work but I always get hung up on the small details.

g8r777
08-03-2011, 08:36 AM
So I now have the exceptions to the highlighting coded and working properly and I did clean up the code using Case as suggested by Greg.

I have attempted to code my conditional formatting. I have managed to write code that compiles and doesn't generate any error messages when it runs but it also doesn't do anything (or at least what I want it to do).

I have attached the OnExit macro with my modifications highlighted in red. Perhaps someone can find where the code is going wrong.

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim oRng As Word.Range
Dim oVoteAuth As Boolean
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:="12345"
End If
Select Case CC.Tag
Case "longname1", "longname2", "longname3"
If (Not CC.ShowingPlaceholderText) And Len(CC.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line."
Cancel = True 'CC.Range.Select
End If
Case "shortname"
If (Not CC.ShowingPlaceholderText) And _
Len(CC.Range.Text) > 20 Then
MsgBox "Short Name must be 20 characters or less."
CC.Range.Select
End If
Case "voteauth"
If CC.Range.Text = "Jerry Lewis" Then
oVoteAuth = True
End If
End Select
If CC.ShowingPlaceholderText = False Then
'Set oRng = CC.Range
'oRng.Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
Else
If CC.Tag = "voteauthin" Then
If oVoteAuth = True Then
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
End If
Else
'Set oRng = CC.Range
'oRng.Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End If
End If
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="12345"
End If
End Sub


I am trying to have one text content control have no background color if another drop down content control has specified values selected even if the text content control has no text entered.

I have attached the document again as I have modified some of the other code.

Frosty
08-03-2011, 09:08 AM
I think it's going to be beneficial to your education for you to learn how do the following things:

1. "Step through" code (using F8 or the button in the VBA IDE)
2. Setting breakpoints (using F9 or the button)
3. Hovering over variable while stepping through code (that will tell you what their value is)

Later, you can get into using watches... but for now, I think you're going to be able to solve a bunch of your own conceptual problems just by being able to step through your own code, and trouble-shoot real time.

It can be daunting for anyone to click a button hoping your macro works. And then trying to wade through all the code to figure out what went wrong.

The solution is to step through your code, and watch it go wrong, then adjust right then and there, and run that particular line again.

Frosty
08-03-2011, 09:13 AM
Oh, and variable naming conventions-- everyone is different, but internal consistency always helps the readability of your code.

Most people have some kind of typical prefix to their variable name, based on the type of variable you're declaring.

bVariable or blnVariable for Booleans
iVariable or intVariable for integer
lVariable or lngVariable for long
you get the idea...
object:o, obj
range:r, rng
string: s, str
collection: col, coll
variant: var

Naming conventions help you organize your thoughts, as well as make it easier to see when you've made some kind of mistake while stepping through your code (sometimes, the incorrect variable type may only show up during "run-time" rather than "compile-time").

g8r777
08-03-2011, 09:43 AM
Frosty,

I appreciate the suggestions. I assume by "step through" you mean "step into". If not please correct me. I have tried that and don't really see what it does. I click on it or press F8 and nothing happens.

As far as breakpoints, that's a good suggestion. I wasn't aware that you could do that. I have just been putting ' in front of all the lines below the code that I want to test through. I assume setting a breakpoint accomplishes the same thing in a mutch simpler and less time consuming manner.

As far as my current code in question, there are no errors. The code runs fine it just doesn't appear to do what I need it to do. I don't believe setting breakpoints will help as no matter where I set them the code will run fine.

Maybe I am missing what stepping into the code and setting breakpoints will help me see.

Frosty
08-03-2011, 09:51 AM
It becomes trickier to troubleshoot routines which you set up to fire automatically (i.e., on "events"). That is where breakpoints come in.

Set a breakpoint at the top of your "OnExit" routine. When that routine fires (or fails to fire) as you expect, you will jump to the VBA window, ready to step through the routine (using F8).

Collectively I think of it as "Step through" although, "Step Into" is the official name for what F8 does. There are a number of variations. You might not need to step through every line of your code, because some parts of it work and some parts of it don't, but that's what I'd suggest to start with. You can explore the concepts of "Step out of" and "Step Over" and "Run to this line" later.

When you say there are no errors in your code, but it doesn't appear to do what you need it to do: that is an error.

However, it is an error that only you can fix. Because Microsoft is doing exactly what you told it to do. So you have to tell it something different.

I'm trying to help you help yourself on how to troubleshoot this kind of stuff.

For example, if you put your cursor on the line which says "Private Sub Document_ContentControlOnExit..." line, and hit F9, you will see a deep red line appear.

Now, if you go use your document, changing a content control and expecting this code to trigger...but you never jump to the VBA window, you know your event isn't even "firing."

So you may need to restart Word (I'm not sure when Content Control events become corrupt in Word 2010).

But if it is firing... then you can use F8 to go step-by-step through your code and see what code you wrote that you *expect* to be triggered by the conditions which existed when this whole routine started.

When the logic your wrote doesn't match up with what you expect... you are well on your way to adjusting.

Breaking it in to smaller bits will make the entire troubleshooting experience feel less overwhelming.

And that's what setting breakpoints will help you to see.

Frosty
08-03-2011, 10:01 AM
In short, using breakpoints and stepping through code is completely different than commenting out sections of code. One is a "real-time" view of your macro in action, and one is simplifying what the macro "did" (and is extremely inefficient as well!).

I think the above is going to make your life a lot easier. Try it out. I'm not trying to be difficult, I'm trying to help you.

g8r777
08-03-2011, 10:06 AM
I now see where F8 comes into play and how this can be useful.

I believe I have found where my current issuse begins.

With the following code:

Case "voteauth"
If CC.Range.Text = "Jerry Lewis" Then
oVoteAuth = True
End If
End Select

if I set a breakpoint at the End Select line and then get the macro to run OnExit, even if I have selected "Jerry Lewis" in the voteauth drop down, when I hover over the oVoteAuth variable i see oVoteAuth = False. I need this to be True for my conditional formatting to show properly. In this case "Jerry Lewis" is the exception. If "Jerry Lewis" then don't follow the rules that all other content controls follow, follow a new set of rules.

I figured this is where the code was going wrong but I believe this verifies it.

Frosty,

Can you confirm that I am on the right track.

If so I am at a loss.

Frosty
08-03-2011, 10:09 AM
You're on the right track. Just set your breakpoint at the top of the routine, and step through. Instead of seeing if oVoteAuth = False, you can hover and see if your CC.Tag value is actually "voteauth" (maybe it's "Voteauth", etc).

Rather than set up the break point to see the result... set it up earlier and check everything as you step through.

You're getting close.

Frosty
08-03-2011, 10:14 AM
Once you get this concept, you'll be ready to look at the Locals Watch window, which is HUGELY useful for real-time debugging, as it allows you (without having to manually "watch" some line of code) to check in with whatever variables you are using and see their values *as you run the macro*.

With these kinds of basics in self-debugging, you will be able to get a lot further before throwing up your hands. Everything else is just experience (i.e,. I'm more familiar with Word than Powerpoint, but, ultimately, I can program in anything VB/VBA related, because I understand the fundamentals).

That's what I think you need, honestly, just some fundamental debugging skills. Everything after that will become easy by comparison.

Frosty
08-03-2011, 10:16 AM
One other piece of info: that little yellow arrow off to the left of the highlighted line of yellow... you can "grab" that arrow and move your it back up in your routine... to try and "redo" some piece of logic.

You are not limited by only going forward. You can move that run line anywhere you want (I'm not sure what it's called... but it's the highlighted yellow bar which indicates where you are currently paused).

g8r777
08-03-2011, 10:39 AM
Okay, by stepping into the code I have now figured out the Cases are mutually exlusive. Once I am in the voteauth case the code:

If CC.Tag = "voteauthin" Then
If bVoteAuth = True Then
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
End If
Else

becomes irrelevant because I am not in the voteauthin case. What I need to figure out is how to reference another case (or content control) while in the current case.

Frosty
08-03-2011, 10:44 AM
I'm just looking over your project. This is where different coding styles come into play. I know Greg prefers everything left-justified (to start), so that Dimmed variables are at the same indent level as Private Sub, etc.

And I prefer everything to start at one indent level inside of the Sub and End Sub lines (except for labels, which are always left-justified).

However, despite coding "style" differences, Greg and I would both agree: you really really need to indent your code properly. You should be able to visually see the logic of your macro.

If yadayada Then
Else
If yadayadayada Then
Else
End If
End If

Is very very difficult to read.

If yadayada Then

Else

If yadayadayada Then

Else

End If

End If

Is much easier to read. This will also help you to diagnose problems. Any time you see an End If directly above another (with no indenting), you may very well have a mistake in your code. Or, at the very least, it will be harder to troubleshoot that code later.

Now, before you get irritated that I've spent so much time not giving you the answer...

Two theoretical issues with your code:

1. You have what I would call a "primary" logical construct in this OnExit routine: the .Tag property of the ContentControl you are exiting. And yet, you test it in two different ways (a select case, and then later in an if statement). You have fallen into a fairly common trap: a TYPO. In one place you test for "voteauth" and set a value, and then later check that value, but only if the same test equals "voteauthin"

Rather than fixing the problem, I'm trying to help you see why, conceptually, you should re-structure and *understand* rather than just "fix it." Because you'll avoid this problem in the future.

2. You don't comment your code at all. Comments aren't just for ignoring pieces of code, they are for explaining what all this mumbo jumbo means. Especially when soliciting help.

While I was writing this, you then indicated that the "voteauth"/"voteauthin" issue isn't a typo, that is an indication of your desire to reference a totally different control. Comments would help explain that desire.

Comments are also extremely helpful when you come back to the code 6 months later and don't remember what was supposed to happen (i.e., you might not remember the voteauth/voteauthin issue, and simply think it's a typo like I did).

So, now we know the problem:

1. You want to reference a DIFFERENT content control during the OnExit event of a content control. Got it. Give me a second. This is the kind of help that experience makes easier.

g8r777
08-03-2011, 10:52 AM
I agree my formatting sucks. I have been trying to clean it up as I go along and I believe it I am getting better at it.

I believe you and I have come to the same conclusion. Until stepping through the code I didn't really understand what the Case function did as explained in my last post.

I now see where the logic fails.

I truly appreciate all the help. Beacause of you and Greg I now have a much better understanding (and way to understand via stepping through) what the code is actually doing.

I agree with that this goes a long way to figuring out the proper way to accomplish things.

Frosty
08-03-2011, 11:11 AM
Here is your routine slightly re-written, commented, as well as a function which you can use to pass in a tag and return a content control...

There is one bit of unnecessary code which I've left in so you can see it.

Also, this is not actually working as desired on my machine (although the "other" content control is actually selected, the shading isn't accurate).

Perhaps it's my version of word, since you're clearly using this same methodology. But I would need to do wdColorWhite (instead of wdColorAutomatic) to get mine to "blank out" in terms of shading. Automatic just leaves it as the last color.

But this is at least closer to the end-result you're looking for:

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim oRng As Word.Range
Dim oVoteAuth As Boolean
Dim oOtherCC As ContentControl

'unprotect the document, if it needs it
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:="12345"
End If

'check which control we're dealing with
Select Case CC.Tag

Case "longname1", "longname2", "longname3"
'can't ahve more than 48 characters on a line
If (Not CC.ShowingPlaceholderText) And Len(CC.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line.", vbInformation
Cancel = True
'CC.Range.Select
End If

Case "shortname"
'we require less than 20 characters
If (Not CC.ShowingPlaceholderText) And Len(CC.Range.Text) > 20 Then
MsgBox "Short Name must be 20 characters or less.", vbInformation
CC.Range.Select
End If

'we need to check another control here
Case "voteauth"
If CC.Range.Text = "Jerry Lewis" Then
oVoteAuth = True
'now adjust our other one
Set oOtherCC = fGetContentControl("voteauthin")
'make sure a control was returned
If Not oOtherCC Is Nothing Then
'you can see how you now don't even need this variable or logic, right?
If oVoteAuth = True Then
oOtherCC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
End If
End If
End If

'we put this case here, so it gets left alone (and not swept up in the Case Else)
Case "voteauthin"
'do nothing, since "voteauth" takes care of this

'if not dealt with above...
Case Else

'leave the color normal if we've changed from the placeholder text
If CC.ShowingPlaceholderText = False Then
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
'otherwise indicate with a special color
Else
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End If
End Select

'now we re-protect
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="12345"
End If
End Sub
'A function allowing you to pass in the tag property, and return the content control
Public Function fGetContentControl(sTag As String) As ContentControl
Dim oCC As ContentControl

'cycle through all our content controls
For Each oCC In ActiveDocument.ContentControls
If UCase(oCC.Tag) = UCase(sTag) Then
'found it! Set our return value and exit the function
Set fGetContentControl = oCC
Exit For
End If
Next
End Function

Frosty
08-03-2011, 11:22 AM
A couple more comments as I glance over your project:

1. Look up the concept of "Scope" as it pertains to variables. You need to understand the difference between a global (or public) variable (available for use with all sub-routines in your project), a module-level variable (available to all sub-routines in your project) and a subroutine variable (only available within that routine). You also need to understand when those variables fall out of "scope" (i.e., when they automatically empty themselves out and/or release the memory they would use).

A lack of understanding on this concept is going to cause you troubleshooting/debugging headaches later. For now, don't "Dim" any variables anywhere but inside a specific routine. So putting "Dim oCC As ContentControl" below Option Explicit but outside any subroutine is a "no no" until you fully understand what's happening there. Trust me on this.

And then when you do decide to apply this concept (global and module variables-- there are appropriate times to use it), give an appropriate prefix (in addition to you variable type prefix). So a global string variable might be
Public pub_sMyGlobalVariable as String
A module variable might be
Dim m_sMyModuleVariable As String
And your subroutine variable would be
Dim sMyVariable As String

2. Very rarely do you want the same routines automatically running in both a Document_New event and a Document_Open event. This will cause you headaches. As the template developer, just leave that code in the Document_New event (for anyone who creates a document based on the template, and leave Document_Open out of it. You can always manually run the Document_New event (with your cursor in the Document_New event and your newly discovered F8 command--or even F5).

g8r777
08-03-2011, 11:42 AM
This works. Thank you. I, too, had to use wdColorWhite instead of Automatic.
Like you, I'm not sure why we can use Automatic elsewhere but have to use White here. Perhaps Greg knows and can educate us both.

I know the code works and I have tried stepping through to figure the following situation out but I have one question.

With the voteauthin case set to do nothing (because we took care of the exception in the voteauth case) how does the code function properly to change the background color to white (automatic) if I change that field from the default value and "Jerry Lewis" isn't selected?

I want it to work the way it does but I don't understand how it does (if that makes sense).

We set voteauthin to do nothing yet the Case Else code seems to apply to that case instead. I'm sure I am missing something.

Also, I am trying to modify the code to change voteauthin back to rose colored if it is blank (ShowingPlaceholderText = True) and "Jerry Lewis" isn't the selection for voteauth.

I'm still thinking through the logic and will post when I have something that works or I think is close.

Frosty
08-03-2011, 11:50 AM
Ahh, good question. You'll need to adjust the code. However, stick with your existing logical construct:

1. You check the .Tag property
2. *most* of the controls you do a single thing (white background if not default text, otherwise rose background)-- this is the Case Else statement
3. All the other controls, you do something specific. You may also wish to do what you do for all of the other controls. If that's the case (no pun intended), it might help to to use a boolean flag inside of your select case, and then perform your "standard" action outside of the select case, based on the boolean flag. Or it might be just as easy to repeat the code. This is kind of a style question.


Sub DoStuff
Dim bDoStandard As Boolean

Select Case Something
Case 1
'do some stuff, but not the standard
Case 2
'do some special stuff, and do the standard
bDoStandard = True
Case Else
bDoStandard = True
End Select

'what's the standard?
If bDoStandard Then
'Do you standard stuff
End If
End Sub
It's really a judgement call on whether the "Standard stuff" is worth being separated out into a subroutine, semi-modularized as I did above, or simply take the code you'd have in that If bDoStandard...End If logic chunk, and just copy paste whereever you need it in the routine.

See what you come up with, and then ask for comments! First order of business is always: get it working the way you want.

Style can come later.

g8r777
08-03-2011, 11:59 AM
This may be a dumb question but why do I need to adjust the code if it is working properly?

I assumed that if the code was working properly the probelm wasn't with the code, it was with my understanding of how it was working.

Had I really understood the Case command earlier this question would have popped up then.

We have the the longname and shortname cases earlier in the code that check for character length only. How do they get properly formatted to a white background if changed from the placeholder? I don't understand how you get code outside of that particular case to affect the case.

I'm missing something.

Frosty
08-03-2011, 12:18 PM
Is it? It doesn't seem to work properly in all cases (to me). But I don't fully understand what it is that you're trying to do, and I also don't fully understand ContentControls :)

Remember, this code triggers when you *leave* each of the content controls in your document, so you need to deal with all cases of the .Tag property.

If I leave voteauth with Jerry Lewis selected, voteauthin turns white.
But if I enter voteauthin, click the delete key (so that I don't see the "Click to enter authority as required" text), and then leave voteauthin, it goes back to Rose (although typing something leaves it as white).

So I know not all bases are covered at that moment. There is more going on which maybe Greg can explain with his deeper understanding of content controls.

But you would probably be well-served to deal with voteauthin whenever you leave it, just to make sure... although I suspect some of what you perceive as "working" may be something to do with default settings on content controls.

I really haven't wrapped my mind around the particulars of content controls yet, so I suspect I'm missing an essential concept in trying to explain exactly what is going on here.

g8r777
08-03-2011, 12:32 PM
What I am trying to do overall is detect if a content control has had text entered (for text content controls) or had an option selected (for a drop down). If no text has been entered or no drop down selected then I want to highlight the content control.

The code works.

My only exception pertains to the voteauth and voteauthin controls. If voteauth is "Jerry Lewis" then I don't care if voteauthin is blank. It should not be highlighted.

That is working. What I need to happen is if voteauth is changed off of "Jerry Lewis" (to any other drop down list entry) and voteauthin is blank, then highlight voteauthin as something now needs to be entered there.

I also still don't understand if we have a case that only looks for character length (the longname and shortname cases) then how does typing something in and then exiting that control have the background changed to white since it appears to me that all we asked the code to do for that case is look for character length. Ultimately this is the behavior I want so I'm not too unhappy but I would like to understand how we are getting other code to run for that case.

Frosty, using your example above the longname and shortname cases fall into your:
Case 2
'do some special stuff, and do the standard
bDoStandard = True


However, the only code we provide is for the special stuff. We never tell it to do the standard stuff yet it does anyway.

gmaxey
08-03-2011, 02:18 PM
First question. Why did I go to the trouble of revising your OnExit event code if you aren't going to use it?

If I understand the issue correctly if you select Jerry Lewis from the voteauth dropdown and then exit you don't want the voteauthin CC flagged with rose shading. Correct?

If so then:

1. Use the CCExit event to detect the CC tagge "voteauth" using and additional case statement.
2. Evaluate its value
3. Get the CC you want to change.
4. Change it.

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim oRng As Word.Range
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:="12345"
End If
Select Case CC.Tag
Case "longname1", "longname2", "longname3", "shortname"
If Validate(CC) Then
CC.Range.Select
End If
'Or
Cancel = Validate(CC)
Case "voteauth"
If CC.Range.Text = "Jerry Lewis" Then
ActiveDocument.SelectContentControlsByTag("voteauthin").Item(1).Range.Shading.BackgroundPatternColor = wdColorWhite
Else
ActiveDocument.SelectContentControlsByTag("voteauthin").Item(1).Range.Shading.BackgroundPatternColor = wdColorRose
End If
End Select
If CC.ShowingPlaceholderText = False Then
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
Else
Select Case CC.Tag
Case "Date1", "Date2", "Date3"
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
Case Else
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End Select
End If
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="12345"
End If
End Sub
Function Validate(oCC_Tested As ContentControl) As Boolean
If (Not oCC_Tested.ShowingPlaceholderText) And Len(oCC_Tested.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line."
Validate = True
Else
Validate = False
End If
End Function

CCs and their ranges are a bit quirky and I don't fully understand the under the hood mechanics. It seems that VBA cannot determine what color wdcolor automatic should be if placeholder text is displayed. You can see this for yourself by adding a single CC to a new document and running this code:

Sub ScratchMacro()
Dim oCC As ContentControl
Dim i As Long
Set oCC = ActiveDocument.ContentControls(1)
oCC.Range.Text = "some text"
oCC.Range.Shading.BackgroundPatternColor = wdColorBrown
oCC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
'Showing placeholder text
oCC.Range.Text = ""
oCC.Range.Shading.BackgroundPatternColor = wdColorBrown
i = oCC.Range.Shading.BackgroundPatternColor
oCC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
'Did it change?
If oCC.Range.Shading.BackgroundPatternColor = i Then
'No.
oCC.Range.Shading.BackgroundPatternColor = wdColorWhite
End If
End Sub







This may be a dumb question but why do I need to adjust the code if it is working properly?

I assumed that if the code was working properly the probelm wasn't with the code, it was with my understanding of how it was working.

Had I really understood the Case command earlier this question would have popped up then.

We have the the longname and shortname cases earlier in the code that check for character length only. How do they get properly formatted to a white background if changed from the placeholder? I don't understand how you get code outside of that particular case to affect the case.

I'm missing something.

Frosty
08-03-2011, 03:21 PM
Well, you peaked my interest and I should learn about content controls at some point anyway. And I still don't totally understand. And since I am actually trying to get my own work done, I will leave you with the code that really works.

The ThisDocument code is more robust in that it deals with all the scenarios (basically, any time you're going to create an associate between controls, you will almost always need to create the reciprocal association as well, or you will rapidly find yourself with a couple of logic flaws in your code).

The ThisDocument code module:

Option Explicit
Private Sub Document_New()
UnprotectActiveDocument
FlagEmptyCCs
ProtectActiveDocument
End Sub
Private Sub Document_Open()
' UnprotectActiveDocument
' FlagEmptyCCs
' ProtectActiveDocument
' ThisDocument.Saved = True
End Sub
Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim oRng As Word.Range
Dim oOtherCC As ContentControl
Dim bIgnoreControl As Boolean
'make it accessible to be changed
UnprotectActiveDocument

'check which control we're dealing with
Select Case CC.Tag

Case "longname1", "longname2", "longname3"
'can't ahve more than 48 characters on a line
If (Not CC.ShowingPlaceholderText) And Len(CC.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line.", vbInformation
Cancel = True
End If

Case "shortname"
'we require less than 20 characters
If (Not CC.ShowingPlaceholderText) And Len(CC.Range.Text) > 20 Then
MsgBox "Short Name must be 20 characters or less.", vbInformation
CC.Range.Select
End If

'we need to adjust another control, based on this control's settings
Case "voteauth"

'so find the other control
Set oOtherCC = fGetContentControl("voteauthin")

'make sure a control was returned
If Not oOtherCC Is Nothing Then
'if our current control is Jerry Lewis, we don't care what's in our other control
If CC.Range.Text = "Jerry Lewis" Then
oOtherCC.Range.Shading.BackgroundPatternColor = wdColorWhite
bIgnoreControl = True
End If
End If

'We need to adjust this control based on another control's settings
Case "voteauthin"
'so find the other control
Set oOtherCC = fGetContentControl("voteauth")

'make sure a control was returned
If Not oOtherCC Is Nothing Then
'if our current control is Jerry Lewis, we don't care what's in our other control
If oOtherCC.Range.Text = "Jerry Lewis" Then
CC.Range.Shading.BackgroundPatternColor = wdColorWhite
bIgnoreControl = True
End If
End If

'if not dealt with above, it's in the "do standard" category
Case Else
'nothing to see here
End Select

'Unless we should ignore it, do our standard formatting
If bIgnoreControl = False Then
'leave the color normal if we've changed from the placeholder text
If CC.ShowingPlaceholderText = False Then
CC.Range.Shading.BackgroundPatternColor = wdColorWhite
'otherwise indicate with a special color
Else
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End If
End If

'reprotect it
ProtectActiveDocument
End Sub
'A function allowing you to pass in the tag property, and return the content control
Public Function fGetContentControl(sTag As String) As ContentControl
Dim oCC As ContentControl

'cycle through all our content controls
For Each oCC In ActiveDocument.ContentControls
If UCase(oCC.Tag) = UCase(sTag) Then
'found it! Set our return value and exit the function
Set fGetContentControl = oCC
Exit For
End If
Next
End Function
The Main code module replacement:
NOTE: it seems to me (and I defer to Greg), that, at least for the code project you uploaded, you don't need to cycle through all tables, all cells in each table, and then through any content controls in a cell... you can just as easily (and more efficiently) go through the ActiveDocument.ContentControls collection.

Of course, if you are dealing with multiple document types, and some content controls exist outside of a table and you want to leave those alone, You would need to keep the old system. BUT-- your "Clear Fields" routine goes through all of the active document content controls, while your FlagEmptyCCs doesn't... so that is a flawed approach to cycle through one way in one routine, and another way in another routine... unless it is intentional.

Option Explicit 'Forces varible declaration
Public Sub ProtectActiveDocument()
'now we re-protect
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="12345"
End If
End Sub
Public Sub UnprotectActiveDocument()
'unprotect the document, if it needs it
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:="12345"
End If
End Sub
'Clear the fields and flag them as empty
Sub ClearFields()
Dim oCC As ContentControl
Dim oILS As InlineShape

'clear out all the content controls
For Each oCC In ActiveDocument.ContentControls
Select Case oCC.Type
Case wdContentControlRichText
oCC.Range.Text = ""
Case wdContentControlDropdownList
oCC.DropdownListEntries(1).Select
End Select
Next oCC
For Each oILS In ActiveDocument.InlineShapes
If oILS.Type = wdInlineShapeOLEControlObject Then
If TypeOf oILS.OLEFormat.Object Is MSForms.OptionButton Then
oILS.OLEFormat.Object.Value = False
End If
End If
Next

FlagEmptyCCs

End Sub

'NOTE: this function does not flag any content controls NOT in a table
Public Sub FlagEmptyCCs()
Dim oCC As ContentControl
Dim oCCs As ContentControls

For Each oCC In ActiveDocument.ContentControls
Select Case oCC.Tag
'for the dates, leave them white even if they are empty
Case "Date1", "Date2", "Date3"
oCC.Range.Shading.BackgroundPatternColor = wdColorWhite
Case Else
If oCC.ShowingPlaceholderText = True Then
oCC.Range.Shading.BackgroundPatternColor = wdColorRose
Else
oCC.Range.Shading.BackgroundPatternColor = wdColorWhite
End If
End Select
Next oCC
End Sub

Frosty
08-03-2011, 03:23 PM
Darn it... I should have refreshed before posting. Thanks for the info, Greg... good to know the quirks of content controls.

I defer to Greg's posting, just leaving my rewrite for reference-- take a look at your ClearFields methodology of cycling through your content controls vs your FlagEmptyCCs methodology.

And, as you can see, you don't need the function I created (fGetContentControl) since that function was already created by Microsoft, and is called SelectContentControlsByTag :)

gmaxey
08-03-2011, 03:32 PM
Reading todays posts closer I noticed that I missed Jason's point about the possibility of a user changing voteauthin after "Jerry Lewis" was selected in voteauth. Little things like that just illustrate how quickly things can get complicated and frustrate when a paying custom says I want "X" and then come back later with gee "X" is great but I really want "Y."

You have opened up a whole new can of worms (no pun intended).

A few comments. First while I like and have a fair knowledge of content controls I feel a userform would better suit your needs.
Secondly, this new requirement brings out the fact that ContentControls were not really intended for use in Protected Forms.

Once again I have practically rebuilt this form so it will work.

The first thing I did was remove the code to protect and unprotect the form and put all the CCs in one big RichText control. The RichText control is locked for editing but the controls inside are not. This sort of protects the form.

Here is the revised code if you don't want to download the doc.

Your "ThisDocument" module:

Option Explicit
Private Sub Document_New()
Call FlagEmptyCCs
End Sub
Private Sub Document_Open()
Call FlagEmptyCCs
ThisDocument.Saved = True
End Sub
Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim oRng As Word.Range 'Deleted this as it isn't used. GKM
Dim oCC_Target As ContentControl 'Added this GKM
Select Case CC.Tag
'This is our rich text container holding all of the form CCs. We don't want to do anything with it so get out.
Case "Collection": Exit Sub
'These are the four special case CCs that require data validation
Case "longname1", "longname2", "longname3", "shortname"
'Do it with a funciton
If fnc_InInvalidData(CC) Then
CC.Range.Select
End If
'Or
'Cancel = fnc_InInvalidData(CC)
'This is a special case CC
Case "voteauth"
'This is the CC that you are going to manipulate while processing the special case CC
'P.S. it would be nice if you used distintive titles and tabs (e.g., Voting Authority)
Set oCC_Target = ActiveDocument.SelectContentControlsByTag("voteauthin").Item(1)
If CC.Range.Text = "Jerry Lewis" Then
'You really don't even want the CC to show. To bad there isn't a CC.Visible property.
With oCC_Target
.LockContents = False
With .Range
.Text = " " 'So it isn't showing placeholder text
.Shading.BackgroundPatternColor = wdColorAutomatic
End With
'To deal with the shmucks.
.LockContents = True
End With
'Move to the next CC of interest
ActiveDocument.SelectContentControlsByTag("acmtype").Item(1).Range.Select
Else
With oCC_Target
.LockContents = False
With .Range
.Text = ""
.Shading.BackgroundPatternColor = wdColorRose
End With
End With
End If
End Select
'Why when leaving "longname1, 2, 3, or short name does the backgroud color change?
'Because we are now out of the Select Case statement that evaluated them specifically and are now evaluating them
generally:
If CC.ShowingPlaceholderText = False Then
'Deal with "voteauthin"
If CC.LockContents = True Then
'Don't try to change it.
Else
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
End If
Else
Select Case CC.Tag
Case "Date1", "Date2", "Date3"
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
Case Else
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End Select
End If
End Sub
Function fnc_InInvalidData(oCC_Tested As ContentControl) As Boolean
If (Not oCC_Tested.ShowingPlaceholderText) And Len(oCC_Tested.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line."
fnc_InInvalidData = True
Else
fnc_InInvalidData = False
End If
End Function

Your standard module:

Option Explicit 'Forces varible declaration
Dim oCC As ContentControl
Dim oCCs As ContentControls
Dim oLE As ContentControlListEntry
Sub ClearFields()
Dim oILS As InlineShape
Set oCCs = ActiveDocument.Range.ContentControls
Dim i As Long
For Each oCC In oCCs
Select Case oCC.Type
Case wdContentControlRichText
If Not oCC.Title = "Collection" Then
If oCC.LockContents = True Then
oCC.LockContents = False
End If
oCC.Range.Text = ""
End If
Case wdContentControlDropdownList
oCC.DropdownListEntries(1).Select
End Select
Next oCC
For Each oILS In ActiveDocument.InlineShapes
If oILS.Type = wdInlineShapeOLEControlObject Then
If TypeOf oILS.OLEFormat.Object Is MSForms.OptionButton Then
oILS.OLEFormat.Object.Value = False
End If
End If
Next
Call FlagEmptyCCs
End Sub
Sub FlagEmptyCCs()
Dim oTbl As Word.Table
Dim oCell As Word.Cell
Dim oCC As ContentControl
Dim oCCs As ContentControls
Set oTbl = ActiveDocument.Tables(1)
'If ActiveDocument.ProtectionType <> wdNoProtection Then
' ActiveDocument.Unprotect Password:="12345"
'End If
For Each oTbl In ActiveDocument.Tables
For Each oCell In oTbl.Range.Cells
If oCell.Range.ContentControls.Count > 0 Then
For Each oCC In oCell.Range.ContentControls
If oCC.ShowingPlaceholderText = True Then
Select Case oCC.Tag
Case "Date1", "Date2", "Date3"
oCC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
Case Else
oCC.Range.Shading.BackgroundPatternColor = wdColorRose
End Select
End If
Next oCC
End If
Next oCell
Next oTbl
'If ActiveDocument.ProtectionType = wdNoProtection Then
' ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="12345"
'End If
End Sub

gmaxey
08-03-2011, 03:43 PM
Jason,

The page is probably like the titel (rough) but when you have time you may find something useful here:

http://gregmaxey.mvps.org/Content_Controls.htm

I have actually been working with another guy this week on a project that resolves the OnExit issue in Word2007 and provides and OnChange event.

As for your comments here is this thread, I appreciate all and as usual all are spot on. I think the reason Brian is looping through tables is because he once asked my how to tag all CCs in a table. Later he asked how to tag all CCs in all tables. I never even thought until now that what he really seemed to want is how to flag all CCs.

Being retired military I don't seem to be a diplomatic as you. I don't think Brian has taken any of it personally. At least his private correspondence does'nt indicate.



Darn it... I should have refreshed before posting. Thanks for the info, Greg... good to know the quirks of content controls.

I defer to Greg's posting, just leaving my rewrite for reference-- take a look at your ClearFields methodology of cycling through your content controls vs your FlagEmptyCCs methodology.

And, as you can see, you don't need the function I created (fGetContentControl) since that function was already created by Microsoft, and is called SelectContentControlsByTag :)

g8r777
08-03-2011, 04:00 PM
I haven't taken anything personally. I appreciate all the help as I know both of you probably have much better and more interesting things to do than to help me. I also realize that I am a bit of a novice and have a lot to learn.

I realize my requests and desires changed as the project progressed and that this made everything much more difficult. For that I apologize. This whole stream has given me a much better understanding of how the code works from both of you (more so than anything else I've worked on).

The further along we got the more questions were generated for me. Both of you so graciously answered them all and for that I am greatful.

I believe I now have a much better framework going forward and will get much farther along myself before asking questions. And when I do ask questions I feel they will be much more intelligent and thought out.

The revised document works flawlessly. I may try to add back the password protection as I would not put it past anyone at my company to figure out how to unlock the RichText field and screw something up. If I can't then I will live with it.

I liked how you read my mind about not wanting the second field to even show if "Jerry Lewis" was suggested. Code that kind of mind reading an you will be a billionaire.

Thank you,


Brian

Frosty
08-03-2011, 04:06 PM
You aren't the first client (nor will you be the last) to change the essential nature of the project in mid-stream. Grin.

For whatever help I provided, you're welcome. And honestly, I get a lot out of these conversations too.

Best thing to take away from these conversations, if at all possible, isn't the solution nearly so much as how you arrived at the solution. Because knowing the answer isn't nearly as useful as knowing how to find the answer, at least in the computer world. There are simply too many "answers" for one person to hold in his/her mind.

But there aren't really that many different ways of finding out the answer. Just some are shorter than others.

Good luck going forward!

- Jason
p.s. thanks for the link and the comments, Greg!

gmaxey
08-03-2011, 05:09 PM
Brian,

None of it was meant to be abusive and you are a sport for taking the jabs along with the assistance.

Like the web title in the link I provided Jason, CCs really are like diamonds in the rough. They have so much potential but it seems that MS pushed them out unfinished. The glaring ommission IMHO is the lack of an OnChange event. With that event your "voteauthin" CC could be formatted when "Jerry Lewis" was select and the user would not need to first exit the CC. Other gross ommission is a direct method of determining which dd entry is selected. It would be nice if there was a .Enabled property. The last two are available with basic form fields so I don't understand why MS didn't provide them with CCs.

If you are interested in the OnChange evnet (in progress) see:

http://gregmaxey.mvps.org/ContentControl_OnChange_Event.htm

g8r777
08-04-2011, 09:21 AM
So after testing the document in our real environment, I've come up with one last thing that I would like to have happen (there's a shock I know).

If longname1 has something entered then longname2 and longname3 do not need to be highlighted but they should still be able to have text entered into them. I liked how Greg handled the voteauth/voteauthin solution but this required locking voteauthin which won't work in the new scenario as users should still be able to enter text.

I have tried various iterations of Greg's code. I have been able to get longname2 and longname3 to turn white when exiting longname1 but as soon as I exit either of them the turn rose again if blank (by design). I haven't been able to come up with the logic for the exception.

I have noted what I am trying to have happen in the following code. I have also highlighted in red some code (although it doesn't work) the should illustrate what I am trying to have happen.

If this is too complicated then I will just live with what we have so far.

The reason for the splitting of the various name controls is beacuse I was messing around with ways to code this exception.

Also, shortname has a different invalid data requirement than the longnames which I fixed.

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim oRng As Word.Range
Dim oVoteAuth As Boolean
Dim oCC_Target As ContentControl
Dim oOtherCC As ContentControl
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:="12345"
End If
Select Case CC.Tag

Case "longname1"
If fnc_InInvalidLong(CC) Then
Cancel = True
'CC.Range.Select
End If

Case "longname2", "longname3"
If fnc_InInvalidLong(CC) Then
Cancel = True
'CC.Range.Select
End If

Case "shortname"
If fnc_InInvalidShort(CC) Then
Cancel = True
'CC.Range.Select
End If

Case "voteauth"
'This is the CC that you are going to manipulate while processing the special case CC
'P.S. it would be nice if you used distintive titles and tabs (e.g., Voting Authority)
Set oCC_Target = ActiveDocument.SelectContentControlsByTag("voteauthin").Item(1)
If CC.Range.Text = "Jerry Lewis" Then
'You really don't even want the CC to show. To bad there isn't a CC.Visible property.
With oCC_Target
.LockContents = False
With .Range
.Text = " " 'So it isn't showing placeholder text
.Shading.BackgroundPatternColor = wdColorAutomatic
End With
'To deal with the shmucks.
.LockContents = True
End With
'Move to the next CC of interest
ActiveDocument.SelectContentControlsByTag("acmtype").Item(1).Range.Select
Else
With oCC_Target
.LockContents = False
With .Range
.Text = ""
.Shading.BackgroundPatternColor = wdColorRose
End With
End With
End If
End Select
'Why when leaving "longname1, 2, 3, or short name does the backgroud color change?
'Because we are now out of the Select Case statement that evaluated them specifically and are now evaluating them
'generally:
If CC.ShowingPlaceholderText = False Then
'Deal with "voteauthin"
If CC.LockContents = True Then
'Don't try to change it.
Else
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
End If
Else
Select Case CC.Tag

'These cases should never be highlighted
Case "Date1", "Date2", "Date3"
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic

'These cases do not need to be highlighted if something has been entered in longname1. They should be able to have content entered in them
'should someone need more space than longname one so we cannot lock them like we did with voteauthin.
Case "longname2", "longname 3"
If CC.ShowingPlaceholderText = False Then
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
Else

'If longname1 has something entered then do not highight longname2 or longname3 but also do not lock them
If ActiveDocument.ContentControls("longname1").ShowingPlaceholderText = False Then
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
Else
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End If
End If

Case Else
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End Select

End If
End Sub

Frosty
08-04-2011, 09:38 AM
1. I thought you determined that wdColorAutomatic was no good? So your second select case seems like it won't be doing what you want.

2. Your red text, I think, needs to use either the custom function I wrote (not really) OR the microsoft function Greg shows... .SelectContentControlsByTag.

3. This logic structure kind of makes me confused, although I realize this is the template Greg gave you. However, I think it is easier to understand a routine if you only use a particular logic structure a single time (at the same indent level).

Having two different Select Case CC.Tag structures, one nested inside a If .ShowingPlaceholderText construct makes it rapidly more difficult to understand what's going on as your project gets more complicated (as it is).

I would suggest adjusting this to go through the Select Case once, and then just deal with each control (and what you want to do with it) as you need. Or starting with the .ShowingPlaceholderText If statement, and nesting the select cases. This may result in some duplicated code. If too much of that duplicated code shows up-- you know it's a good time to modularize and make your own function.

But I think, honestly, you're just getting lost in a forest of logical constructs

You've got two "main" logic checks you want to deal with: 1) the .Tag property (for identifying "special" controls from the "normal" controls) and 2) the .ShowingPlaceholderText value.

I think your routine should be (in the big picture way) structured in such a way as to make one of those two primary.

Option 1:

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Select Case CC.Tag
Case 1
If CC.ShowingPlaceholderText Then
Else
End If
Case 2
If CC.ShowingPlaceholderText Then
Else
End If
Case Else
If CC.ShowingPlaceholderText Then
Else
End If
End Select
End Sub
Option 2:

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
If CC.ShowingPlaceholderText Then
Select Case CC.Tag
Case 1
Case 2
Case Else
End Select
Else
Select Case CC.Tag
Case 1
Case 2
Case Else
End Select
End If
End Sub
Having the two structures somewhat mixed and matched is simply causing confusion (in my humble opinion).

g8r777
08-04-2011, 10:16 AM
Frosty,

I agree with your logical construct argument. Cleaning up the code and making it more logical (which I am sure will also serve to further my understanding) is a project for another day.

You were correct about using Greg's SelectContentControlsByTag method. I have now been able to get the document to do everything I need it to do. The final code is below.

I know it is ugly (formatting wise). As a point of convention do you use tabs or spaces to indent different lines?



Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim oRng As Word.Range
Dim oVoteAuth As Boolean
Dim oCC_Target As ContentControl
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:="12345"
End If
Select Case CC.Tag

Case "longname1"
If fnc_InInvalidLong(CC) Then
Cancel = True
'CC.Range.Select
End If

'remove highlighting from longname2 and longname3 if text entered in longname1
If CC.ShowingPlaceholderText = False Then
Set oCC_Target = ActiveDocument.SelectContentControlsByTag("longname2").Item(1)
With oCC_Target
With .Range
.Shading.BackgroundPatternColor = wdColorWhite
End With
End With

Set oCC_Target = ActiveDocument.SelectContentControlsByTag("longname3").Item(1)
With oCC_Target
With .Range
.Shading.BackgroundPatternColor = wdColorWhite
End With
End With
End If

'highlight longname2 and longname3 only if longname1 is cleared AND longname2 and longname3 are cleared
If CC.ShowingPlaceholderText = True Then
Set oCC_Target = ActiveDocument.SelectContentControlsByTag("longname2").Item(1)
If oCC_Target.ShowingPlaceholderText = True Then
With oCC_Target
With .Range
.Shading.BackgroundPatternColor = wdColorRose
End With
End With
End If

Set oCC_Target = ActiveDocument.SelectContentControlsByTag("longname3").Item(1)
If oCC_Target.ShowingPlaceholderText = True Then
With oCC_Target
With .Range
.Shading.BackgroundPatternColor = wdColorRose
End With
End With
End If
End If

Case "longname2", "longname3"
If fnc_InInvalidLong(CC) Then
Cancel = True
'CC.Range.Select
End If

Case "shortname"
If fnc_InInvalidShort(CC) Then
Cancel = True
'CC.Range.Select
End If
Case "voteauth"
'This is the CC that you are going to manipulate while processing the special case CC
'P.S. it would be nice if you used distintive titles and tabs (e.g., Voting Authority)
Set oCC_Target = ActiveDocument.SelectContentControlsByTag("voteauthin").Item(1)
If CC.Range.Text = "Jerry Lewis" Then
'You really don't even want the CC to show. To bad there isn't a CC.Visible property.
With oCC_Target
.LockContents = False
With .Range
.Text = " " 'So it isn't showing placeholder text
.Shading.BackgroundPatternColor = wdColorAutomatic
End With
'To deal with the shmucks.
.LockContents = True
End With
'Move to the next CC of interest
ActiveDocument.SelectContentControlsByTag("acmtype").Item(1).Range.Select
Else
With oCC_Target
.LockContents = False
With .Range
.Text = ""
.Shading.BackgroundPatternColor = wdColorRose
End With
End With
End If
End Select
'Why when leaving "longname1, 2, 3, or short name does the backgroud color change?
'Because we are now out of the Select Case statement that evaluated them specifically and are now evaluating them
'generally:
If CC.ShowingPlaceholderText = False Then
'Deal with "voteauthin"
If CC.LockContents = True Then
'Don't try to change it.
Else
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
End If
Else

Select Case CC.Tag

Case "Date1", "Date2", "Date3"
CC.Range.Shading.BackgroundPatternColor = wdColorWhite

'keep longname2 and longname3 from rehighlighting if they are blank but longname1 has something entered
Case "longname2", "longname3"
If CC.ShowingPlaceholderText = False Then
CC.Range.Shading.BackgroundPatternColor = wdColorWhite
Else
Set oCC_Target = ActiveDocument.SelectContentControlsByTag("longname1").Item(1)
If oCC_Target.ShowingPlaceholderText = False Then
CC.Range.Shading.BackgroundPatternColor = wdColorWhite
Else
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End If
End If

Case Else
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End Select
End If
End Sub

Frosty
08-04-2011, 10:53 AM
Be wary of the "I'll do it right *later*" argument too much. As with word processing... it always takes a little longer to "do it right" initially. But it pays such dividends later. Remember, you know how this code is supposed to work as good as you are going to right now-- so that is the time to restructure. Not 6 months from now when you have to take the time to re-learn what it was supposed to do. Typically my process is this:

1. Get something working
2. Have someone else verify it's working as they want it to
3. Fix whatever I messed up, give back
4. Get a "yah, that's basically it"
<<end proof of concept phase>>
5. Clean up my code
6. Deliver the final product.

If the timeline is such that I need to deliver an intermediary product before #5, I do (which is basically #4). But I'm always trying to make sure the update process is as automated as possible (because there will always be a need to update), and so steps #5 & #6 aren't such a big deal.

Anyway-- that's just generic advice. Of course delivery is always more important than perfection.

As for the discussion point on tabs and indenting:
These are the things I always change, immediately upon doing development on a machine (all in the VBA development environment)
Inside Tools > Options...
1. Editor > Auto Syntax Check (checked off, default is checked on) (highlights mistakes in coding in red, rather than popping up a message box to tell me it's wrong)
2. Editor > Require Variable Declaration (checked on, default is checked off) -- this puts Options Explicit at the top of every new module/class/form
3. Editor > Tab Width = 2 (default is 4)
4. Editor Format > Code Colors > Keyword Text (foreground a bright blue, default is a dark blue -- just easier for me to read)

And then close that dialog and I...
5. Show all the Debug and Edit toolbars (only Standard is generally showing)
6. Customize my standard toolbar by adding the Compile Project command at the end of it (it's at the top of the Debug category). This helps remind me to click that compile button all the time, which has saved me more often than I can count.
7. Play with my windows so that I can easily read my code, still see my project explorer and immediate window, as well as have my Locals Window and Watch window docked close to the immediate window, so I can double-click them quickly to see what's going on in a particular routine (Locals and Watch window are something to explore- they are hugely useful at times)

Of course, YMMV.

g8r777
08-04-2011, 11:06 AM
Excellent suggestions as always. I now know why all the code you and Greg have sent have had indenting two spaces off from where mine is. I have been tabbing and then deleting two spaces each time to try to get the same indenting as you.

By "another day" I did mean sooner rather than later. I do need to get this document deployed and it works. I just meant that I could clean it later as it it already working.

g8r777
08-04-2011, 03:58 PM
Frosty,

So "sooner" was sooner rather than later. I am attempting to code for some more complex conditional formatting (at the request of one of my users now that the document is deployed). It made sense for me to clean up now.

If you have time (and I know that's asking a lot) please take a look at the code below. I tried to clean up the formatting and tried to come up with one logical construct, in this case Option 1 that you provided.

I have prioritized Tag over ShowingPlaceholderText. I hope what I did is what you were trying to point out. The code has the same functionality as the old code but appears much cleaner to me.

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim oCC_Target As ContentControl

Select Case CC.Tag

Case "longname1"
If fnc_InInvalidLong(CC) Then
Cancel = True
'CC.Range.Select
End If

If CC.ShowingPlaceholderText = True Then

CC.Range.Shading.BackgroundPatternColor = wdColorRose

'highlight longname2 and longname3 only if longname1 is cleared AND longname2 and longname3 are cleared
Set oCC_Target = ActiveDocument.SelectContentControlsByTag("longname2").Item(1)
If oCC_Target.ShowingPlaceholderText = True Then
With oCC_Target
With .Range
.Shading.BackgroundPatternColor = wdColorRose
End With
End With
End If

Set oCC_Target = ActiveDocument.SelectContentControlsByTag("longname3").Item(1)
If oCC_Target.ShowingPlaceholderText = True Then
With oCC_Target
With .Range
.Shading.BackgroundPatternColor = wdColorRose
End With
End With
End If
Else

CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
'remove highlighting from longname2 and longname3 if text entered in longname1
Set oCC_Target = ActiveDocument.SelectContentControlsByTag("longname2").Item(1)
With oCC_Target
With .Range
.Shading.BackgroundPatternColor = wdColorWhite
End With
End With

Set oCC_Target = ActiveDocument.SelectContentControlsByTag("longname3").Item(1)
With oCC_Target
With .Range
.Shading.BackgroundPatternColor = wdColorWhite
End With
End With
End If


Case "longname2", "longname3"
If fnc_InInvalidLong(CC) Then
Cancel = True
'CC.Range.Select
End If

If CC.ShowingPlaceholderText = False Then
CC.Range.Shading.BackgroundPatternColor = wdColorWhite
Else

Set oCC_Target = ActiveDocument.SelectContentControlsByTag("longname1").Item(1)
If oCC_Target.ShowingPlaceholderText = False Then
CC.Range.Shading.BackgroundPatternColor = wdColorWhite
Else
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End If

End If

Case "shortname"
If fnc_InInvalidShort(CC) Then
Cancel = True
'CC.Range.Select
End If
Case "voteauth"

If CC.ShowingPlaceholderText = True Then

CC.Range.Shading.BackgroundPatternColor = wdColorRose

Else

'This is the CC that you are going to manipulate while processing the special case CC
'P.S. it would be nice if you used distintive titles and tabs (e.g., Voting Authority)
Set oCC_Target = ActiveDocument.SelectContentControlsByTag("voteauthin").Item(1)
If CC.Range.Text = "Sole" Then
'You really don't even want the CC to show. To bad there isn't a CC.Visible property.
With oCC_Target
.LockContents = False
With .Range
.Text = " " 'So it isn't showing placeholder text
.Shading.BackgroundPatternColor = wdColorAutomatic
End With
'To deal with the shmucks.
' .LockContents = True
End With
'Move to the next CC of interest
ActiveDocument.SelectContentControlsByTag("acmtype").Item(1).Range.Select
Else
With oCC_Target
.LockContents = False
With .Range
.Text = ""
.Shading.BackgroundPatternColor = wdColorRose
End With
End With
End If
End If
Case "Date1", "Date2", "Date3"
CC.Range.Shading.BackgroundPatternColor = wdColorWhite

Case Else
If CC.ShowingPlaceholderText = True Then
CC.Range.Shading.BackgroundPatternColor = wdColorRose
Else
CC.Range.Shading.BackgroundPatternColor = wdColorWhite
End If

End Select
End Sub

As a random aside, I disabled the locking of voteauthin as that was no longer needed. If this was locked and a user clicked in it (because it was technically still there but with no text or highlighting) then they would get an error message and would be promted to debug. That whole routine freaks my users out. It was easier to have them be able to click in that cell by accident and have nothing happen. No one will type in there anyway. I agree with Greg that it would nice to have a CC.Visible command of some kind to just make the whole content control disappear.

Frosty
08-04-2011, 05:24 PM
This is getting fairly close to a style issue. Apart from a couple of minor comments, what I've done can be summed up pretty simply:
I've made it easier to read for *my* brain... but maybe not yours.
1. I've separated out your "color appropriately" function, which I think you can get some mileage out of, if only to clean up the whole wdColorWhite vs wdColorAutomatic thing, and if you ever decide you don't like Rose and want to go with Aqua... it's all in a single place.
2. There are two "Frosty Notes" -- read those. I'm not sure you want to wipe out data in voteauthin every time someone chooses something other than "Sole" -- remember, people don't always go linearly, and people make mistakes and click the wrong thing. I think you probably want to test whether or not voteauthin is filled with only " " and then you could wipe it out.
3. I know it works, but I'm still not sure why -- so I added in the colorappropriately function to the "shortname" case.
4. If you're using the Cancel (which means-- don't do this OnExit routine), then it means you're going to get another shot at the OnExit, so you can use that as a branch to stop the rest of your processing.
5. If you are setting .LockContents = False in both logic branches, you can pull it out and set above (or get rid of it entirely, perhaps)
6. With ... End With. Judicious use of this will make your code more readable. It's a judgement call. However, this is not a good use of it:

With oCC_Target
With .Range
.Shading.BackgroundPatternColor = wdColorWhite
End With
End With
This works just as well

oCC_Target.Range.Shading.BackgroundPatternColor = wdColorWhite
7. Variable naming is a style issue. I renamed oCC_Target to CC_Associate. It makes slightly more sense to me... I'm dealing with CC primarily, and then occasionally an associated CC.
8. And I always reserve the right to change my mind. With the creation of the ColorAppropriate subroutine, suddenly the .ShowingPlaceholderText became less of a primary logic branch (since I was mostly using that as an argument for the subroutine), and thus I stuck with always thinking of the CC and the associated CCs (thus the slight change in the "longname" cases, structurally). A moving target, I know. But that's when you know it's really just a style issue, rather than right way vs. wrong way.
Anyway... here's my slight re-write of what you've done. Again, I think the bulk of this is simply style, rather than substance.

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim CC_Associate As ContentControl

Select Case CC.Tag

Case "longname1"
'if invalid, just cancel and do nothing else
If fnc_InInvalidLong(CC) Then
Cancel = True
'otherwise...
Else
'color this control appropriately
ColorAppropriately CC, CC.ShowingPlaceholderText

'and deal with associated controls...
Set CC_Associate = ActiveDocument.SelectContentControlsByTag("longname2").Item(1)
'if primary is blank, color appropriately
If CC.ShowingPlaceholderText = True Then
ColorAppropriately CC_Associate, CC_Associate.ShowingPlaceholderText
'otherwise, don't color
Else
ColorAppropriately CC_Associate, False
End If

Set CC_Associate = ActiveDocument.SelectContentControlsByTag("longname3").Item(1)
'if primary is blank, color appropriately
If CC.ShowingPlaceholderText = True Then
ColorAppropriately CC_Associate, CC_Associate.ShowingPlaceholderText
'otherwise don't color
Else
ColorAppropriately CC_Associate, False
End If
End If

Case "longname2", "longname3"
'if invalid length, don't do anything else
If fnc_InInvalidLong(CC) Then
Cancel = True
Else
'whether to color is determined by primary control, which is...
Set CC_Associate = ActiveDocument.SelectContentControlsByTag("longname1").Item(1)
'if primary showing placeholder, color appropriately,
If CC_Associate.ShowingPlaceholderText Then
ColorAppropriately CC, CC.ShowingPlaceholderText
'otherwise, no color regardless
Else
ColorAppropriately CC, False
End If
End If
Case "shortname"
If fnc_InInvalidShort(CC) Then
Cancel = True
Else
ColorAppropriately CC, CC.ShowingPlaceholderText
End If

Case "voteauth"
'color appropriately
ColorAppropriately CC, CC.ShowingPlaceholderText

'deal with the associated control, which is...
Set CC_Associate = ActiveDocument.SelectContentControlsByTag("voteauthin").Item(1)
'FROSTY NOTE:*** is this line necessary? You had it in both logic branches
CC_Associate.LockContents = False
'special case-- adjust our associated content control to not have placeholder text
If CC.Range.Text = "Sole" Then
CC_Associate.Range.Text = " "
'and move to the next CC
ActiveDocument.SelectContentControlsByTag("acmtype").Item(1).Range.Select
Else
'FROSTY NOTE:*** are you sure you want to do this? Selecting "Sole" will wipe out this data
'perhaps check CC_Associate.range.text against your "blank value" and reset only then, a la
'the commented out If/End If
'If CC_Associate.Range.Text = " " Then
CC_Associate.Range.Text = ""
'End If
End If
'don't forget to color our associated control
ColorAppropriately CC_Associate, CC_Associate.ShowingPlaceholderText

Case "Date1", "Date2", "Date3"
ColorAppropriately CC, False

Case Else
ColorAppropriately CC, CC.ShowingPlaceholderText
End Select
End Sub
'consolidation of the two colors you use
Private Sub ColorAppropriately(ByVal CC As ContentControl, bColored As Boolean)
If bColored Then
CC.Range.Shading.BackgroundPatternColor = wdColorRose
Else
CC.Range.Shading.BackgroundPatternColor = wdColorWhite
End If
End Sub

Frosty
08-04-2011, 05:31 PM
Oh, and in case it wasn't clear... I think your clean up looks great. A huge improvement (and more functional) than the original code you posted. And that was absolutely in the direction I'd talked about earlier.

So, apart from the with...end with stuff, and a couple of the "are you sure?" questions, everything is mostly just a matter of style and preference at this point. I just revamped a bit as a bit of a brain exercise with the thought of separating out the "ColorThisContentControlThusly" functionality, and a bit of tweaking.

EDIT: syntax and typos

gmaxey
08-04-2011, 06:32 PM
Brian,

First your difficulties with indenting may be my fault. Unconventional as I am I always use two spaces as my indent and never use tabs in my code. I'm 52 years old and don't imagine I will change.

While Jason may feel he lacks experience with content controls there is no lack of knowledge or experience with writing structured code. I defer to his suggestions in all cases.

That said, it has been difficult to assist you properly because you continue to move the goal post after the start of the game. I understand that this is not intentional, but hardly nothing I have offered so far looks like what I would probably have offered had I known your true objectives at the start of the game.

Unlike Jason who seems to view your project with a broader scope and offered outstanding advice, I have simply tried to answer your questions one at a time. I think this may have contributed to some of your difficulties. For example you first asked me (in another thread I think) how to reset all CCs to placeholder text state. That resulted to the ClearFields code in your project. While not discussed here (or at least I don't think so). We now know that objective really isn't necessary because if you use a template instead of an old document to create your new forms the CCs will be showing placeholder text anyway.

We exanded that reset all CCs in a table to all CCs in all tables. In truth we just needed to reset all CCs in the document. That was a tree in the forest that Jason identified while we were looking at a different tree.

Your newest objective not to flag longname2, 3 and shortname if longname 1 makes me wonder again just what it is that you are really trying to do!!

What is longname2 and longname 3? Looking at your form (really looking at it for the first time) it seems that longname2 and longname3 are just extentions of longname1 (i.e., line 2 and line 3). If this is the case then why aren't you using the allow carriage returns property of the longname1 content control?

If that is not the case then please explain what longname2 and 3 are. If you don't care if they are flagged if longname1 has text then why do you care if they are flagged at all? Flags in the sense you first applied them to me means "You must enter text here." If I am led to believe that I must do this (fill in longname2) and that I must to that (fill in longname3) and then after doing so I learn that if I had simply filled in longname1 that I really didn't have to do either then I am going to say "You wasted my time." :dunno

I know that you have put a lot of work into this form and that you have learned alot. I hope you've learned that while CCs are neat and there are neat things that can be done with them, they get very complicated very fast if you start trying to do anything more that enter text.

Good luck.

gmaxey
08-04-2011, 07:12 PM
Step through this code in a new document watch the document while doing so. It might help explain the wdcolorautomatic issue:

Option Explicit
Sub ScratchMacro()
Dim oCC As ContentControl
Dim oRng As Word.Range
Set oRng = ActiveDocument.Paragraphs(1).Range
oRng.Text = "Test"
oRng.Font.Color = wdColorBlack
oRng.Shading.BackgroundPatternColor = wdColorBlack 'Text and background are the same color
oRng.Font.Color = wdColorAutomatic 'Automatic evaluates the contrast of the range font compared to its background _
and automatically adjusts
oRng.Shading.BackgroundPatternColor = wdColorAutomatic
Set oCC = ActiveDocument.ContentControls.Add(wdContentControlText)
With oCC
.Range = "" 'Showing placeholder text
'VB knows what the range is and nows what color Aqua is so it is applied.
.Range.Shading.BackgroundPatternColor = wdColorAqua 'VB knows what the range is and nows what color Aqua is.
'VB knows what the range is but since there is nothing in it is doesn't know how to evaluate and apply the proper color
.Range.Shading.BackgroundPatternColor = wdColorAutomatic
'Put something in the range so it has a background to evaluate
.Range = "Some text"
.Range.Shading.BackgroundPatternColor = wdColorAutomatic
End With
End Sub

g8r777
08-04-2011, 08:09 PM
Greg,

I like your moving the goalpost after starting the game analogy. It is spot on. To add, I didn't even know the rules of the game when the game started.

I do have a reason for three separate longname fields. This form is for a user to input account data into. The form then gets sent to our operations department. They use the data in the form to propogate fields in our back office system. For the longname there are three separate lines each of which can have no more than 48 characters. I thought it easier to code for this as three separate controls than one control with carriage returns and then trying to code to limit each line to 48 characters.

Take the example "Jerry Lewis Revocable Trust Number 1".

A user could choose to have this as one line or could have it be:

Jerry Lewis
Revocable Trust
Number 1

It is up to the user how they want to format the titling with the constraint that no line have more than 48 characters. Our Ops department will enter the fields in their system however we tell them.

As long as the user enters something in longname1 then they don't have to enter anything in longname2 or 3 (no highlighting) but they should have the ability to if they need to (titling goes over 48 characters) or want to (specific formatting for the titling).

I guess a different way to code this would be to have longname2 and 3 "invisible" (no text, no highlighting using code you previously provided) until longname1 had text entered. Then they would be "visible" and able to have text entered but wouldn't be highlighted. This method would have it so they are never highlighted and would only be available for entry if something is first entered in longname1.

I hope this makes sense and shows that I'm not totally nuts or incompetent (only slightly). It was the understanding I developed through this whole endeavor that allowed me to code all of that.

BREAK

Frosty,

You are correct about not wanting to clear voteauthin. I thought of that while using the document in a trial run. I was going to work on that tomorrow but you beat me to it. It is scary how you and Greg are able to read minds (or more probably know the proper way things should happen).

I also realized that the color coding was used over and over and I could probably minimize the code by creating a sub and calling it over and over. I'd be interested to see your ColorAppropriately subroutine.

All of you other comments are spot on (as usual). I realize that I have unnecessary or redundant code. This isn't always apparent since the code works. I appreciate the suggestions and will hopefully know what to look for in the future.

Thank you both,

Brian

gmaxey
08-04-2011, 08:28 PM
Brian,

OK. I understand. A UserForm would have been much easier to code and manage.

Anyway and while waiting your reply I put together some code that you might consider. In it I decided to separate out the flaggin code to a called procedure.

BTW I see you are back to a protected form so if you want to test this out you'll have to put the code in the document I sent you last night.

In your ThisDocument.Module

Option Explicit
Private Sub Document_New()
Call FlagEmptyCCs
End Sub
Private Sub Document_Open()
Call FlagEmptyCCs
ThisDocument.Saved = True
End Sub
Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim oRng As Word.Range 'Deleted this as it isn't used. GKM
Dim oCC_Target As ContentControl 'Added this GKM
Select Case CC.Tag
'This is our rich text container holding all of the form CCs. We don't want to do anything with it so get out.
Case "Collection": Exit Sub
'These are four special case CCs that require data validation
Case "longname1"
If fnc_InInvalidData(CC) Then
CC.Range.Select
Exit Sub
End If
Call Main.SetCCFlag(CC)
Case "longname2", "longname3", "shortname"
If fnc_InInvalidData(CC) Then
CC.Range.Select
Exit Sub
End If
Case "voteauth"
Set oCC_Target = ActiveDocument.SelectContentControlsByTag("voteauthin").Item(1)
If CC.Range.Text = "Jerry Lewis" Then
'You really don't even want the CC to show. To bad there isn't a CC.Visible property.
With oCC_Target
.LockContents = False
.Range.Text = " " 'So it isn't showing placeholder text
'To deal with the shmucks.
.LockContents = True
End With
Call Main.SetCCFlag(CC)
'Move to the next CC of interest
ActiveDocument.SelectContentControlsByTag("acmtype").Item(1).Range.Select
Else
With oCC_Target
.LockContents = False
.Range.Text = ""
End With
End If
Call Main.SetCCFlag(CC)
'These are the three CCs that you don't care about
Case "Date1", "Date2", "Date3"
'So do nothing
Case Else
Call Main.SetCCFlag(CC)
End Select
End Sub
Function fnc_InInvalidData(oCC_Tested As ContentControl) As Boolean
If (Not oCC_Tested.ShowingPlaceholderText) And Len(oCC_Tested.Range.Text) > 48 Then
MsgBox "Limit 48 characters per line."
fnc_InInvalidData = True
Else
fnc_InInvalidData = False
End If
End Function


In a standard module (mine is named "Main"):
Option Explicit 'Forces varible declaration
Dim oCC As ContentControl
Dim oCCs As ContentControls
Sub ClearFields()
Dim oILS As InlineShape
Set oCCs = ActiveDocument.Range.ContentControls
Dim i As Long
For Each oCC In oCCs
Select Case oCC.Type
Case wdContentControlRichText
If Not oCC.Title = "Collection" Then
If oCC.LockContents = True Then
oCC.LockContents = False
End If
oCC.Range.Text = ""
End If
Case wdContentControlDropdownList
oCC.DropdownListEntries(1).Select
End Select
Next oCC
For Each oILS In ActiveDocument.InlineShapes
If oILS.Type = wdInlineShapeOLEControlObject Then
If TypeOf oILS.OLEFormat.Object Is MSForms.OptionButton Then
oILS.OLEFormat.Object.Value = False
End If
End If
Next
Call FlagEmptyCCs
End Sub
Sub SetCCFlag(ByRef oCC_Passed As ContentControl)
Application.ScreenUpdating = False
If Not oCC_Passed.ShowingPlaceholderText Then
Select Case oCC_Passed.Tag
Case "longname1"
'You say your don't care if these three are empty as long as longname1 isn't empty, so:
With ActiveDocument.SelectContentControlsByTag("longname2").Item(1)
.LockContents = False
.SetPlaceholderText , , "Click and enter name, as applicable"
.Range.Shading.BackgroundPatternColor = wdColorWhite
End With
With ActiveDocument.SelectContentControlsByTag("longname3").Item(1)
.LockContents = False
.SetPlaceholderText , , "Click and enter name, as applicable"
.Range.Shading.BackgroundPatternColor = wdColorWhite
End With
With ActiveDocument.SelectContentControlsByTag("shortname").Item(1)
.LockContents = False
.SetPlaceholderText , , "Click and enter name, as applicable"
.Range.Shading.BackgroundPatternColor = wdColorWhite
End With
Case "voteauthin"
If ActiveDocument.SelectContentControlsByTag("voteauth").Item(1).Range.Text = "Jerry Lewis" Then
oCC_Passed.Range.Shading.BackgroundPatternColor = wdColorWhite
Else
oCC_Passed.Range.Shading.BackgroundPatternColor = wdColorRose
End If
Case Else
oCC_Passed.Range.Shading.BackgroundPatternColor = wdColorWhite
End Select
Else
Select Case oCC_Passed.Tag
Case "longname1"
'I suppose if longname is empty then you would want these to be flagged, emtpy and locked. No?
With ActiveDocument.SelectContentControlsByTag("longname2").Item(1)
.LockContents = False
.SetPlaceholderText , , "Locked for content change"
.Range.Text = ""
.Range.Shading.BackgroundPatternColor = wdColorRose
.LockContents = True
End With
With ActiveDocument.SelectContentControlsByTag("longname3").Item(1)
.LockContents = False
.SetPlaceholderText , , "Locked for content change"
.Range.Text = ""
.Range.Shading.BackgroundPatternColor = wdColorRose
.LockContents = True
End With
With ActiveDocument.SelectContentControlsByTag("shortname").Item(1)
.LockContents = False
.SetPlaceholderText , , "Locked for content change"
.Range.Text = ""
.Range.Shading.BackgroundPatternColor = wdColorRose
.LockContents = True
End With
Case "voteauthin"
If ActiveDocument.SelectContentControlsByTag("voteauth").Item(1).Range.Text = "Jerry Lewis" Then
oCC_Passed.Range.Shading.BackgroundPatternColor = wdColorAutomatic
Else
oCC_Passed.Range.Shading.BackgroundPatternColor = wdColorWhite
End If
Case Else
oCC_Passed.Range.Shading.BackgroundPatternColor = wdColorRose
End Select
End If
Application.ScreenUpdating = True
End Sub
Sub FlagEmptyCCs()
Dim bLocked As Boolean
For Each oCC In ActiveDocument.ContentControls
If oCC.ShowingPlaceholderText = True Then
If oCC.LockContents = True Then
bLocked = True
oCC.LockContents = False
End If
Select Case oCC.Tag
'You say your don't care if these are empty as long as longname1 isn't empty
Case "longname2", "longname3", "shortname"
If Not ActiveDocument.SelectContentControlsByTag("longname1").Item(1).ShowingPlaceholderText Then
oCC.Range.Shading.BackgroundPatternColor = wdColorWhite
Else
oCC.Range.Shading.BackgroundPatternColor = wdColorRose
End If
'You said you never wanted these flagged.
Case "Date1", "Date2", "Date3"
'So do nothing.
Case "voteauthin"
If ActiveDocument.SelectContentControlsByTag("voteauth").Item(1).Range.Text = "Jerry Lewis" Then
oCC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
Else
oCC.Range.Shading.BackgroundPatternColor = wdColorWhite
End If
Case Else
oCC.Range.Shading.BackgroundPatternColor = wdColorRose
End Select
If bLocked = True Then
oCC.LockContents = True
End If
bLocked = False
End If
Next oCC
End Sub


Note this code still has the call to ClearFields and FlagEmptyCCs which is redundant if you are using a template as we discussed.

Frosty
08-04-2011, 08:32 PM
The ColorAppropriately sub was at the end of code I posted earlier, as an fyi...

'consolidation of the two colors you use
Private Sub ColorAppropriately(ByVal CC As ContentControl, bColored As Boolean)
If bColored Then
CC.Range.Shading.BackgroundPatternColor = wdColorRose
Else
CC.Range.Shading.BackgroundPatternColor = wdColorWhite
End If
End Sub
Which allowed passing in things like
ColorAppropriately CC, CC.ShowingPlaceholderText

But this really is style type stuff...

g8r777
08-05-2011, 07:53 AM
Sorry Frosty. I spent 20 minutes looking at your rewrite and couldn't find the ColorAppropriately code. It was late and I guess my brains and eyes weren't on the same page.

gmaxey
08-05-2011, 10:50 AM
Brian,

I sent you your form back modified using a UserForm rather than all this difficult CC OnExit logic. One advantage you should see is the UserForm allows real time monitoring. It doesn't need to wait until you have entered 500 characters to tell you that you can't. It won't let you.

Frosty
08-05-2011, 12:15 PM
Brian,

While I don't have a lot of experience with Content Controls in Word 2007/2010, I do have a good bit of experience coding protected "form field" type documents in Word 2003. And, as Greg has pointed out, most of what you're trying to do here is very analogous to that (you just happen to be using a newer version of that functionality).

And, in my experience, using protected documents in Word have always had limitations. While Microsoft provides the ability to capture some "events" (and tie macros to those events), once you get into heavy data validation and (conceptually) linked data fields... use of UserForm really becomes the better approach. While you can, ultimately, make it work... it's ever so much more work.

The good news: much of what you've learned here is going to transfer very well. The main difference is that, instead of the word document being your "form", you simply have a different way of capturing input and spitting it out in document form.

Even though you may be scratching your head saying "uggh, but I just learned the Content Controls way of doing thing," I think using Greg's approach of a user form is going to make life a lot easier for you, especially as he's probably already done the hard stuff... and now you just have to look at the two options and ask any further questions about what is, essentially, a Rosetta stone giving you the translation between the two approaches.

In summary: use content controls and a protected document until your questions turn into a 20+ response thread, and then you know you've passed the point of beneficial return in the complexity, because you're then trying to fit a round peg in a square hole.

Nevertheless, I'm glad this thread came up... because I got to learn some nitty gritty about what is "new" to me: Content Controls. So thanks for... umm... doing it "wrong!" :)

Frosty
08-05-2011, 12:19 PM
And one other salient point:

Developing good User Forms is an art. When in doubt of how something "should" work (since there are so many options), open up a Microsoft dialog that uses similar functionality, and make your form work like that. Simple example (since I just saw this in another thread), always put your OK button to the left of the Cancel button (which should be at the bottom right corner of your form). This is how every dialog of MS works, so it's a good approach.

gmaxey
08-05-2011, 12:49 PM
Jason,

Brian brought up valid concern in offline correspondence. With the content controls it isn't going to take much effort for a novive to revise the dropdown listings. He is concerned about having to make changes to the code later on if say a new vote authority is appointed. I don't much blame him. It can be rough being the go to guy all the time.

This can be avoided of course by populating the list using an external (to the UserForm) source. That can easily be the content control itself. In the form I sent to Brian via e-mail I have pulled out all of the dropdownCCs and replaced them with plaintext. Just to demo I've put the voteauth dropdown back in.

Now if the novice needs to update the list they just need to change the collections CC to allow editing and edit the dropdown list entries, relock the collection.

Document file attached to illustrate if you or anyone else is interested.

To call the form click on the last QAT button.

Frosty
08-05-2011, 01:07 PM
Oh, you're good. I love it when "either/or" questions turn into "why not both?" answers.

This can also be a good use of Comments in a form (whether or not you use the "official" comment function, or simply put the text as the first paragraph, big and bold, with a bookmark around it. In the Document_New code you can delete the comment each time, but in the Document_Open you don't... and then any "template developer" will see that "comment" explaining how to make modifications.

That's the approach I've used when trying to keep something which is complex to build, but doesn't need my expertise in simply updating.