Solved

MS Word - populate fixed field

Posted on 2013-01-03
11
449 Views
Last Modified: 2013-01-08
Hi,

We have a lot of different contract documents that we use for templates.

The documents contains much repeating text such as company name, contacts and so on.

Is it possible to make a fill in form that are like a sidebar sitting on the right of the main document, where a user fills in his information.

When the user fills in the fields in the form, the filled in text will auto populate the contract document.

Hope you understand.

Thanks!
0
Comment
Question by:agepab
  • 6
  • 5
11 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38740923
There are several techniques that you could use.
For instance you could bookmark any text to be repeated and place Ref fields at the places that it is to be redisplayed.

You could use Form Fields or Content Controls, perhaps with the 'sub' form as a building block. It would be part of the document and not a sidebar.

The nearest that you could get to that would be as a VBA UserForm which could load text on to the document at locations defined by, say, pre-existing bookmarks.
0
 

Author Comment

by:agepab
ID: 38742030
Great thanks:)

I think it would be more user efficient to use a userform to populate the document.

Do you know of any example docs with a VBA userform that I could build it from?

Thanks!
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38742238
The best approach will depend on your exact circumstance. In this one, you need to set Bookmarks in the template. Because Bookmark names must be unique, you will need to group all those expecting the same data, but differentiate between each instance. You can do this by appending a sequential number to each set of names.

E.g. CompanyName_01,  CompanyName_02, CompanyName_03

The code in the Userform would then be like this:
Option Explicit

Private Sub CommandButton1_Click()
    Dim bmk As Bookmark
    For Each bmk In ActiveDocument.Bookmarks
        Select Case Split(bmk.Name, "_")(0)
            Case "CompanyName"
                InsertTextInBookmark bmk, txtCompanyName.Text
            Case "Contact"
                InsertTextInBookmark bmk, txtContact.Text
            Case "Address"
                '...
            'Case ...
        End Select
    Next bmk
End Sub

Sub InsertTextInBookmark(bmk As Bookmark, strText As String)
    Dim rng As Range
    
    Set rng = bmk.Range
    rng.Text = strText
    're-add bookmark in case it was overwritten and
    'to ensure that it contains the text
    rng.Document.Bookmarks.Add bmk.Name, rng
End Sub

Open in new window

0
 

Author Comment

by:agepab
ID: 38750429
Hi,
Thanks, I have made a userform now in my template. I arranged the textfield as I wanted it in the form. I added a 'OK' button where I added the script above with modifications on my field names.

But I have some questions, does the Case "CompanyName" reflect the field name in the userform or the bookmark name?

Here is my code so far, but it don't work, nothing happends when I push "OK" button.

Option Explicit

Private Sub OK_Click()
    Dim bmk As Bookmark
    For Each bmk In ActiveDocument.Bookmarks
        Select Case Split(bmk.Name, "_")(0)

            Case "Radgiver10"
'Case "Radgiver10" is the name of the field in the Userform.

                InsertTextInBookmark bmk, txtRådgiver.Text
'"txtRådgiver.Text" is the bookmark name.

            Case "Radgiver10"
                InsertTextInBookmark bmk, txtRådgiver01.Text
            Case "Radgiver10"
                InsertTextInBookmark bmk, txtRådgiver02.Text
            Case "Oppdragsgiver10"
                InsertTextInBookmark bmk, txtOppdragsgiver.Text
            Case "Oppdragsgiver10"
                InsertTextInBookmark bmk, txtOppdragsgiver02.Text
            Case "Oppdragsgiver10"
                InsertTextInBookmark bmk, txtOppdragsgiver03.Text
            Case "Oppdragsgiver10"
                InsertTextInBookmark bmk, txtOppdragsgiver04.Text
            Case "Oppdragsgiver10"
                InsertTextInBookmark bmk, txtOppdragsgiver05.Text
            Case "Oppdragsgiver10"
                InsertTextInBookmark bmk, txtOppdragsgiver06.Text
            Case "LederFast"
            InsertTextInBookmark bmk, txtStyrelederFast.Text
            Case "LederTime"
                InsertTextInBookmark bmk, txtStyrelederTime.Text
            Case "MedlemFast"
                InsertTextInBookmark bmk, txtStyremedlemFast.Text
            Case "MedlemTime"
                InsertTextInBookmark bmk, txtStyremedlemTime.Text
            Case "RadgiverTime"
                InsertTextInBookmark bmk, txtRadgiverTime.Text
            
        End Select
    Next bmk
