Find and Replace Shape Text in VBA

[wapperdude]

Having the target and replacement text in dbl quotes is a requirement.  I'd have to search to find out how to add them after the fact, but it is do-able. 

I suspect your date entry is causing the macro violation.  You're going to have to do some detective work, manually placing the dbl quotes around the date, etc.  Or, use the single quote to comment things out and make manual, equivalent entries in the macro.  Find what's the problem basically.

[jogjer]

I am not typing a date into the textbox just general text.  Sure I could type text within a string in this replace but that would defeat my purpose.  How can I get the double quotes around it?  I tried to use an ampersand to concatenate it to ' " '  but that didn't work.  Is there any way to get a string variable to work in the replace function or will only a literal double quoted string work?

[wapperdude]

To add the double quotes, here's an example:

vsoMyText = NewText
vsoMyText = Chr(34) & vsoMyText & Chr(34)

In this example, vsoMyText was initially set without quotes.  2nd line adds the quotes.

I was only suggesting the manual entry steps to pinpoint where the problem might be, i.e., the specific line of code, and to use the comment character to minimize the number of lines that might have violations.  Easier to fix one thing at a time.  Obviously, you want to be fully automated.

[jogjer]

I tried this but it didn't solve the problem.  It still gives the same error message.

Private Sub butSubmit_Click()
Dim vsoPage As Visio.Page, vsoShape As Visio.Shape
    Dim vsoCharacters1 As Visio.Characters, vsoStrng, vsoFind, vsoReplace As String
   
    For Each vsoPage In ThisDocument.Pages
        For Each vsoShape In vsoPage.Shapes
            Set vsoCharacters1 = vsoShape.Characters
            vsoStrng = vsoCharacters1.Text
            vsoFind = Chr(34) & Me.txtFind1.Text & Chr(34)
            vsoReplace = Chr(34) & Me.txtReplace1.Text & Chr(34)
            vsoCharacters1.Text = Replace(vsoStrng, vsoFind, vsoReplace)
        Next
    Next

[wapperdude]

I went back to your file, updated the macro, and get the same error.  I was able to pinpoint the problem to the replace function. 

It is not obvious to me why it isn't working.  The only significant difference between your methodology and what I provided is that you call the replace function as code under the UserForm.     For whatever reason, VBA is taking exception to this.   

Well, at least neither of us is imaging things!

Perhaps someone else will jump in on this. 

[wapperdude]

I copied your UserForm and code to a new file and ran it.  The error went away.  Must be some corruption in your present file.

[wapperdude]

The attached file contains the basic solution.  The Userform only has one entry, but that can be easily expanded to your 10 entries.  The macro will search all shapes on all pages, substitutes a new string for an old string.  The Userform code closes the UserForm once all of the substitutions have been completed.

[jogjer]

It works but I find its code a bit confusing is the get1 and new1 referring to the textbox in userform1 or the variables set to "enter text" in the showforms module? So in order to expand this to 10 replaces I have to create the "enter text" variables and textboxes with the same name (which I will change to find1 - 10 and replace1-10) and see if it still works.  Thanks for all your help.  Let's see if I am understanding your code correctly.

[jogjer]

I don't understand the ChgText() module.  What exactly is that doing?  Without knowing how to modify this I can't get one replace to work (much less modify it to do 10 replaces). I get the fun error I've been getting with this whole process in the replace statement: "compile error: wrong number of arguments or invalid property assignment." 

If my understanding of this is correct you are hardcoding the text that is in the shape to "my text".  That defeats the purpose of this macro.  I want to be able to find and replace any text not look for a predefined text in code.

[wapperdude]

Let's start at the beginning, in case others, are interested in this:

The objective was to search thru all shapes, on all pages in a Visio document and change existing text with new text.  The only requirement of the existing text is, it exists.  The macro needs to be able to accept 10 different entries at a time.

