VBA Find and Replace User-form Capitalizes Randomly and Copies New Text Multiple Times

2
votes

I have been trying to create a userform that my coworkers can use to update specific parts of a Word document (ie computer parts) without having to search for the parts in the document. Sort of streamlining find and replace.

Using VBA bookmarks and the .Find function I have largely accomplished this task. However, in testing there have been multiple instances where the new part name has either copied over twice (ie I type "Part_Number_1234" and get "Part_Number_12341234") or the new text is capitalized.

I do realize that part of the issue is related to the fact that I am changing text twice when I change the bookmark text and use the find and replace function. I attempted to mitigate this by using the wdFindAsk wrap parameter and moving the text to be changed near the top of the document; however, this only seemed to occasionally solve the problem as it still happens frequently.

I have tried using different wrap parameters and moving the actual text around in the Word document (as Word loops through the entire document in find and replace)

'Changes bookmarks to keep list of all parts

Dim BMRange As Range
Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
BMRange.Text = TextToUse
ActiveDocument.Bookmarks.Add TextToUse, BMRange

'Finds and replaces the specified part with the new part

With Selection.Find
.ClearFormatting
.Text = ComboBox1.Value
.Replacement.ClearFormatting
.Replacement.Text = TextBox1.Value
.Execute Replace:=wdReplaceAll, Forward:=True, _
Wrap:=wdFindAsk
End With

Example of what I'm trying to do with this code:

I want to change part number: "Part_12345"

I select the bookmark with that part number in the user-form and type in the new part number: "Part_Number_12345678"

Result is supposed to change the bookmark and the text in the document to the new value specified.

However, about 50% of the time the result for the body text will be either:

"Part_Number_PART_NUMBER_12345678" or "PART_NUMBER_12345678"

And within that 50% the bookmark text will be:

"Part_Number_12345678678"

(Using wdFindAsk has helped this issue)

The code above are the two instances in my code where text is changed. The first section is so I can use bookmarks to keep a list of new parts. The bottom is to replace the part name in the rest of the document.

1
vbams-worduserform
Could you please use the edit link to add a bit of sample text this would run on, as well as expected and actual results? That will help us determine where the problem is - without the information anything we do will be a bit hit-and-miss... What I'm looking for is a minimal reproducible example in "tech speak" :-) – Cindy Meister
Have you considered using REF fields for the "part names in the document" that are changed using Find/Replace. (Cross-references to the bookmarks.) After updating the fields in the document they would automatically reflect the content of the bookmark - no need to find/replace. Another possibility would be mapped content controls, rather than the bookmark and REF field combination. – Cindy Meister
So REF fields are a way of connecting bookmarks to other portions of the text? So when the bookmark is changed the text in the REF field in question is changed as well? @CindyMeister – user9409531
Yes, that's correct. It's what Word uses internally when you insert a cross-reference to a bookmark. – Cindy Meister
Perfect, I will try to implement it Monday when I'm back at work. I'll update this post with how it goes. Thank you! @CindyMeister – user9409531

1 Answers

1
votes

I realized that I didn't even need my user-form code at all (which is why I was changing the bookmark name, I was populating a list of bookmark names in the user-form). All I needed to do was create the bookmarks and link them to the specified word I wanted to update with an REF field and then create a button to update the fields (using this: "ActiveDocument.Fields.Update"). This approach is way simpler and produces consistent results. Problem solved.