Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA function: loop to replace bookmarks with VBA TextBox value

Posted on 2003-11-14
9
Medium Priority
?
2,083 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
[X]
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
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 37

Assisted Solution

by:Joanne M. Orzech
Joanne M. Orzech earned 140 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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 22

Assisted Solution

by:Dreamboat
Dreamboat earned 100 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 260 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

If you work with Word a lot, you probably use styles. If you use styles a lot, you've probably balled your fist more often than not when working with the ribbon. In Word 2007/2010, one of the things that I find missing when using styles is a quic…
I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Suggested Courses

670 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