MS Word VBA to check for empty text form fields upon file close/exit

Question

I need to have a MS Word Macro check upon file exit or file close, that certain specified text fields (legacy form fields, not content control) are empty.

I have used some code that is a pretty intrusive warning box. But its also contingent on the user selecting that field then the macro pops up a warning box either upon entry or exit, as specified in the form field properties menu. I have several fields,"Text1", "text2", then text7 thru 11. Trouble is, the user MUST select a field to get this code to work, on top of that, the warning box basically sends them into a death loop before they can even close the file. I also have to make a new module for each of field with the code below. Perhaps the best solution here is a macro that runs on close and/or exit of the file, which says "Hey you forgot to fill out these fields, they are 'mandatory' so go back and do that please, thanks!" What do you all think?

Sub MustFillIn3()
    If ActiveDocument.FormFields("Text2").Result = "" Then
        Do
            sInFld = InputBox("Request date required, please fill in below.")
        Loop While sInFld = ""
        ActiveDocument.FormFields("Text2").Result = sInFld
    End If
End Sub

score 0 · Accepted Answer · edited Jun 02 '23 at 18:05

Yes, just write the check code in the event handler procedure Document_Close in ThisDocument object, like this

Sub Document_Close()
    Dim ff As FormField, sInFld As String, msgShown As Boolean, d As Document, i As Byte
    'Dim ffNameDict As New Scripting.Dictionary, ffNameSpecCln As New VBA.Collection
    Dim ffNameDict As Object, ffNameSpecCln As New VBA.Collection
    
    
    Dim arr(7) As String, j As Byte
    arr(0) = "location": arr(1) = "request_date": arr(2) = "site"
    arr(3) = "UPC": arr(4) = "Current_LOA": arr(5) = "Req_LOA"
    arr(6) = "You Lost this One!!"
    
    For i = 1 To 11
        Select Case i
            Case 1, 2, 7, 8, 9, 10, 11 '"Text1", "text2", then text7 thru 11.
        
                'to a specific name list?
                'ffNameSpecCln.Add "Specific Name HERE " & i, "Text" & i
                ffNameSpecCln.Add arr(j), "Text" & i
                j = j + 1
        End Select
    Next i
     
    Set ffNameDict = CreateObject("Scripting.Dictionary")
    Set d = ActiveDocument
    For i = 1 To 11
        Select Case i
            Case 1, 2, 7, 8, 9, 10, 11 '"Text1", "text2", then text7 thru 11.
            'ffNameDict("Text" & i) = "Text" & i
            ffNameDict("Text" & i) = ffNameSpecCln.Item("Text" & i)
        End Select
    Next i
    For Each ff In d.FormFields
        If ff.Result = "" And ffNameDict.Exists(ff.Name) Then
            If Not msgShown Then
                MsgBox "Hey you forgot to fill out these fields, they are 'mandatory' so go back and do that please, thanks!", vbExclamation
                msgShown = True
            End If
            Do
'                sInFld = InputBox("Request date required, please fill in below." + vbCr + vbCr + _
                                    "@" + ff.Name + " is the current text fields to fill in !")
                sInFld = InputBox("Request date required, please fill in below." + vbCr + vbCr + _
                                    "@" + ffNameDict(ff.Name) + " is the current text fields to fill in !")
            Loop While sInFld = ""
            ff.Result = sInFld
        End If
    Next ff
    d.Save
End Sub

note: The Private modifier in this image should be removed in order to be called in the appWord_DocumentBeforeSave event handler (code above already set)

This check sub is triggered when the current document is closed and is not related to whether ff has focus or not (ie. the user Doesn't MUST select a field ).

Option Explicit

Public WithEvents appWord As Word.Application
 
Private Sub appWord_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
    ThisDocument.Document_Close
End Sub

You have to run this sub to Register Event_Handler to Word Application.

Option Explicit

'https://learn.microsoft.com/en-us/office/vba/word/concepts/objects-properties-methods/using-events-with-the-application-object-word
Public X As New app 
Public Sub Register_Event_Handler() 
    Set X.appWord = Word.Application 
End Sub

"物件類別模組" = class modules

"模組" = modules

"表單" = user form

"Microsof Word 物件" = Microsof Word object

As for the details, you should adjust them yourself. Try to understand the code I have given you to simulate it. Come back to StackOverflow and ask a new question when you encounter difficulties and problems in the implementation.

I've used the text field to test:

Is this yours?

Before closing the document check if it has been modified

Option Explicit

Public WithEvents appWord As Word.Application
 
Private Sub appWord_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
    If Not Doc.Saved Then
        If MsgBox("Do you want to save?", vbOKCancel + vbQuestion) = vbOK Then
            Doc.Save
        Else
            Doc.Close wdDoNotSaveChanges
        End If
    End If
End Sub

Private Sub appWord_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
    MS_Word_VBA_to_check_for_empty_text_form_fields_upon_file_close_exit
End Sub

Comment out the event handler Document_Close code and Registering an event handler when a document is opened:

Option Explicit

rem now can be Private, because there is no other place to call this procedure
Private Sub Document_Close()
    'MS_Word_VBA_to_check_for_empty_text_form_fields_upon_file_close_exit
End Sub


Private Sub Document_Open()
    Register_Event_Handler ' See previous code
End Sub

Extract the code to become a separate checker procedure or method:

Sub MS_Word_VBA_to_check_for_empty_text_form_fields_upon_file_close_exit()
    Dim ff As FormField, sInFld As String, msgShown As Boolean, d As Document, i As Byte
    'Dim ffNameDict As New Scripting.Dictionary, ffNameSpecCln As New VBA.Collection
    Dim ffNameDict As Object, ffNameSpecCln As New VBA.Collection
    
    
    Dim arr(7) As String, j As Byte
    arr(0) = "location": arr(1) = "request_date": arr(2) = "site"
    arr(3) = "UPC": arr(4) = "Current_LOA": arr(5) = "Req_LOA"
    arr(6) = "You Lost this One!!"
    
    For i = 1 To 11
        Select Case i
            Case 1, 2, 7, 8, 9, 10, 11 '"Text1", "text2", then text7 thru 11.
        
                'to a specific name list?
                'ffNameSpecCln.Add "Specific Name HERE " & i, "Text" & i
                ffNameSpecCln.Add arr(j), "Text" & i
                j = j + 1
        End Select
    Next i
     
    Set ffNameDict = CreateObject("Scripting.Dictionary")
    Set d = ActiveDocument
    For i = 1 To 11
        Select Case i
            Case 1, 2, 7, 8, 9, 10, 11 '"Text1", "text2", then text7 thru 11.
            'ffNameDict("Text" & i) = "Text" & i
            ffNameDict("Text" & i) = ffNameSpecCln.Item("Text" & i)
        End Select
    Next i
    For Each ff In d.FormFields
        If ff.Result = "" And ffNameDict.Exists(ff.Name) Then
            If Not msgShown Then
                MsgBox "Hey you forgot to fill out these fields, they are 'mandatory' so go back and do that please, thanks!", vbExclamation
                msgShown = True
            End If
            Do
'                sInFld = InputBox("Request date required, please fill in below." + vbCr + vbCr + _
                                    "@" + ff.Name + " is the current text fields to fill in !")
                sInFld = InputBox("Request date required, please fill in below." + vbCr + vbCr + _
                                    "@" + ffNameDict(ff.Name) + " is the current text fields to fill in !")
            Loop While sInFld = ""
            ff.Result = sInFld
        End If
    Next ff
    d.Save
End Sub

Oscar, thanks for the input. I didnt get that to work though. Pasted in just like this, it allows me to close the file without intervention though. Thoughts? To clarify, all of the "form fields" are using the legacy forms "text form fields" — Guitarmageddon, Jun 01 '23 at 14:01
@Guitarmageddon Can you share your sample file (the one you said didn't work) with me so I can try it out? Preferably one that can be edited collaboratively. — Oscar Sun, Jun 01 '23 at 14:09
I responded to this main post with a screenshot/link. couldnt add it to this comments thread. does that help any? — Guitarmageddon, Jun 01 '23 at 14:19
To answer your question about the form field I used, yes, that is in fact the correct one — Guitarmageddon, Jun 01 '23 at 14:21
@Guitarmageddon I used this to test either, it should be okay. Otherwise, you will share your sample file with me. Otherwise, I can't do anything about it — Oscar Sun, Jun 01 '23 at 14:31
Ok It seems to be working now, but not sure what changed. In any case, here is a twist. Now that I see the behavior, how can we limit this validation behavior to only the form field descriptions of my original post? So Text1, Text2, and Text7 thru 11. Thoughts? — Guitarmageddon, Jun 01 '23 at 14:34
@Guitarmageddon Do you mean that it only works on the following text fields? "Text1", "text2", then text7 thru 11. Just use their names to judge. It is already in my code. I will update it right away if this is what you want. — Oscar Sun, Jun 01 '23 at 14:40
Two comments: 1. yes, I mean it would only work on those SPECIFIC text fields. I have other names for them, but I can modify the code if needed to name what I like. Just not sure how to specify specific groups of names like that instead of ALL form fields as you did originally. 2. now that I see the behavior of these pop messages, is it possible to ONLY do this behavior on document SAVE versus just closing? The user could have changed their mind and simply want to close, but the way Word pops up these boxes is quite intrusive. I assume if the user saves the file, they intend to submit. — Guitarmageddon, Jun 01 '23 at 14:46
@Guitarmageddon The code has been updated, you can add the name of the text field you want to operate on to this **ffNameDict** variable. It is also possible to check it when saving. But don't you allow these text fields to be left blank? How do you allow the user to close the file without input? — Oscar Sun, Jun 01 '23 at 14:59
Oscar, so we only want this file submitted with these 7 fields completed. So I supposed the user should be able to outright CLOSE without any intervention, but if they are SAVING then they would be intending to submit. So can this same check be done on SAVE instead of close as you have written it? Another thing I am noticing is that each of these 7 fields are referencing a unique name in reality, and simply Text1, Text2, etc is confusing to the user when it pops up. I have a list of 7 unique names for these fields. I see how you have crafted it, can that be modified to a specific name list? — Guitarmageddon, Jun 01 '23 at 15:04
Those explicit names I would instead of the "Text1" etc logic are: location, request_date, site, UPC, Current_LOA, Req_LOA. These would be the exact form field names I would check. Then, the popup box could give better direction to the user — Guitarmageddon, Jun 01 '23 at 15:08
@Guitarmageddon It's ALMOST DONE. But you should know how to run the procedure Register_Event_Handler to Register the Event_Handler to Word Application. This is necessary to trigger the appWord_DocumentBeforeSave event handler. See my updated answer and try to fix to your need. — Oscar Sun, Jun 01 '23 at 15:28
Thank you very much. Hard to tell exactly which subfolders you are referencing since it is not in english. I will work at this — Guitarmageddon, Jun 01 '23 at 15:40
Oscar I no longer would need the "Text1" logic since we are explicitly specifying those field names, right? I think that is causing an issue. I got a compile error once adding all the steps you mentioned above. trying to troubleshoot that... — Guitarmageddon, Jun 01 '23 at 15:58
@Guitarmageddon You just need to add the actual name of your text fields in the corresponding position in the code. The name you gave me before is missing one too (I think there are 7 in total). It's all noted in the code, and it's easy to see. You should not rush to apply it to your actual document, because you did not give me the document you are using, you have to modify and apply it yourself from the logic I gave you. — Oscar Sun, Jun 01 '23 at 16:06
Oscar I think I see how you did the naming thing. It makes more sense to me now looking at it fresh today. One question I have.... it all seems to be working fine. However, what if I user just opens the file with no intent to fully complete? In order to exit, they have to dummy in some data , to get to the prompt questioning them to save the file, then they can elect not to save and then move on. That was my logic in deciding to do this only with document save, and not document close. Does that make sense? — Guitarmageddon, Jun 02 '23 at 12:39
@Guitarmageddon Then you should place some code in the **appWord_DocumentBeforeClose** event. Refer to my updated answer end. — Oscar Sun, Jun 02 '23 at 16:27

MS Word VBA to check for empty text form fields upon file close/exit

1 Answers1

Before closing the document check if it has been modified

Linked