Code for Back Tab

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Linda Aldrich - 15 Feb 2008 01:17 GMT

Greetings,

I'm new to building macros using the Visual Basic Editor. I don't know if
this is the correct forum but haven't been able to find one for setting up
Word tables as end-user input forms. I've researched the use of a 'user
form' as a front end input to the Word form but the way that this form will
be used and re-used, the user-input form called from a template will not
work.

I've built the form utilizing a Word table and added an exit macro to Tab
Down through columns in the table using Method 2 of the "How to set the "tab
order" of a Word form" article contributed by Dave Rado and Mark Tangard.
http://word.mvps.org/FAQs/TblsFldsFms/SetTabOrder.htm

Currently if you start through the script, you cannot click out of the cell
or back tab to correct a mistake. Is there a way to let the end-user go
back to the previous cell if they need too? If so, would it be possible to
send me a snippet that I can add to the code?

Thank you!

L Aldrich

Reply to this Message

Doug Robbins - Word MVP - 15 Feb 2008 02:17 GMT

As the article by Dave Rado and Mark Tangard discusses, the method has the
very disadvantage that you are trying to overcome and the use of a UserForm
is really the only alternative.

Can you say why that would not work for your situation, or perhaps better,
explain in detail what that situation is so the we may be able to suggest a
way in which a userform could be made to work.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Greetings,
>
[quoted text clipped - 18 lines]
>
> L Aldrich

Reply to this Message

Tony Strazzeri - 17 Feb 2008 00:37 GMT

I support the userform approach. It is far better even if it appears
more complicated at first. The formfield approach doesn't lend itself
to anything sophisticated.

I have recently created a timesheet template for a customer that
sounds similar in some ways to what you may be trying to do. I gather
you need to be able to go back into the document to add/edit existing
entries. This need not be too hard to do. I implemented this in my
timesheet template.

I explain how I did this -not to promote myself, but to demonstrate
what is possible. Let me explain the general process. If you are
interested and need help with any aspect feel free to post back.

I created this template to store the data in a table in the document
but fully controlled the input from a userform. This gave me the
advantage of a simple layout but with sophisticated data input and
editing (using dateTimePicker control, scroll buttons etc.).

The data is stored in a table in the document. I lock the document
(for forms) even though I DO NOT USE formfields, so that the user
cannot directly edit the data.

To add/edit the data I provide a macrobutton on the document. This
launches my userform.

When the user creates a new document from the template they will get a
blank table. Thereafter the document is saved as a document, and when
reopened the previous data rows (except for header and total row) are
read from the document and used to populate a listview on the userform
with any existing data. This could be a listbox but I chose to use
the listview because you can resize the column widths.

Data is entered into individual controls which do all sorts of
validation. A button then adds the data to the list.

Another button allows any item on the list to be disassembled back to
the individual fields for editing.

Finally the user clicks on a button which replaces all of the rows in
the table with the data from the userform list.

I hope this helps you consider this approach and the benefits it can
provide. Feel free to post back with specific questions or issues you
may have.

Cheers
TonyS.

Reply to this Message

Linda Aldrich - 29 Feb 2008 14:31 GMT

I am a novice at this but would like to give your method below a try...
Where do I start?

>I support the userform approach. It is far better even if it appears
> more complicated at first. The formfield approach doesn't lend itself
[quoted text clipped - 44 lines]
> Cheers
> TonyS.

Reply to this Message

fumei - 29 Feb 2008 18:46 GMT

You need to spec out in detail what: "but the way that this form will
be used and re-used, the user-input form called from a template will not
work." really means.

Why not?

>I am a novice at this but would like to give your method below a try...
>Where do I start?
[quoted text clipped - 4 lines]
>> Cheers
>> TonyS.

Reply to this Message

Linda Aldrich - 10 Mar 2008 21:01 GMT

the form will initially be opened from a template folder in succession frm a
series of other self care documents. This form will be completed by the
nurse practicioner on a shared network drive or be e-mailed, or burned to a
CD and sent to/with the patient home to be completed, the patient will reply
by e-mail and the form will be saved up to the network in a 'patient name'
folder. If the nurse practioner completes the initial form, she will save
the document into a 'patient name' network folder on the server. If a
patient completes the initial form, the patient will save the form locally,
and then e-mail the form back to the nurse practicioner who will save it to
the 'patient name' network folder.

The form will be re-opened upon each visit of the patient and a new column
of laboratry results will be entered or changes will be made to a previously
entered value.

I hope this helps...

Thank you!

Linda Aldrich

> You need to spec out in detail what: "but the way that this form will
> be used and re-used, the user-input form called from a template will not
[quoted text clipped - 10 lines]
>>> Cheers
>>> TonyS.

Reply to this Message

