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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mokshadavid - 27 Jun 2008 21:53 GMT

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.

Reply to this Message

Doug Robbins - Word MVP - 28 Jun 2008 08:37 GMT

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.

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 a protected Word 2002 form that allows users to enter data
> into
[quoted text clipped - 17 lines]
>
> I hope you understand what I am asking.

Reply to this Message

mokshadavid - 28 Jun 2008 21:56 GMT

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.

Reply to this Message

Jay Freedman - 28 Jun 2008 22:24 GMT

>Excellent suggestion, works great. I didn't know if a protected form would
>allow that, but it allows the formula to calculate.
[quoted text clipped - 4 lines]
>
>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.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

Reply to this Message

mokshadavid - 28 Jun 2008 22:48 GMT

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.

> 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.
[quoted text clipped - 4 lines]
> Microsoft Word MVP FAQ: http://word.mvps.org
> Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

Reply to this Message

Doug Robbins - Word MVP - 28 Jun 2008 23:45 GMT

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

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

> Thanks, yes, I've read that article, but when a user presses enter to
> "TAB"
[quoted text clipped - 22 lines]
>> Email cannot be acknowledged; please post all follow-ups to the newsgroup
>> so all may benefit.

Reply to this Message

mokshadavid - 29 Jun 2008 03:03 GMT

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

> Replace the macro in the KnowledgeBase Article with:
>
[quoted text clipped - 6 lines]
> End If
> End Sub

Reply to this Message

mokshadavid - 29 Jun 2008 05:34 GMT

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.

Reply to this Message