Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

VBA function: loop to replace bookmarks with VBA TextBox value

Posted on 2003-11-14
9
2,062 Views
Last Modified: 2008-01-16
Argh ... it's been too long since I programmed!

I have a Word form and need to copy info entered in a VBA Textbox (say, ClientName) to various places in the document (say, each page). I have added bookmarks where I need it copied to and named them ClientName1, ClientName2, ClientName3, and so on.

So, I need a function that goes through the document looking for ClientName bookmarks, and replacing them with the text from ClientName. I've already stolen--er, was inspired by--Dreamboat's code to replace bookmarks (following), but am having difficulty combining it with the looping functionality.

A nice-to-have: since I'll be naming my VBA TextBoxes the same as my bookmarks, I'd like to not hardcode the textbox name -- I can just grab the name of the current textbox and add 1, 2, 3, etc. to it. Therefore, I picture calling the function by merely typing something like PopulateItFunction Me, Me.Value

Am I dreaming?

Tristan

Dreamboat's bookmark replacement code:

Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)
    Dim BMRange As Range
    Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
    BMRange.Text = TextToUse
    ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
End Sub
0
Comment
Question by:TristanRyan
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 37

Assisted Solution

by:Joanne M. Orzech
Joanne M. Orzech earned 35 total points
ID: 9749398
I think it might be a lot easier to just use doc variables instead of bookmarks...but that's my personal preference.

Then you would use code like this:

Dim Client1 as String

ActiveDocument.Variables.Add Name:="Client1", value:=frmForm.Textbox1.Text




HTH
JO
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 9749541
oops...sorry - to elaborate

in your document itself, rather than bookmarks, you would Insert, Field, DocVariable, and name it "Client1"
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 9750127
Sub UpDateFields(ClientName as Textbox)
    ActiveDocument(ClientName.Name & "1").Result = ClientName.Text
    ActiveDocument(ClientName.Name & "2").Result = ClientName.Text
    ActiveDocument(ClientName.Name & "3").Result = ClientName.Text
    'Continue the above pattern until all are filled
End Sub

Sub UpDateFields(ClientName as Textbox)
    For I = 1 to 3 '<- change the 3 to the total number of ClientName fields
        ActiveDocument(ClientName.Name & Cstr(I)).Result = ClientName.Text
    Next I
End Sub

Let me know if more is needed
dragontooth


0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 22

Assisted Solution

by:Dreamboat
Dreamboat earned 25 total points
ID: 9758618
Yeah. And you only need one bookmark. Make the rest of them cross-references to the bookmark.

(Dragon--please validate my statement? I think I'm correct.)

Tristan: I don't write code. I steal it. Wherever that bookmark update code came from, I sure didn't write it! But thanks for the credit anyway! LOL
0
 
LVL 14

Accepted Solution

by:
Tommy Kinard earned 65 total points
ID: 9764666
<Make the rest of them cross-references to the bookmark>
Yes this can be done with cross reference the draw back is the user will have to Update Fields, which could lead to problems when the user forgets to update. But of course we can code that in also.

Sub UpDateFields(ClientName as Textbox)
        ActiveDocument(ClientName.Name).Result = ClientName.Text '<- This line changed since there is only 1 formfield
        ActiveDocument.Fields.Update
End Sub

dragontooth

0
 
LVL 1

Author Comment

by:TristanRyan
ID: 9783089
Well, thanks y'all... I've used a combo of everything. I don't know how one normally gives points out -- if I've committed a faux pas in dividing these points this way, please let me know and I'll post some more points to correct it!

Tristan
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 9783200
Thanks for the Points and the Grade!

No faux pas (?) here!
dragontooth

0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 9783229
Dragon: That's french for screwup. LOL!

Thanks, Tristan!
0
 
LVL 1

Author Comment

by:TristanRyan
ID: 9783395
Thanks for the translation, Dreamboat... I live in Montreal and forget which phrases are commonly used in English and which are understood simply because it's so maddeningly bilingual here. Example discussion between snobs: "Can you believe it? He bought dep wine for the vernissage!" "Wow... what a faux pas." (Dep wine = cheap wine, because that's what's sold in corner stores, called "dépanneurs"; vernissage = art opening).

Ah, the joys of the www.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

789 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