Linda Aldrich - 13 Mar 2008 15:58 GMT

I've created the userform for the first column (no macrobutton's have been
created yet), created the autonew macro to show the userform, re-protected
the template, saved, and opened the template from New. I entered the data
into the fields on the userform and clicked on the command button to
populate the form and I get an Error 4605. What am I doing wrong?

>I support the userform approach. It is far better even if it appears
> more complicated at first. The formfield approach doesn't lend itself
[quoted text clipped - 44 lines]
> Cheers
> TonyS.

Reply to this Message

Doug Robbins - Word MVP - 13 Mar 2008 20:38 GMT

What does the error message say and if you click on Debug, what line of code
is highlighted.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> I've created the userform for the first column (no macrobutton's have been
> created yet), created the autonew macro to show the userform, re-protected
[quoted text clipped - 50 lines]
>> Cheers
>> TonyS.

Reply to this Message

Linda Aldrich - 14 Mar 2008 15:25 GMT

I figured out the reason. It was because the document was protected. When
I unprotected the document and re-opened it worked. I would like to
protect the document before it is displayed - is that possible? Also, I
finished building the form and have changed the command button name to
'cmdUpdate' from 'cmdSave' and now I am getting the following error:
Compile error: Invalid use of property. when running this set of code:

Private Sub cmdUpdate_Click()
With ActiveDocument
.Bookmarks("txtLName").Range _
.InsertBefore txtLName
.Bookmarks("txtFName").Range _
.InsertBefore txtFName
.Bookmarks("dte1").Range _

Thank you for your help...

Linda Aldrich

_____________________

> What does the error message say and if you click on Debug, what line of
> code is highlighted.
[quoted text clipped - 54 lines]
>>> Cheers
>>> TonyS.

Reply to this Message

Linda Aldrich - 14 Mar 2008 21:48 GMT

I think I have this question answered -- I created a macro to generate the
code below to protect a document after leaving the userform:

ActiveDocument.Protect Password:="", NoReset:=False, Type:= _
wdAllowOnlyFormFields

>I figured out the reason. It was because the document was protected. When
>I unprotected the document and re-opened it worked. I would like to
[quoted text clipped - 74 lines]
>>>> Cheers
>>>> TonyS.

Reply to this Message

Linda Aldrich - 14 Mar 2008 15:56 GMT

I found the resolution to the Compile Error. I had a space before my
commented text. Once I removed the space, the code ran fine. Still looking
forward to help with the macrobutton for the follow-on columns.

And, another question to boot! I ran the UserForm1 and populated some of
the fields on the form, but would like to be able to finish populating those
fields from the userform if the user has updates later. When I ran Tools,
Macros, Autonew, it showed the UserForm1 with the previously entered values,
which I thought good, it saved them in the form, but now -- I would like to
add values to two more fields and select cmdUpdate. When I did, it
re-inserted a second set of values for all of the previously populated
fields. Is there a way that I can tell it to replace the text in the
bookmark with the new value?

Thanks again for all of your help.

Linda Aldrich

> What does the error message say and if you click on Debug, what line of
> code is highlighted.
[quoted text clipped - 54 lines]
>>> Cheers
>>> TonyS.

Reply to this Message

Doug Robbins - Word MVP - 15 Mar 2008 02:56 GMT

If you want to reload the form with data/add additional data to the form
like you are doing, it would be better to have the information displayed in
the document by the use of { DOCVARIABLE varname } fields and set the values
of the variables to the data entered into the form.

Say you had a control on your form into which the patient's name was added
and that you have named that control txtPatientName, then in the template,
where you want the patient's name to appear, you would insert the following
field

{ DOCVARIABLE varPatientName }

and, in the CommandButton Click event, you would have the following code

With ActiveDocument
If len(txtPatientName) > 0 Then
.Variables("varPatientName").Value = txtPatientName
Else
.Variables("varPatientName").Value = " "
End If

'similarly for the other controls/variables
.Fields.Update
End With

The reason for the If ... Else ... End If statement is to prevent the
DOCVARIABLE field from displaying an error if its value is Nul. The
.Fields.Update is to update the fields in the document so that the
DOCVARIABLE fields show the values of the variables.

This way, the data will not be duplicated as is being done with the
bookmarks.

When you are reloading the form, to have the data that was already contained
in variables in the document, you would use the following code in the
Initialize() event of the form

With ActiveDocument
txtPatientName.Text = .Variables("varPatientName").Value

'Similarly for the other controls/variables

End With

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

>I found the resolution to the Compile Error. I had a space before my
>commented text. Once I removed the space, the code ran fine. Still
[quoted text clipped - 72 lines]
>>>> Cheers
>>>> TonyS.

Reply to this Message

Rate this thread: