Microsoft VBa Template and Repeating data

Question

I have spent hours trying to figure out how to get data in my template to duplicate itself

I cannot get the cross reference to work

Can someone PLEASE help me with the coding and let me know what I need to change or add on my user form. I just want to be able to repeat "CandidateFirstName" a number of times on the document

This is what I have todate

Private Sub CommandButton1_Click()

Dim CandidateFirstName As Range

Set CandidateFirstName = ActiveDocument.Bookmarks("CandidateFirstName").Range

CandidateFirstName.Text = Me.TextBox1.Value

Dim CandidateSurname As Range

Set CandidateSurname = ActiveDocument.Bookmarks("CandidateSurname").Range

CandidateSurname.Text = Me.TextBox2.Value

Dim JobTitle As Range

Set JobTitle = ActiveDocument.Bookmarks("JobTitle").Range

JobTitle.Text = Me.TextBox3.Value

Dim ContactFullName As Range

Set ContactFullName = ActiveDocument.Bookmarks("ContactFullName").Range

ContactFullName.Text = Me.TextBox4.Value

Dim ContactTitle As Range

Set ContactTitle = ActiveDocument.Bookmarks("ContactTitle").Range

ContactTitle.Text = Me.TextBox5.Value

Dim CompanyName As Range

Set CompanyName = ActiveDocument.Bookmarks("CompanyName").Range

CompanyName.Text = Me.TextBox6.Value

Dim ConsultantName As Range

Set ConsultantName = ActiveDocument.Bookmarks("ConsultantName").Range

ConsultantName.Text = Me.TextBox7.Value

Me.Repaint

UserForm1.Hide

End Sub

THANKS

Answer 1

It is all too easy to delete bookmarks from a document and then your cross-references will not work.  I suggest that you use DOCVARIABLE fields as, unlike bookmarks, each one of which must have a unique name, you can insert as many of each (e.g. { DOCVARIABLE CandidateFirstName } ) as you need.

Answer 2

On the actual Word Document, when I try insert cross reference with the other "CandidateFirstName" it does not seem to work not sure why

The original CandidateFirstName bookmark populates, but the others don't

It is not possible to have more than one bookmark with the same name, so I don't know what you're doing there.

If you have a bookmark named CandidateFirstName, then all you need to do for the code I posted to work is to insert cross-references to that bookmark, via Insert|cross-reference - you do not need to uncheck "insert as hyperlink".

Answer 3

Thank you

I change the coding and it still does not work

On the actual Word Document, when I try insert cross reference with the other "CandidateFirstName" it does not seem to work not sure why

The original CandidateFirstName bookmark populates, but the others don't

The process I have used to cross reference is as follows:

Highlight second reference "CandidateFirstNAme", select cross reference, select Bookmark, and bookmark text and unselect "insert as hyperlink" and click insert

?!?!?

Any ideas of what I am doing wrong?

thanks again

Answer 4

Presumably your document has cross-references to some or all these bookmarks, but there's no indication of that in your code. To be able to update any cross-references to your bookmarks, you need to actually update the bookmark content; what your code does is to insert content after them. You then need to update the cross-references. Try:

Private Sub CommandButton1_Click()

Call UpdateBookmark("CandidateFirstName", Me.TextBox1.Value)

Call UpdateBookmark("CandidateSurname", Me.TextBox2.Value)

Call UpdateBookmark("JobTitle", Me.TextBox3.Value)

Call UpdateBookmark("ContactFullName", Me.TextBox4.Value)

Call UpdateBookmark("ContactTitle", Me.TextBox5.Value)

Call UpdateBookmark("CompanyName", Me.TextBox6.Value)

Call UpdateBookmark("ConsultantName", Me.TextBox7.Value)

ActiveDocument.Fields.Update

UserForm1.Hide

End Sub

Sub UpdateBookmark(StrBkMk As String, StrTxt As String)

Dim BkMkRng As Range

With ActiveDocument

  If .Bookmarks.Exists(StrBkMk) Then

    Set BkMkRng = .Bookmarks(StrBkMk).Range

    BkMkRng.Text = StrTxt

    .Bookmarks.Add StrBkMk, BkMkRng

  End If

End With

Set BkMkRng = Nothing

End Sub

Answer 5

In the various locations that you want the data to appear, I would insert the following fields

{ DOCVARIABLE CandidateFirstName }

{ DOCVARIABLE CandidateSurname }

{ DOCVARIABLE JobTitle }

{ DOCVARIABLE ContactFullName }

{ DOCVARIABLE ContactTitle }

{ DOCVARIABLE CompanyName }

{ DOCVARIABLE ConsultantName }

using CTRL+F9 to insert each pair of field delimiters and then replace your code with

With ActiveDocument

    .Variables("CandidateFirstName").Value = Me.TextBox1.Value

    .Variables("CandidateSurname").Value = Me.TextBox2.Value

    .Variables("JobTitle").Value = Me.TextBox3.Value

    .Variables("ContactFullName").Value = Me.TextBox4.Value

    .Variables("ContactTitle").Value = Me.TextBox5.Value

    .Variables("ConsultantName").Value = Me.TextBox6.Value

    .PrintPreview

    .ClosePrintPreview

End With

Me.Hide

I would also replace the names of the controls with names the relate to there purpose.  For example instead of TextBox1, rename it txtCandidateName

For more on UserForms, see the following page of Greg Maxey's website

http://gregmaxey.mvps.org/Create\_and\_employ\_a\_UserForm.htm