Word VB Duplicate code for Text box

Question

I currently have 10 Text boxes and pretty much the same code for each. I wan't to be able to type a number and have it create that many boxes. I did a lot of research but I am unable to find an easy way to do this. Any Ideas to try? It will have to duplicate the text boxes in my Userform and the code. I was thinking of copy and paste or some kind of loop.

This code puts the filename into word doc containing the text with bookmarks ""

Private Sub CommandButton1_Click()

With ActiveDocument
If .Bookmarks("href1").Range = ".jpg" Then
    .Bookmarks("href1").Range _
    .InsertBefore TextBox1
    .Bookmarks("src1").Range _
    .InsertBefore TextBox1
    .Bookmarks("alt1").Range _
    .InsertBefore TextBox1
 End If
End With

With ActiveDocument
If .Bookmarks("href2").Range = ".jpg" Then
    .Bookmarks("href2").Range _
    .InsertBefore TextBox2
    .Bookmarks("src2").Range _
    .InsertBefore TextBox2
    .Bookmarks("alt2").Range _
    .InsertBefore TextBox2
 End If
End With

With ActiveDocument
If .Bookmarks("href3").Range = ".jpg" Then
    .Bookmarks("href3").Range _
    .InsertBefore TextBox3
    .Bookmarks("src3").Range _
    .InsertBefore TextBox3
    .Bookmarks("alt3").Range _
    .InsertBefore TextBox3
 End If
End With

With ActiveDocument
If .Bookmarks("href4").Range = ".jpg" Then
    .Bookmarks("href4").Range _
    .InsertBefore TextBox4
    .Bookmarks("src4").Range _
    .InsertBefore TextBox4
    .Bookmarks("alt4").Range _
    .InsertBefore TextBox4
 End If
End With

With ActiveDocument
If .Bookmarks("href5").Range = ".jpg" Then
    .Bookmarks("href5").Range _
    .InsertBefore TextBox5
    .Bookmarks("src5").Range _
    .InsertBefore TextBox5
    .Bookmarks("alt5").Range _
    .InsertBefore TextBox5
 End If
End With

With ActiveDocument
If .Bookmarks("href6").Range = ".jpg" Then
    .Bookmarks("href6").Range _
    .InsertBefore TextBox6
    .Bookmarks("src6").Range _
    .InsertBefore TextBox6
    .Bookmarks("alt6").Range _
    .InsertBefore TextBox6
 End If
End With

With ActiveDocument
If .Bookmarks("href7").Range = ".jpg" Then
    .Bookmarks("href7").Range _
    .InsertBefore TextBox7
    .Bookmarks("src7").Range _
    .InsertBefore TextBox7
    .Bookmarks("alt7").Range _
    .InsertBefore TextBox7
 End If
End With

With ActiveDocument
If .Bookmarks("href8").Range = ".jpg" Then
    .Bookmarks("href8").Range _
    .InsertBefore TextBox8
    .Bookmarks("src8").Range _
    .InsertBefore TextBox8
    .Bookmarks("alt8").Range _
    .InsertBefore TextBox8
 End If
End With

With ActiveDocument
If .Bookmarks("href9").Range = ".jpg" Then
    .Bookmarks("href9").Range _
    .InsertBefore TextBox9
    .Bookmarks("src9").Range _
    .InsertBefore TextBox9
    .Bookmarks("alt9").Range _
    .InsertBefore TextBox9
 End If
End With

With ActiveDocument
 If .Bookmarks("href10").Range = ".jpg" Then
    .Bookmarks("href10").Range _
    .InsertBefore TextBox10
    .Bookmarks("src10").Range _
    .InsertBefore TextBox10
    .Bookmarks("alt10").Range _
    .InsertBefore TextBox5
 End If
End With

UserForm1.Hide

    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting

    With Selection.Find
        .Text = ".jpg "
        .Replacement.Text = ".jpg"

        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.HomeKey Unit:=wdLine
    Selection.Find.Execute Replace:=wdReplaceAll



        Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting

    With Selection.Find
        .Text = "/ "
        .Replacement.Text = "/"

        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.HomeKey Unit:=wdLine
    Selection.Find.Execute Replace:=wdReplaceAll



    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting

    With Selection.Find
        .Text = ".jpg.jpg"
        .Replacement.Text = ".jpg"

        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.HomeKey Unit:=wdLine
    Selection.Find.Execute Replace:=wdReplaceAll

