[vba]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[/vba]
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.
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.
[vba]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[/vba]
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.
[VBA]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[/VBA]
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.
[VBA] Select Case CC.Tag
Case "Date1", "Date2", "Date3"
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
Case Else
CC.Range.Shading.BackgroundPatternColor = wdColorRose
End Select
[/VBA]
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.
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.
[vba]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
[/vba]
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.
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.
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").
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.
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.
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.
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:
[vba]Case "voteauth"
If CC.Range.Text = "Jerry Lewis" Then
oVoteAuth = True
End If
End Select[/vba]
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.
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.
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.
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).
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:
[VBA]If CC.Tag = "voteauthin" Then
If bVoteAuth = True Then
CC.Range.Shading.BackgroundPatternColor = wdColorAutomatic
End If
Else[/VBA]
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.
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.
[vba]
If yadayada Then
Else
If yadayadayada Then
Else
End If
End If
[/vba]
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.
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.
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:
[vba]
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
[/vba]
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).
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.
Reply With Quote
