userform submits plaintext to bookmarks - no further calculationpossible

S

Sven

I created a template in Word 2007 that uses formfields (legacy). Every
formfield has its own bookmark, which made it great to calculate
further with these fields. The results of the calculation then again
was used as a bookmark to show up somewhere else in the document.

Problem for the people filling in the template: it wasnt clear which
fields they should edit and which they should just leave alone and let
them be calculated automatically.

So i managed to create a userform in vba with only the necessary
fields. This form replaces the bookmarks in the doc with the text
values from the form. That way it removes the bookmarks and no more
automatic calculations with the variables from the userform are done.
Can I submit fields from the form into fields of the word doc, rather
than replace them?

for example: in the userform txtDays is submitted to bkDays and txtFee
is submitted to bkFee. Then in the word doc I have a field that
calculates (bkDays*bkFee) which gives me bkTotal and that last
bookmark is used in different locations around the doc. Problem is
that bkDays and bkFee are replaced with the textvalue so no more
calculations are done.

I have no vba background so I was happy enough to toy the form
together from tutorials around the web but now i'm a bit stuck and i
have the feeling that i'm awefully close. This is what is used to pass
the data from the userform to the worddoc when clicked:
With ActiveDocument
.Bookmarks("bkDays").Range.Text = txtDays.Value

Kind Regards Sven
 
M

macropod

Hi Sven,

If you set the data-entry formfields' properties to 'calculate on exit', you can probably do whatever calculations you need using
ordinary formula fields. In a document protected for forms, formula fields can't be accessed by the user. Thus, to calculate
'bkTotal', for example:
.. press Ctrl-F9 to create a pair of field braces (ie '{}').
.. between the field braces, type '=bkDays*bkFee', so that you get '{=bkDays*bkFee}'
.. add whatever field formatting switches you need.
If you bookmark the formula field with the 'bkTotal' bookmark name, a simple cross-reference to that bookmark is all you'll need for
the value to be replicated elsewhere in the document.

Note that no vba is needed for this.

For more information how to do a wide range of calculations in Word, check out my Word Field Maths Tutorial, at:
http://www.wopr.com/index.php?showtopic=365442
or
http://www.gmayor.com/downloads.htm#Third_party
 
P

Pesach Shelnitz

Hi Sven,

It is also possible to replace the text in a bookmark without deleting the
bookmark itself and having to recreate it. The following macro demonstrates
how this can be done with your bkDays bookmark.

Sub ReplaceBookmarkText()
Dim newText As String
Dim myRange As Range

newText = InputBox("Type text that will appear at the bookmark.")
Set myRange = ActiveDocument.Bookmarks("byDays").Range
ActiveDocument.Bookmarks("bkDays").Range.InsertBefore newText
myRange.Start = _
ActiveDocument.Bookmarks("bkDays").Start + Len(newText)
myRange.Delete
End Sub
 
S

Sven

You need to recreate the bookmark when you have inserted text. See the
following article for help on how you can do that:http://www.word.mvps.org/FAQs/MacrosVBA/InsertingTextAtBookmark.htm

--
Regards
Lene Fredborg - Microsoft MVP (Word)
DocTools - Denmarkwww.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word










- Tekst uit oorspronkelijk bericht weergeven -

Thanks alot Lene!

i didnt manage to put the different ranges per bookmark in sub routine
as in the last part of your example (i had no clue where to put it)
but i just did 19 times the BMrange and that did the trick too.
 
L

Lene Fredborg

You are welcome. I am glad I could help.

When you get a little more experience with VBA, I am sure you will find out
how you can change your code so that you do not need to repeat the bookmark
code 19 times. If you want to improve your code now, you could post one
sample of the code where you treat the bookmarks and we may be able to show
you how to change the code.

--
Regards
Lene Fredborg - Microsoft MVP (Word)
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top