Consulting

Page 4 of 4 FirstFirst ... 2 3 4
Results 61 to 71 of 71

Thread: Macro to highlight if certain Word table cells are blank

  1. #61
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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."

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

    Visit my website: http://gregmaxey.com

  2. #62
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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]
    Greg

    Visit my website: http://gregmaxey.com

  3. #63
    VBAX Regular
    Joined
    Dec 2006
    Posts
    71
    Location
    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

  4. #64
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location

    Copy All

    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

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

    Visit my website: http://gregmaxey.com

  5. #65
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    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

    But this really is style type stuff...

  6. #66
    VBAX Regular
    Joined
    Dec 2006
    Posts
    71
    Location
    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.

  7. #67
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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.
    Greg

    Visit my website: http://gregmaxey.com

  8. #68
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    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!"

  9. #69
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    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.

  10. #70
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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.
    Attached Files Attached Files
    Greg

    Visit my website: http://gregmaxey.com

  11. #71
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •