Protected Form – next field and fire on-exit macro (VBA – Word)

Topic

New Reply

In a protected (online) form, I would like a command to go to the next enabled form field, or even to the first form field in the document. If there is an on-exit macro for the field, I want it to fire.

I tried the following based on the Enter key substitution macro:

Private Sub Actor()
' Move to next form field so as to trigger on-exit macros but
'  only if in a formfield in a protected section of the doc
'
    Dim sMyFormField As String
    If ActiveDocument.ProtectionType = wdAllowOnlyFormFields And _
        Selection.Sections(1).ProtectedForForms = True Then
         ' Retrieve the bookmark of the current selection.
         ' This is equivalent to the name of the form field.
        sMyFormField = Selection.Bookmarks(1).Name
         ' Go to the next form field if the current form field
         ' is not the last one in the document.
        If ActiveDocument.FormFields(sMyFormField).Name  _
            ActiveDocument.FormFields(ActiveDocument.FormFields.Count) _
            .Name Then
            ActiveDocument.FormFields sMyFormField).Next.Select
        Else
            ' If the current form field is the last one,
            ' go to the first form field in the document.
            ActiveDocument.FormFields(1).Select
        End If
    End If
End Sub

——–

I get an error 5941 “The requested member of the collection does not exist.”
Debug identifies my the following If statement as the problem:

If ActiveDocument.FormFields(sMyFormField).Name _
ActiveDocument.FormFields(ActiveDocument.FormFields.Count) _
.Name Then

In the Immediate window:
? ActiveDocument.FormFields(Selection.Bookmarks(1).Name).Name
produces an error

? Selection.Bookmarks(1).Name
produces a name: CaseNo

? ActiveDocument.FormFields(ActiveDocument.FormFields.Count).Name
produces a name

Thoughts? Suggestions?

I’m also posting this on the Microsoft vba beginners newsgroup. If they come up with a solution, I’ll share it here, and vice versa.
TIA

Reply | Quote

Replies

Hi Charles,

Without having reproduced the entire scenario, just a couple of questions:

Have you done a test like:

sMyFormField = Selection.Bookmarks(1).Name
MsgBox sMyFormField

to confirm that Selection.Bookmarks(1).Name is reliably returning the name of the current formfield? – The selection object, when operating in a protected forms document, can give some unexpected results.

Does the following give a different result?:

sMyFormField = Selection.FormFields(1).Name

Gary

Reply | Quote

Yes, I have done such tests, with curious results. The tests are what the immediate window statements are about.

? selection.bookmarks(1).name
reports the name assigned to the field for text, dropdown and checkbox fields.

? selection.formfields(1).name
gives an error that the requested member of the collection does not exist if the field is a text field and gives the name of the field if the field is a checkbox or dropdown field

? ActiveDocument.FormFields(Selection.Bookmarks(1).Name).name
gives the same name when the field is a text or dropdown field but reports “the requested member of the collection does not exist” for a checkbox field.

? ActiveDocument.FormFields(Selection.formfields(1).Name).name
gives the name when the field is a checkbox or dropdown field and an error that the requested member of the collection does not exist when the field is a text field

BTW, on the microsoft newsgroup I have been advised that the variable for the formfield name must be declared as a variant rather than a string so I have made that change in my code.

Reply | Quote

Further update:

I was referred to the MVP site for help getting the names of the fields and resolved that anomoly. However, I still can’t get my on-exit macros to fire when I move to the next formfield using the code. (It does move to the next formfield, though.) The code now is:

Sub Actor()
' Move to next form field so as to trigger on-exit macros but
'  only if in a formfield in a protected section of the doc
'
    Dim vMyFormField As Variant
    If ActiveDocument.ProtectionType = wdAllowOnlyFormFields And _
        Selection.Sections(1).ProtectedForForms = True Then
         ' Retrieve the bookmark of the current selection.
         ' This is equivalent to the name of the form field.
        If Selection.FormFields.Count = 1 Then
            'No textbox but a check- or listbox
            vMyFormField = Selection.FormFields(1).Name
        ElseIf Selection.FormFields.Count = 0 And _ 
            Selection.Bookmarks.Count > 0 Then
            vMyFormField = Selection.Bookmarks(Selection.Bookmarks.Count).Name
        End If
         ' Go to the next form field if the current form field
         ' is not the last one in the document.
        If ActiveDocument.FormFields(vMyFormField).Name  _
            ActiveDocument.FormFields(ActiveDocument.FormFields.Count) _
            .Name Then
            ActiveDocument.FormFields(vMyFormField).Next.Select
        Else
            ' If the current form field is the last one,
            ' go to the first form field in the document.
            ActiveDocument.FormFields(1).Select
        End If
    End If
End Sub

Reply | Quote

I got a solution on the Microsoft vba.beginner newsgroup and will post it here soon.

Reply | Quote

> I got a solution on the Microsoft vba.beginner newsgroup and will post it here soon.

OK. I feel that I’ve been patient (grin!).

Charles, I found your post after stumbling over/into a similar problem. I was luckier than you. At 10:00a.m. I was able to loop through all the formfields in my document, at 10:05a.m, after placing a table of contents within the document, I was not able to obtain all the formfields.

I have found that removing the TOC field seems to let me locate all the formfields. That will be my solution for the immediate future!

My guess is that a field that is not a formfield interrupts Word97SR2’s concept of a collection. Although the Count is correct at 36, the loop faults after the 6th formfield, and cannot find the 7th formfield – the one immediately following the {TOC}.

Reply | Quote

I’m working with Office 97 too and I have no problem looping through the FormFields collection.

Dim ffield As FormField
For Each ffield In ActiveDocument.FormFields
Debug.Print ffield.Name
Next ffield

This prints every FormField’s name, with or without a TOC present between two FormFileds.

Reply | Quote

I’m working with Office 97 too and I have no problem looping through the FormFields collection.

Dim ffield As FormField
For Each ffield In ActiveDocument.FormFields
Debug.Print ffield.Name
Next ffield

This prints every FormField’s name, with or without a TOC present between two FormFileds.

Reply | Quote

> I got a solution on the Microsoft vba.beginner newsgroup and will post it here soon.

OK. I feel that I’ve been patient (grin!).

Charles, I found your post after stumbling over/into a similar problem. I was luckier than you. At 10:00a.m. I was able to loop through all the formfields in my document, at 10:05a.m, after placing a table of contents within the document, I was not able to obtain all the formfields.

I have found that removing the TOC field seems to let me locate all the formfields. That will be my solution for the immediate future!

My guess is that a field that is not a formfield interrupts Word97SR2’s concept of a collection. Although the Count is correct at 36, the loop faults after the 6th formfield, and cannot find the 7th formfield – the one immediately following the {TOC}.

Reply | Quote