Consulting

Page 1 of 4 1 2 3 ... LastLast
Results 1 to 20 of 71

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

  1. #1
    VBAX Regular
    Joined
    Dec 2006
    Posts
    71
    Location

    Macro to highlight if certain Word table cells are blank

    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

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

    Visit my website: http://gregmaxey.com

  3. #3
    VBAX Regular
    Joined
    Dec 2006
    Posts
    71
    Location
    I tired this but keep getting the follwoing message:

    Compile error in hidden module: ThisDocument
    Any suggestions?

    Thank you,

    Brian

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

    Visit my website: http://gregmaxey.com

  5. #5
    VBAX Regular
    Joined
    Dec 2006
    Posts
    71
    Location
    Greg,

    The compile error is with the following code:

    [VBA]Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)[/VBA]

    The code Document_ContentControlOnExit is an ambiguous name.

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

    [VBA]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[/VBA]


    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

  6. #6
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Ambiguous name because you have two subroutines named Document_ContentControlOnExit.

    You should be able to take the
    [vba]
    Dim oRng As Word.Range
    If ContentControl.ShowingPlaceholderText = False Then
    Set oRng = ContentControl.Range
    oRng.Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
    End If
    [/vba]
    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.

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

    [vba]
    If CC.ShowingPlaceholderText = False Then
    Set oRng = CC.Range
    oRng.Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
    End If
    [/vba]
    It will still generate an error if you have content controls not in a table cell.

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

    Reply

    It looks like Jason has answered this.


    Quote Originally Posted by g8r777
    Greg,

    The compile error is with the following code:

    [vba]Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)[/vba]

    The code Document_ContentControlOnExit is an ambiguous name.

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

    [vba]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[/vba]


    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
    Greg

    Visit my website: http://gregmaxey.com

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

  10. #10
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Done automatically when?

    To process all tables instead of just table 1 then:

    [VBA]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
    [/VBA]
    Greg

    Visit my website: http://gregmaxey.com

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

  12. #12
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Put ScratchMacro in a standard module and call it from a Sub Document_Open() procedure in the ThisDocument module.
    Greg

    Visit my website: http://gregmaxey.com

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

    [vba]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
    [/vba]

    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?

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

    [VBA]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
    [/VBA]

    Send me your document.
    Greg

    Visit my website: http://gregmaxey.com

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

    [vba]
    Dim x as Integer
    Dim oPara as Paragraph
    For x = 1 To ActiveDocument.Paragraphs
    Set oPara = ActiveDocument.Paragraphs(x)
    oPara.Alignment = wdAlignParagraphsCenter
    Next
    [/vba] will cycle through all the paragraphs in the document and center align each of them... However, that is slightly different than...
    [vba]
    Dim oPara as Paragraph
    For Each oPara in ActiveDocument.Paragraphs
    oPara.Alignment = wdAlignParagraphCenter
    Next
    [/vba] 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.

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

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

    [VBA]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
    [/VBA]
    Greg

    Visit my website: http://gregmaxey.com

  18. #18
    VBAX Regular
    Joined
    Dec 2006
    Posts
    71
    Location
    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
    Attached Files Attached Files

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

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

    You're almost there.

    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.
    Attached Files Attached Files
    Last edited by gmaxey; 08-02-2011 at 12:58 PM. Reason: Didn't attach file as I thought I did
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

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