VBA Word - UserForm selection to doc formatting help (find/replace), please

[pk247]

Hi All,

I think I'm at the end of my tether with this and really hope someone can help me perform my "find/replace"'s in the code below so that it runs against the user's selection only.

For lack of experience reasons I just can't figure out why the code is doing a find/replace over the entire document (which is typically over 200 pages and therefore very slow) .

Basically the user double-clicks the selection from the UserForm and it returns to the word doc where the cursor is. The code then makes some updates because certain parts need to be bolded and others not. This is defined using the || symbols and works quite well - but it's slow in large documents.

The main trouble is that the find/replace code is happening over the entire document and can take up to 7-8seconds. It would be just as quick for the user to type the text then, which isn't what I want for various reasons...

Can someone please please help? It would be very much appreciated.

Thanks!

Paul, Ireland

Option Explicit
Private Sub UserForm_Initialize()

Application.ScreenUpdating = False
    Me.ListBox1.ColumnCount = 3
    With Me.ListBox1
        .ColumnWidths = "250, 600, 90"
    End With
    
    Me.ListBox1.FontSize = 10
    Me.ListBox1.FontName = "Tahoma"
       
Dim arrData() As String
Dim sourcedoc As Document
Dim i As Long
Dim j As Long
Dim myitem As Range
Dim m As Long
Dim n As Long
  Application.ScreenUpdating = False
  'Modify the following line to point to your list member file and open the document
  Set sourcedoc = Documents.Open(FileName:="...this in an internal file location..._Source_Doc.doc", ReadOnly:=True, Visible:=False)
  'Get the number of list members (i.e., table rows - 1 if header row is used)
  i = sourcedoc.Tables(2).Rows.Count - 1
  'Get the number of list member attritbutes (i.e., table columns)
  j = sourcedoc.Tables(2).Columns.Count
  'Set the number of columns in the Listbox
  ListBox1.ColumnCount = j
  'Load list members into an array
  ReDim arrData(i - 1, j - 1)
 
  For n = 0 To j - 1
    For m = 0 To i - 1
      Set myitem = sourcedoc.Tables(2).Cell(m + 2, n + 1).Range
      myitem.End = myitem.End - 1
      arrData(m, n) = myitem.Text
    Next m
  Next n
  'Use the .List property to populate the listbox with the array data.
  ListBox1.List = arrData
  'Close the source file
  sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
lbl_Exit:
  Exit Sub
End Sub

''''''''''''''''''this is where the user selections begins, and where I'd appreciate your help:

Private Sub listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long
Dim STYLE As String
Dim oRng As Word.Range
Dim blnFound As Boolean
Dim MYRANGE As Range
STYLE = ""
Dim MyText As String
Application.ScreenUpdating = False

    Selection.TypeText (MyText)
    
  For i = 1 To ListBox1.ColumnCount
    Select Case True
      'Build the combo display
      Case i = ListBox1.ColumnCount - 1
        STYLE = STYLE & ListBox1.Column(i - 1) & " "
      Case i = ListBox1.ColumnCount
        'STYLE = STYLE & ListBox1.Column(i - 1) & vbCr
        STYLE = ListBox1.Column(0) & " " & _
                 ListBox1.Column(2)
        Case Else
        STYLE = STYLE & ListBox1.Column(i - 1) & vbCr '& vbTab
    End Select
  Next i
   Application.ScreenUpdating = False
  Set oRng = Selection.Range
  oRng.Text = "||" & STYLE & "||"
  
  
    'Set MYRANGE = ActiveDocument.Content
    Set MYRANGE = ActiveDocument.Tables(1).Range
    
    
    MYRANGE.Find.Execute FindText:="||" & STYLE & "||", Forward:=True
    If MYRANGE.Find.Found = True Then MYRANGE.Bold = False
    Application.ScreenUpdating = False
    Selection.Find.Text = "||" & STYLE & "||"
    Application.ScreenUpdating = False
    blnFound = Selection.Find.Execute
    Application.ScreenUpdating = False
    If blnFound Then
    Selection.Find.Text = STYLE
        Application.ScreenUpdating = False
        Selection.Find.ClearFormatting
        Selection.Find.Replacement.ClearFormatting
        Selection.Find.Replacement.Font.Bold = False
            With Selection.Range.Find
                .Text = "|||^0032*^0032^0032^0032^0032"
                .Replacement.Text = ""
                .Replacement.Font.Bold = True
                .MatchWildcards = True
                .Execute Replace:=wdReplaceAll
            End With
            With Selection.Range.Find
                .Text = "||| "
                .Replacement.Text = ""
                .MatchWildcards = True
                .Execute Replace:=wdReplaceAll
            End With
   
            With Selection.Range.Find
                .Text = "||"
                .Replacement.Text = ""
                .MatchWildcards = True
                .Execute Replace:=wdReplaceAll
            End With
    
            With Selection.Range.Find
                .Text = "[XXX-C-X]"
                .Replacement.Text = "[XXX-C-X]"
                .Replacement.Font.Bold = True
                .MatchWildcards = True
                '.Wrap = wdFindContinue
                .Format = True
                .MatchCase = False
                .MatchWholeWord = False
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
                .Execute Replace:=wdReplaceAll
            End With
            
    End If
       Application.ScreenUpdating = False
            
    
    Selection.MoveEnd Unit:=wdLine, Count:=1
    Selection.Collapse Direction:=wdCollapseEnd
  
  Application.ScreenUpdating = True
 