End Sub

Sub InsertTextInBookmark(bmk As Bookmark, strText As String)
    Dim rng As Range
    
    Set rng = bmk.Range
    rng.Text = strText
    're-add bookmark in case it was overwritten and
    'to ensure that it contains the text
    rng.Document.Bookmarks.Add bmk.Name, rng
End Sub

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38750523
If you have the same text to be inserted in several places, you will need to bookmark each place. You cannot use exactly the same name for more than one bookmark, but you can fill more than one bookmark from the same text box.

When a match is found in a Select Case block , the action will be done in the Case part, and the block is edited, so line 16 in you code will never be actioned. The same applies to lines 20 to 28, etc.

My suggestion is that you have bookmarks with names like "Radgiver_01",  "Radgiver_02",
"Oppdragsgiver_01",  "Oppdragsgiver_02",  "Oppdragsgiver_03". It doesn't matter what comes after the underscore, provided that the first parts are all the same and the last parts are all different, for any given first part.
Private Sub OK_Click()
    Dim bmk As Bookmark
    For Each bmk In ActiveDocument.Bookmarks
        Select Case Split(bmk.Name, "_")(0)

            Case "Radgiver"
                '"Radgiver" is the first part of the bookmark name on the document.
                InsertTextInBookmark bmk, txtRådgiver.Text
                '"txtRådgiver.Text" is the text from the text box on the user form
            Case "Oppdragsgiver"
                InsertTextInBookmark bmk, txtOppdragsgiver.Text
            Case "LederFast" 'if there is no "_" in the bookmark name, Split(bmk.Name, "_")(0) will return the whole name
                InsertTextInBookmark bmk, txtStyrelederFast.Text
            Case "LederTime"
                InsertTextInBookmark bmk, txtStyrelederTime.Text
            Case "MedlemFast"
                InsertTextInBookmark bmk, txtStyremedlemFast.Text
            Case "MedlemTime"
                InsertTextInBookmark bmk, txtStyremedlemTime.Text
            Case "RadgiverTime"
                InsertTextInBookmark bmk, txtRadgiverTime.Text
            
        End Select
    Next bmk
End Sub

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:agepab
ID: 38752818
Thank you very much, almost there I think, but get a error on the last line:

 rng.Document.Bookmarks.Add bmk.Name, rng

Open in new window


What does the  Next bmk do?

My code is now:

Private Sub OK_Click()

    Dim bmk As Bookmark
    For Each bmk In ActiveDocument.Bookmarks
        Select Case Split(bmk.Name, "_")(0)

            Case "Rådgiver"
                '"Radgiver" is the first part of the bookmark name on the document.
                InsertTextInBookmark bmk, txtRadgiver.Text
                '"txtRadgiver.Text" is the text from the text box on the user form
            Case "Oppdragsgiver"
                InsertTextInBookmark bmk, txtOppdragsgiver.Text
                'if there is no "_" in the bookmark name, Split(bmk.Name, "_")(0) will return the whole name
            Case "Kontaktperson"
                InsertTextInBookmark bmk, txtKontaktperson
            Case "StyrelederFast"
                InsertTextInBookmark bmk, txtLederFast.Text
            Case "StyrelederTime"
                InsertTextInBookmark bmk, txtLederTime.Text
            Case "StyremedlemFast"
                InsertTextInBookmark bmk, txtMedlemFast.Text
            Case "StyremedlemTime"
                InsertTextInBookmark bmk, txtMedlemTime.Text
            Case "RådgiverTime"
                InsertTextInBookmark bmk, txtRadgiverTime.Text
        End Select
    Next bmk
