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) :banghead:.

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]

16198

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)

16200

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.