lbl_Exit:
  Exit Sub
End Sub

[gmayor]

Your code has some confusing references.

Private Sub listbox1_DblClick starts by typing MyText at the cursor, but it is not clear from the code where the cursor is (i.e. what is selected) and MyText is not defined.
You have used STYLE as a string name. It is not good practice to use VBA terms as variable names. strSTYLE would be preferable.

From there on it all becomes a little foggy. The searches start by looking for a string derived from the list box in a table. Then we are back to Selections again with the selection containing the elusive MyText. Are we supposed to be searching the aforementioned table or the mysterious inserted MyText?

What is the 'selection' text that the searches are supposed to be searching?

[pk247]

Hi Graham,

Thanks very much for taking the time to look at this for me. I know my code seems a bit thrown together (I have only read around the net and just through trial and error got it to this stage).

It would be good if I could show a video demo of it working but hopefully this helps:

User clicks on button in Ribbon

[CODE][Sub Style_Guide_ComboUserForm()

ShorthandUserForm.Show vbModeless

End Sub
/CODE]

Shorthand.JPG

UserForm displays text from this source doc table (this is just a snippet):

H1	H2	H3
| RB ENT	You have entered <entered value>. Or You have entered <option1>/<option2>/<option3>.	[XXX-C-X]
| RB YN	Is this correct? For yes, press 1. For no, press 0.	[XXX-C-X]
| ERR INV	Your entry is invalid.	[XXX-C-X]
| ERR INV DT	The date you entered is invalid.	[XXX-C-X]
| ERR MAX	You have exceeded the maximum number of re-entries. Please contact IXRS technical support for assistance.	[XXX-C-X]

User double-clicks on the first column in the UserForm and it returns the selected text into the current word doc e.g.
RB ENT

then RB ENT [XXX-C-X] transfers onto the screen


The user won't see the entry until my code finishes with it's updates (I'm sure you see this okay).


The code, which I'm struggling with, then is supposed to unBold text whereby a "||" & string & "||" is found and then perform the other replacements.


The point of this is that certain Shorthand "Styles" should be bolded and others should not. At all times the [XXX-C-X] should be bolded. My code is actually bolding all items it finds rather than concentrating solely on what the user had double-clicked on. I use spacing and line bars to create parameters for me to do the find/replaces on and that's why you see the references to "^0032" in the code.: (I can't seem to make this image bigger but if you zoom it should display ok)

Capture source.JPG

Does this help illustrate what I'm doing? You're probably thinking my code could be better written and I'm sure you're right. If you could guide me on how to make it so that the user entry is the only text that is "found/replaced" then that will be a great help.

Thanks!

Paul, Ireland

[gmayor]

Sorry, but I think your explanation has just confused me even more
It appears that your users are clicking into a table and that what they click is the search string. What is not clear is what part of the document you want to search for this string. The whole table? Just that row? Somewhere else? If (as your comments suggest) you only want to process the cell clicked, then search and replace should not be necessary. Just process that cell. However I think there must be more to this than I am seeing.