Creating a variable string from a multiselect listbox in MS Word

Question

I have created a userform with a multiselect listbox with an "OK" command. When my user makes selections from the listbox and clicks the OK command, I want to create an array (based on the user's selections in the listbox) that I can then loop over for each item in the array as I open multiple files the user has specified.

For example, if my user selects "Client 1" and "Client 3" in my listbox and then selects the "OK" command, I want to create an array from those values and then call up each value in the array in a "find and replace" Sub that replaces, e.g., "Client 1" with "Client 1" (colored red), "Client 3" with "Client 3" (colored red). (The red is so that my other find and replace macro can skip these items by specifying a different color to find for, along with text Client 1, Client 3, etc.)

Reading elsewhere on this site, I created a function to try to generate the array, but I don't know how to get it into and use it in my UserForm Sub.

After finding an answer, below, I deleted the original code I had pasted here, because it was clearly all wrong and won't help anyone.

Additional information about the overall objective: I have already created a macro to do an initial find and replace in multiple files. This macro opens a bunch of files selected by the user and replaces certain client names with the text "Confidential Client". Now, people are asking me if they can exclude certain clients from being replaced. That is why I want to add the userform with a listbox that will let them select clients to exclude.

Please help!

It's not clear what you mean by "a string of variables". Could you please use the edit button under the Question and provide an example? Do you perhaps mean an array of values that you can loop over, executing code on each member? — Cindy Meister
Yes, an array is what I think I want. I edited the question and tried to clarify what I am attempting. — Martha Wiles
OK, I'm still confused :-) If you want an array, then you should change your function to return the array (use "As Variant" instead of "As String"). If you really do want a string, then don't create an array, create a string in the For-Loop, adding each new value to the string: sItems = sItems & ListBoxClients.List(i) & ", " and then return that from the function. — Cindy Meister
Then there's the issue with the "Find and Replace Sub". I understand what you mean, sort of, but it's not clear when this should be called if you don't want it connected to the "OK" button? — Cindy Meister

Martha Wiles Martha Wiles · Accepted Answer · 2016-05-27T00:29:14

So, through much trial and error and googling, I came up with the following solution, which works well for my purpose. First, after clicking F7 on my userform, I added the items to a list array.

Private Sub UserForm_Initialize()
   'Creates and assigns the array to ListBoxClients when the form loads
   With ListBoxClients
   .AddItem "Client 1"
   .AddItem "Client 2"
   .AddItem "Client 3"
   End With
End Sub

Then, I created the following response to my "OK" command. First, it prompts the user to select the files to process and opens the first file:

Private Sub cmdOK_Click()
   Me.Hide
   MsgBox "Click OK to browse and select files to exclude.", vbInformation
   Dim MyDialog As FileDialog, GetStr(1 To 3000) As String '3000 files is the maximum applying this code
   On Error Resume Next
   Set MyDialog = Application.FileDialog(msoFileDialogFilePicker)
   With MyDialog
       .Filters.Clear
       .Filters.Add "All WORD File ", "*.docx", 1
       .AllowMultiSelect = True
   i = 1
   If .Show = -1 Then
   For Each stiSelectedItem In .SelectedItems
   GetStr(i) = stiSelectedItem
   i = i + 1
   Next
   i = i - 1
   End If
   Application.ScreenUpdating = False
   For j = 1 To i Step 1
   Set Doc = Documents.Open(FileName:=GetStr(j), Visible:=True)
   Windows(GetStr(j)).Activate

Then, I loop through the selected items in the array and replace each selected item with the same text, colored red (so my other macro--not shown here--will skip over it when it performs the find and replace):

'Find and replace listbox items in files
Dim ii As Integer
   For ii = 0 To ListBoxClients.ListCount - 1
      If ListBoxClients.Selected(ii) Then
      Selection.Text = ListBoxClients.List(ii)
      Selection.Find.ClearFormatting
      Selection.Find.Replacement.ClearFormatting
      Selection.Find.Replacement.Font.Color = 192
            With Selection.Find
                .Text = Selection.Text
                .Replacement.Text = Selection.Text
                .Forward = True
                .Wrap = wdFindContinue
                .Format = True
                .MatchCase = False
                .MatchWholeWord = False
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
            End With
      Selection.Find.Execute Replace:=wdReplaceAll

Before I finish, I get rid of the selected array item that for reasons unknown is pasted at the top of each of my files:

      ' delete mysterious added text at top of page (figure this out later)
      Selection.HomeKey Unit:=wdStory
      Selection.EndKey Unit:=wdLine, Extend:=wdExtend
      Selection.Delete Unit:=wdCharacter, Count:=1
      End If
   Next ii

Then I have some code that closes the current document and returns it to the top section of the sub to open the next file (and eventually end). I have no idea why "Application.Run macroname:="NEWMACROS" is there, but it works, so I'm not going to delete it.

   Application.Run macroname:="NEWMACROS"
   ActiveDocument.Save
   ActiveWindow.Close
   Next
   Application.ScreenUpdating = True
   End With
End Sub

Finally, I add the code to cancel the form if the user changes his/her mind:

Private Sub cmdCancel_Click()
'User has cancelled so hide the form
Me.Hide
End Sub

That's it. I hope this helps someone else.

Creating a variable string from a multiselect listbox in MS Word

1 Answers