VBA - Word 2002 form | auto-SUM function for bookmarked table cell

[mokshadavid]

I've created a protected Word 2002 form that allows users to enter data into
text fields. This form will then be printed out and turned in.

I want to SUM 2-3 table cells that are bookmarked income_1, income_2, etc...
and display the result in a TOTAL cell below the values.

I can use a Word SUM Function to SUM the bookmarked values, but I have to
update that manually. This form is for others, so I would like the SUM
function to happen automatically.

Is there VBA code to automatically SUM the values in the cells bookmarked
income_1, income_2, etc...? And display it in another cell? I thought maybe
it could be a BookMark-Change event that will fire the Macro. Or maybe I
could put a button in the form that says "Calculate" and then that would fire
the manual "update" function via VBA code.

I hope you understand what I am asking.

 

[Doug Robbins - Word MVP]

In the properties dialog for each of the formfields into which the user will
enter data, check the Calculate on Exit box. Then the result will be
updated when the user exits from the formfield.

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

 

[mokshadavid]

Excellent suggestion, works great. I didn't know if a protected form would
allow that, but it allows the formula to calculate.

NOW, if I can only find someone who can help me disable the ENTER key in a
word 2002 form, then I will have a great form, that nobody would be albe to
mess up.

Thanks so much for your time and insight.

 

[Jay Freedman]

Excellent suggestion, works great. I didn't know if a protected form would
allow that, but it allows the formula to calculate.

NOW, if I can only find someone who can help me disable the ENTER key in a
word 2002 form, then I will have a great form, that nobody would be albe to
mess up.

Thanks so much for your time and insight.

The article at http://support.microsoft.com/?kbid=211219 shows how to make the
Enter key work the same as the Tab key in a form, to move to the next field.

 

[mokshadavid]

Thanks, yes, I've read that article, but when a user presses enter to "TAB"
to the next form-field, it doesn't apply the set numerical formatting in the
form field, and it doesn't calculate in the SUM function I was asking about.

I just want to totally disable the ENTER key when the document opens. A lot
of programming thought went into that KB article, but I still think something
like "set Enterkey=0" is what I need.

Thanks for your interest.

 

[Doug Robbins - Word MVP]

Replace the macro in the KnowledgeBase Article with:

Sub EnterKeyMacro()
' Check whether the document is protected for forms
If ActiveDocument.ProtectionType <> wdAllowOnlyFormFields
'The document is not protected for forms,
' insert a carriage return.
Selection.TypeText Chr(13)
End If
End Sub

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

 

[mokshadavid]

Thanks Doug, your comment got me focused on that EnterKey() macro, and I
whittled things down from there.

Ok, would someone please help me test this? I think this is the solution
I've been looking for, but before I ~announce~ it, I would like to know if it
works.

My goal: To disable the enter key in a Microsoft Word 2002 table form so
that nothing happens when the user hits the enter key, and they have to
navigate by mouse and TAB key. This is so the form does not get changed.
This is also to retain numerical formatting in my cells that calculate on
field exit. I didn't not want the Enter key to act like a TAB.

Here is what I put in my VBA code:

Sub EnterKeyMacro()
If KeyAscii = 13 Then
cmdOk.Enabled = False
End If
End Sub

It works for me! I have a protected document template form that opens up
protected, and the user form that is generated from the template also still
opens up protected. The best part is that the ENTER key is disabled.

The only downside I see is that I would have to disable the MACRO to update
the template form (in some ways), because the ENTER key is disabled for the
form, original template and copies.

Thanks - mokshadavid

 

[mokshadavid]

Here is what I put in my VBA code to disable the Enter Key in my Word 2002
form. It looks as if it could easily work just as well for any key, if you
know the character number.

Sub EnterKeyMacro()
If KeyAscii = 13 Then
cmdOk.Enabled = False
End If
End Sub

It works for me! I have a protected document template form that opens up
protected, and the user form that is generated from the template also still
opens up protected. The best part is that the ENTER key is disabled in both.

p.s. I don't know why it works. I have no idea what cmdOk.Enabled=False
means, I found it on some forum, and it works better than anything else I've
seen.