Results 1 to 7 of 7
  1. #1
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts

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

    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:
    <pre>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.FormField s.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
    </pre>

    --------

    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.FormField s.Count) _
    .Name Then


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

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

    ? ActiveDocument.FormFields(ActiveDocument.FormField s.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
    Charles Kyle Kenyon
    Madison, Wisconsin

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

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

    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

  3. #3
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts

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

    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).N ame).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.
    Charles Kyle Kenyon
    Madison, Wisconsin

  4. #4
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts

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

    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:
    <pre>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).Nam e
    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.FormField s.Count) _
    .Name Then
    ActiveDocument.FormFields(vMyFormField).Next.Selec t
    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
    </pre>

    Charles Kyle Kenyon
    Madison, Wisconsin

  5. #5
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts

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

    I got a solution on the Microsoft vba.beginner newsgroup and will post it here soon.
    Charles Kyle Kenyon
    Madison, Wisconsin

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Form - next field and fire on-exit m

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

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Form - next field and fire on-exit m

    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.

Posting Permissions

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