creating a macro that resets (blanks) a form's fields 2

[kawnz]

I have a Word document with a form, that is protected so that the form works by tabbing through the fields.

It is a form that I might use 2 or 3 times a day, so I often leave it open.

when I reach the last field in the form, and hit tab - it goes to the top... I would like, on hitting tab on that last field, that a dialog pops up asking if I would like to clear the form. If I hit yes, then I would like to default to an empty form (i.e. clear the fields of information). If I hit no, then the form remains as it was before I hit tab (that's just in case I hit it accidentally)

Is it possible to create a macro to do this? I am not familiar with the language. I hope I have explained what I want the form to do...

Thanks for any help

 

[GeekGirlau]

Kawnz,

Word doesn't have a Clear option for form fields, but there is a way around this. This code uses the annoying feature in Word where you lose all data in the fields if you unprotect the form then protect it again. The default answer to the question is "No" in case you accidently hit Enter without thinking about it.

Copy this macro, then in the properties for the last field in your form, set the name of the macro in the Run macro on Exit section.

Sub ClearAll()
    If vbYes = MsgBox("Do you want to clear the form?", _
                    vbQuestion + vbYesNo + vbDefaultButton2, _
                    "Clear Form?") Then
        ActiveDocument.Unprotect
        ActiveDocument.Protect wdAllowOnlyFormFields
    End If
End Sub

 

[fumei]

GeekGirlau is semi-correct. But there is a Clear for dropdowns. The following can be put as the exit macro for the last formfield. Clicking Yes will clear all formfields, No will not.

Sub ClearAllFormFields()
Dim myFormField As FormField
Dim aDoc As Document
Set aDoc = ActiveDocument
Dim response
Dim msg As String
msg = "Do you want to clear all formfield results?"
response = MsgBox(msg, vbYesNo)
If response = vbYes Then
    For Each myFormField In aDoc.FormFields()
        Select Case myFormField.Type
            Case 70  ' text formfield
                myFormField.result = ""
            Case 71  ' check formfield
                myFormField.result = False
            Case 83   ' dropdown formfield
                myFormField.DropDown.ListEntries.Clear
        End Select
    Next
Else
End If
set adoc = Nothing
End Sub

Gerry

 

[fumei]

Oooops, I made a major error.....

The above will fail on recreating the OnExit macro if the last formfield is a drop down. I am working on it. Should post back up within an a (hopefully) short time. Please do not run the above if the last formfield is a dropdown.

My apologies.

Gerry

 

[fumei]

OK. Here is the proper code.

Sub ClearAllFormFields()
Dim myFormField As FormField
Dim aDoc As Document
Dim response
Dim msg As String

Set aDoc = ActiveDocument
msg = "Do you want to clear all formfield results?"
response = MsgBox(msg, vbYesNo)
If response = vbYes Then
    For Each myFormField In aDoc.FormFields()
        Select Case myFormField.Type
            Case 70  ' text
                myFormField.result = ""
            Case 71  ' check
                myFormField.result = False
             Case 83   ' dropdown
'  was myFormField.DropDown.ListEntries.Clear
'  should be the following, resetting to Value (Item) 1
                myFormField.DropDown.Value = 1
        End Select
    Next
Else
End If
Set aDoc = Nothing
End Sub

Works now.

Gerry

 

[fumei]

You wrote in thread68-862111:

sorry to be a pain, but when the macro resets the form, it also resets the default fields. is there a way to have it look for defaults and leave them there?

Yes, it does. I am really trying to help here. Could you explain what you mean by look for defaults and leave them there?

Does that mean you want it to look for the defaults and NOT return the formfield to that? In that case, why have a default?

I guess I also have to ask...what are you putting in as defaults. In my testing example, I did not use any. But say:

text formfield: default text = "this text"
the user changes it to "my text"

The clearing macro will clear "my text" and return it to the default of "this text". That is what a default is for. What do you want to happen?

What does

look for defaults and leave them there

mean?

Gerry

 

[kawnz]

let me explain what the form is for... i work in a billing dept, and a number of people in the dept need to send adjustment requests. these requests are completed by one of two people only. We don't have a centralized shared drive, so everyone is saving their forms in their own personal drive, so that the form travels with them where they go.

On this form, we have a "requested by" field, where the name of the person goes, that usually uses the form. I had it set as a form field, rather than hard code it into the document, people could modify that field if need be. I discussed with several people here whether I should justhave them hard code their names in there, but many feel that just in case someone is filling in for them, or using their computer, that they be able to attach their own name in that field.

I guess what we really need here is for the reset function to skip that requester field when it resets the form, so that people don't have to keep entering their name.

I do like the function of checking the default field to see if the name entered matches the default... may need that in future.

I hope I have explained things a little clearer. I really do appreciate your help on this. I am not used to the etiquette on this forum yet. Thanks for helping.

 

[dcompto]

See both of the following:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;191028

and

http://support.microsoft.com/default.aspx?scid=kb;en-us;191027&Product=wrd20

 

[fumei]

Not a problem. And, a fuller explanation also helps a lot! It helps all of us, as it makes it clear what someone actually wants.

OK. Let me check that I have this. One of the fields is a requestor field, in that the data in it is the person requesting...whatever.

Yes, we can do this.

In the clearing code, where it checks for a text formfield (in the Select Case Type = 70) change:

Case 70 ' text
myFormField.Result = ""

to:

Case 70 ' text
If myFormField.Name <> "RequestingName" Then
myFormField.Result = ""
End If

This will check if the formfield name is RequestingName, and if it is not, it will reset to blank. Therefore, if it is, it will leave it alone.

Here is a perfect example of why it is good practice to explicitly name things. Pardon me if I rant.

Word names formfields in the sequence they appear in the document. Text1, Text2, Text3. Suppose the text formfield you are using for the requesting name is Text3. Also suppose you had the code:

If myFormField.Name <> "Text3" Then
myFormField.Result = ""
End If

This would also work, as long as you never, ever, change the order. If you put another text formfield before it, or move it down the sequence order, there may still be a Text3...but it will not be the one you want!

Say you move to the top of the document. If you do not explicitly name it, that is, if you accept Word's default name, then its name would become Text1. Automatically. But your code would act on Text3.

It is much more work, but it is very, very, good practice to always name things so they mean something. Both to you, and to anyone else that may come after.

There, I have done my rant for the day. Oh, and of course you do not have to name that formfield RequestingName. You can name it whatever you want, just as long as it makes sense, describes it reasonably well. ReqName is good. Whatever.

Hope this helps.

Gerry

 

[fumei]

decompto - good articles, but for this question, there is no need to unprotect, reprotect. In fact for this code, it does not occur. The values can be retained within the code.

However, the articles are very useful for the cases where unprotecting and reprotecting is in fact required. Thanks for posting them.


Gerry

 

[kawnz]

Thank you ever so much Gerry I am sure you made everyone in our office quite happy


Another functionality I was asked to add:
to have the option of creating (i.e. duplicating) a blank form on the next page so that they can print more forms at one time. I created a macro that inserts a new page using the macro recorder:

Sub create_new_form()
Selection.InsertBreak Type:=wdPageBreak
    Application.DisplayAutoCompleteTips = True
    ActiveDocument.AttachedTemplate.AutoTextEntries("ADJ_FORM").Insert Where:= _
        Selection.Range
End Sub

but now I need to insert that somewhere within script or macro (?) so that it retains the input on form(s) already filled out.


and of course, since we are dealing with computers that often crash (or people close docs w/o realizing they have unsaved/unprinted forms there) is there a way to temporarily save files, say, every 5 forms, and then delete once the forms have been printed? not priority, just a question on if it can be done (and of course, if it can... how )

Thanks again Gerry... I do hope to be studying this macro topic more.

 

[fumei]

OK, think about this. You want to be able to print multiple copies of the same form? That is, the completed form with the information filled in?

If that is the case, then good design requires a solution to what you want to do.

What you want to do is print multiple copies, not make extra pages with the same information.

Add a dropdown with items 1, 2, 3, 4, 5....
Put text beside it like "Print extra copies"

Have code that picks up the value of the dropdown (default = 1 ie. single copy) and print out that number. If they select 5, it will print out 5 copies.

You wrote:

blank form on the next page so that they can print more forms at one time

Keep thinking. Why would they want to print blank forms? The form is filled out electronically. Of what use is printed out blank forms. I am not saying you can not do this, but why would you?


Gerry

 

[kawnz]

no... we don't want to print more than one copy of the same form. we want to be able to continue to create more forms within the same file... but I don't want to have, say 10 blank forms already in the doc at all times, because then people would be printing out 10 pages every time they open the doc.

When they get to the end of form 1... I would like to have the option to leave form 1 where it is (i.e. leave the info in there), and create form 2, which is just a blank form, ready to be filled out.

And then when I am finished with form 2, I would like to have a blank form 3 on the screen, leaving form 1 and form 2 in tact.

Now why is this?

We share 2 printers among 40 people. These particular forms have to be printed on colored papers. At the moment, we have to print each form as we finish filling it out, set the printer to manual feed, then walk to the printer, with a yellow piece of paper in our hand, and feed the printer that yellow piece of paper, get our one form, and then go back to our desks. Even having several forms open at once, and printing them all around the same time, creates a problem of - am I the only one printing something, is someone else going to print on my yellow piece of paper... all that stupid stuff.

So... they would like to have the form recreate itself after the first one, say, up till 10, and then print all at once, with 10 pieces of yellow paper in hand, ready to print.

I hope this makes sense.

dcompto sent links to the unprotect/protect codes... and I have the macro code for the inserting a new form on a new page...

How do I run these together, so that when we have the option to create a new page, it first unprotects the document, then creates the form on the new page, then reprotects the document, all the while leaving the previously completed forms with information still filled out.

I hope this helps.

 

[kawnz]

ok... I have gotten this far...

I have a macro that unprotects, I also have a macro that protects. I have a macro that generates a new form:

Sub create_new_form()
    Selection.HomeKey Unit:=wdStory
    Application.DisplayAutoCompleteTips = True
    ActiveDocument.AttachedTemplate.AutoTextEntries("ADJ_FORM").Insert Where:= _
        Selection.Range
    Selection.InsertBreak Type:=wdPageBreak
End Sub

I then created another macro that runs these three using

Sub generate_new_form()
    Application.Run MacroName:="ToolsProtectUnprotectDocument"
    Application.Run MacroName:="create_new_form"
    Application.Run MacroName:="ProtectForm"
End Sub

It all works, except I am perplexed by something. The first time it runs, it generates an empty form (form 2) before the first form, but starts the file again at the first field in form 1. When I go through it again, it works fine, and generates form 3 before form 1 and form 4 before form 3... What I think it is doing it going to the second form on the page each time.



You might wonder why I have it going to the top of the file, then going creating a new form above the first one, instead of going to the end of the page and creating a new form. I found when I went to the end of the page, when it creates a new form, it keeps defaulting to the first form (form 1) as the fill in form. When I put the new form in above form 1, it goes to the blank form to fill in. I know there is a better way to do this, but this seemed to work.

 

[kawnz]

OK, figured it out, what it was doing was going to the form, after the cursor, which in this case, was the second form. I added another ctrl+home in there and now it goes to the beginning of the file, ready for a new form to start Thanks again for all your help.

 

[kawnz]

Just an update on how far I got...

I have the forms creating in ascending order (1 at the top, 10 at the bottom), and added a feature that automatically prints the file (to manual feed) at 10 pages, and resets the document back down to 1 form on 1 page, ready for the user to fill again.

I decided to set a limit to 10 forms/pages per file, so that data loss from not saving, or crashing machines would be limited, and the printing of the forms would be timely.

By setting it to automatically print with manual feed, it stops the user needing to know how to set the paper source when printing, and prevents the waste of paper. I am trying to take as much think work away from the users of this form as many are not used to working with Office.

Thank you again for your help today, especially Gerry

 

[Dreamboat]

Thank you ever so much Gerry I am sure you made everyone in our office quite happy

Gerry has a way of doing that...

Anne Troy
Killer Coder Live Chat!

http://www.vbaexpress.com/forum/showthread.php?p=2121