End Sub

Sub InsertTextInBookmark(bmk As Bookmark, strText As String)
    Dim rng As Range
    
    Set rng = bmk.Range
    rng.Text = strText
    're-add bookmark in case it was overwritten and
    'to ensure that it contains the text
    rng.Document.Bookmarks.Add bmk.Name, rng
End Sub

Open in new window


Sorry for my slow learning curv. Hope we can get it working.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38752944
What is the error message and number please?

The 'Next bmk' line matches this one:
    For Each bmk In ActiveDocument.Bookmarks

Open in new window


That is an instruction to walk through each member of the 'ActiveDocument.Bookmarks' collection, setting the variable bmk to that member. The Next instruction tells the code to loop back to the 'For Each' line and set the variable to the next member. The loop continues until the last member of the collection has been processed.
0
 

Author Comment

by:agepab
ID: 38753822
Ok, I see!

I get "Run time error 5825 - object deleted" on line 37. I'll attach the document.
Mal---exchange.doc
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 38754136
There were some errors in the code, two of them were mine

Firstly, if the bookmark object has been deleted in the line
rng.Text = strText

Open in new window

then we can't get its name property. The code now saves the name into a variable and user that to re-add the bookmark.

Secondly, re-adding the bookmark disturbs the bookmarks collection. The For Each depends on the collection being stable, and we have disturbed it, so that the Next bmk doesn't always work properly. The new code uses the index in the collection. This is re-calculated each time, so is more reliable.

Lastly, the text box name 'txtRådgiverTime.Text' was 'txtRadgiverTime.Text' in the code. The little circle was missing above the 'a' - I don't know the name in English, let alone Norwegian. I am sure that you will have found that error if the code had run that far.
Sub OK_Click()

    Dim bmk As Bookmark
    Dim b As Integer
    For b = 1 To ActiveDocument.Bookmarks.Count
        Set bmk = ActiveDocument.Bookmarks(b)
        Select Case Split(bmk.Name, "_")(0)
            Case "Rådgiver"
                '"Radgiver" is the first part of the bookmark name on the document.
                InsertTextInBookmark bmk, txtRadgiver.Text
                '"txtRadgiver.Text" is the text from the text box on the user form
            Case "Oppdragsgiver"
                InsertTextInBookmark bmk, txtOppdragsgiver.Text
                'if there is no "_" in the bookmark name, Split(bmk.Name, "_")(0) will return the whole name
            Case "Kontaktperson"
                InsertTextInBookmark bmk, txtKontaktperson
            Case "StyrelederFast"
                InsertTextInBookmark bmk, txtLederFast.Text
            Case "StyrelederTime"
                InsertTextInBookmark bmk, txtLederTime.Text
            Case "StyremedlemFast"
                InsertTextInBookmark bmk, txtMedlemFast.Text
            Case "StyremedlemTime"
                InsertTextInBookmark bmk, txtMedlemTime.Text
            Case "RådgiverTime"
                InsertTextInBookmark bmk, txtRådgiverTime.Text ' was txtRadgiverTime.Text
        End Select
    Next b
End Sub

Sub InsertTextInBookmark(bmk As Bookmark, strText As String)
    Dim rng As Range
    Dim strName As String
    
    strName = bmk.Name
    Set rng = bmk.Range
    rng.Text = strText
    rng.Document.Bookmarks.Add strName, rng
End Sub

Open in new window

0
 

Author Comment

by:agepab
ID: 38754200
Perfect! Thank you so much :)
0
 

Author Closing Comment

by:agepab
ID: 38754204
Perfect! and thank you!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Like many others, we try and discourage users from printing documents unnecessarily and instead send or share them electronically. However, this doesn't always work and documents are still printed. With this simple solution, if the user tries to …
Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now