This is accomplished with a macro that launches a UserForm into which both the old text and new text may be written.  If the User enters into the old text, something that doesn't exist, then nothing gets changed.  There is nothing hardcoded.  The entire process is launched by pressing the "Replace" button on the 1st page.

My macro deviates  from the above in that the UserForm only does a single entry.     Other than that, it does all of the above.     I'm assuming all of the necessary elements were included with file,    since you said it works, which is two pages, each has two shapes, each shape has three lines of text.  Press the replace button on the 1st page to launch the process.  Type in any part or all of one line of the existing text, type in any desired replacement, and press Submit.  All objects on all pages will now show the old text replaced with new text, and unspecified old text remains unchanged.

Part of the confusion is my fault:  the ChgText() module I used to copy/paste code into the UserForm module.  In the clean up process, I failled to remove it.  Feel free to do so.   

The Details:
UserForm:  Contains TextBoxes for entering both old and new text.  I named the boxes as "Get1" for the old text entry, and "New1" for the new text entry.  You need to expand the form (and re-arrange as desired) to have 9 more boxes of each type).  Rename them as desired for convenience.

UserForm Code:  Requires that there are a total of 10 entries each: vsoNewText1 ... vsoNewText10 and vsoOldText1 ... vsoOldText10.  (My macro does not number the two entries shown.)  All of these should be declared in Dim statements, e.g., Dim vsoNewText1 as string, vsoOldText1 as string. 

Then, in the body of the code, copy and paste the three lines, starting with the line that begins with "Set".  You'll need 10 of these, they should all be within the For/Next loop.  Remember to change the suffix numbers.

At the end of the macro, after the last Next, I show two lines of code.  These may be deleted, or copied and placed as 9 additional pairs.  They refer to the Textboxes in the userform and preset the text to "Enter Text", to indicate to the User what to do.

Module1:  ShowForm()  This is the "liason" code between the "Replace" button and the "UserForm" code.  That's the way VBA works.  Seems dumb to me.  Again, I tried to initialize the form with the entries here.  You may delete these if you desire, except for the UserForm1.Show.  That's necessary.

This Document:  ChgText()  Delete this module, it's leftover.
Replace_Click()  This module is the the "Replace" button on the Visio drawing page.  It calls the ShowForm module.

I think that pretty much summarizes/explains everything and shows what needs to be done to expand to 10 entries.  If you cannot get this to work, and the example is satisfactory, I will fill out the form for 10 entries if necessary, but, that would defeat the learning experience.

Hope this clarifies things.  (No, I did not proof read this post.)

[wapperdude]

Addendum:  I thought the Module1 ShowForm() code was unnecessary, and it is.  Just copy/paste its contents into the Replace_Click() macro, delete the ShowForm reference, and delete the ShowForm() macro.  Makes more sense, eliminates the middle man.   

[jogjer]

Yesterday I got your version to work but not mine.  Today after deleting the unnecessary subs and modules I got mine to work with one replace.  Now I'll try it with 10 replaces.  Thanks again for all your help and your detailed explanation.

[jogjer]

I successfully modified the macro so it does ten find and replaces.  I could have made it more efficient using arrays but I chose not to and just repeat code several times. It also has a background page that has page numbers (page of pages) in the upper right corner, the document name at top (without directory), a place where you can paste a company logo (the original macro had an acxiom logo but I no longer have a copy of that logo) and the document name on the bottom (with the directory).  The file is attached if anyone else wants to use it.  Thanks once again for all the help.

[Peter1958]

Hi, I'm a translator trying to replace a vsd's German phrases with English ones, and found this old post. Despite my lack of VBA, in Word and Excel it was a doddle to record text replacement (including case-sensitivity), but Visio won't play. I'm not interested in forms and just 10 replacements, but rather in hundreds of replacements from a list. If I knew the code fragment for one replacement explicitly stated (e.g. "Übersetzer"->"translators") I could generate 100s of them in Excel from a couple of columns of translation pairs. Can anyone help please?