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."
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.
Step through this code in a new document watch the document while doing so. It might help explain the wdcolorautomatic issue:
[VBA]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
[/VBA]
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.
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
[VBA]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
[/VBA]
In a standard module (mine is named "Main"):
[VBA]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).ShowingPlace holderText 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
[/VBA]
Note this code still has the call to ClearFields and FlagEmptyCCs which is redundant if you are using a template as we discussed.
The ColorAppropriately sub was at the end of code I posted earlier, as an fyi...
[vba]
'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[/vba]
Which allowed passing in things like
ColorAppropriately CC, CC.ShowingPlaceholderText
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.
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.
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!"
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.
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.
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.