End Sub

2nd part of question.

I was able to figure out the 2nd part to my question by the webpage provided by Rich and this other webpage [stackoverflow.com/questions/10544456/] (Dynamically Adding Labels to User Form = Blank UserForm) It also adds multiples as I wanted. I just need to format them now which should be easy. Thanks for all the help.

Private Sub CommandButton3_Click()

Dim theTextbox As Object
Dim textboxCounter As Long

For textboxCounter = 1 To 4
    Set theTextbox = UserForm1.Controls.Add("Forms.Textbox.1", "Test" & textboxCounter, True)
    With theTextbox        

        .Top = 10 * textboxCounter
    End With
Next
End Sub

score 1 · Accepted Answer · answered May 15 '14 at 15:05

You can achieve this by stashing the textbox's in the object array and then iterating through the objects individually.

Sub CommandButton1_Click()
Dim TBs(9) As Object
Set TBs(0) = TextBox1 : Set TBs(1) = TextBox2 : Set TBs(2) = TextBox3
Set TBs(3) = TextBox4 : Set TBs(4) = TextBox5 : Set TBs(5) = TextBox6
Set TBs(6) = TextBox7 : Set TBs(7) = TextBox8 : Set TBs(8) = TextBox9
Set TBs(9) = TextBox10

For i = 0 To 9
    With ActiveDocument
        If .Bookmarks("href" & i + 1).Range = ".jpg" Then
            .Bookmarks("href" & i + 1).Range _
            .InsertBefore TBs(i)
            .Bookmarks("src" & i + 1).Range _
            .InsertBefore TBs(i)
            .Bookmarks("alt" & i + 1).Range _
            .InsertBefore TBs(i)
        End If
    End With
Next

UserForm1.Hide

    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting

    With Selection.Find
        .Text = ".jpg "
        .Replacement.Text = ".jpg"

        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.HomeKey Unit:=wdLine
    Selection.Find.Execute Replace:=wdReplaceAll



        Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting

    With Selection.Find
        .Text = "/ "
        .Replacement.Text = "/"

        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.HomeKey Unit:=wdLine
    Selection.Find.Execute Replace:=wdReplaceAll



    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting

    With Selection.Find
        .Text = ".jpg.jpg"
        .Replacement.Text = ".jpg"

        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.HomeKey Unit:=wdLine
    Selection.Find.Execute Replace:=wdReplaceAll

End Sub

Thanks a whole lot. I will give this a try. Looks good what I see. While waiting is was trying to use the .show .hide method. It works but lots of code duplication. — JohnB, May 15 '14 at 17:43
Rich your code is working great. It will save a lot of time and code. Thank you. The 2nd part of this is the text boxes them selves. Is there a way to enter a number into a text box and give that many text boxes? — JohnB, May 15 '14 at 20:04
Yes, check this website for dynamically adding textbox's: http://www.ozgrid.com/forum/showthread.php?t=115492 ...more specifically: *mikerickson*'s answer. — Rich, May 15 '14 at 20:22
Sorry I didn't explain this before but that page shows how to add to the worksheet its self. I am trying to discover how to add to my Userform. I am trying to use something like "UserForm1.Controls.add.Textbox". Nothing I try seems to be working. — JohnB, May 16 '14 at 13:44
I am getting closer. I finally found this page [link](http://www.ozgrid.com/Excel/free-training/ExcelVBA2/excelvba2lesson3.htm). I now know what the "Me" being used is, which is the Toolbox. I wonder who named it that anyways. :) So I am trying Me.Controls.Add. Still not working. I will let you know if I get any further. — JohnB, May 16 '14 at 13:53
Well this is what I have now but still can't get it to work. I will wait for suggestions. Private Sub CommandButton3_Click() Me.Controls.Add ("UserForm1.Textbox.12") End Sub — JohnB, May 16 '14 at 14:11

Word VB Duplicate code for Text box

1 Answers1