VBA to Find and Format Text string defined using Inputbox within selection

sistemalan · #1 09-29-2014, 09:56 AM

Hi all,

I am a new but keen convert to the wonderful world of VBA, and am hoping that some kind and knowledgeable person might be able to give me a quick bit of help.

To help with the timetabling of staff and volunteers for a children's after school club I need to create a macro which will function as follows:

Before the macro is read I will manually select a chunk of text
I run the macro. An Input box appears. I type someone's name into it and hit OK.
The Macro searches within the highlighted selection and formats each occurence of the inputted name (red and strikethrough)

This will provide a quick way of fool-proofing and speeding up a task which I currently have to do manually very often, namely seeing who is off on a certain day and making sure they are marked as such on the timetable so that I can see what gaps need to be filled.

Huge gratitude for anyone who can point me in the right direction.

Many Thanks,

Alan

gmaxey · #2 09-29-2014, 12:51 PM

Code:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.Range
Dim oRngProcess As Word.Range
  Set oRng = Selection.Range
  Set oRngProcess = oRng.Duplicate
  With oRng.Find
    .Text = InputBox("Enter text to find.")
    Do
      .Execute
      If oRng.InRange(oRngProcess) Then
        With oRng
          .Font.ColorIndex = wdRed
          .Font.StrikeThrough = True
          .Collapse wdCollapseEnd
        End With
      Else
        Exit Do
      End If
    Loop
  End With
End Sub

sistemalan · #3 09-30-2014, 03:54 AM

Hi Greg,

Thank you so much that seems like it should be perfect, however for some reason Word is freezing when I run this. My selection is a whole table. I run the macro, input the name and hit OK. I can see the formatting has been applied as expected but then the Spinning wheel of death just keeps going and Windows tells me that Word has stopped responding. Curious. Any ideas what might be wrong here? I'm running Word 2013 on Window 7.

Cheers!

Alan

gmaxey · #4 09-30-2014, 06:28 AM

Well that is interesting. I can't claim to understand why, but use:

Code:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.Range
Dim oRngProcess As Word.Range
  Set oRng = Selection.Range
  Set oRngProcess = oRng.Duplicate
  With oRng.Find
    .Text = InputBox("Enter text to find.")
    Do
      .Execute
      If oRng.InRange(oRngProcess) Then
        With oRng
          If Len(oRng) = 0 Then Exit Do
          .Font.ColorIndex = wdRed
          .Font.StrikeThrough = True
          .Collapse wdCollapseEnd
        End With
      Else
        Exit Do
      End If
    Loop
  End With
End Sub

sistemalan · #5 09-30-2014, 08:28 AM

Thanks Greg. That appears to work perfectly. Placebo effect perhaps? :-)

I'm really grateful, this will save me a lot of time and I'll be able to modify the code to make a few more of my repetitive tasks a lot easier.

Cheers!

Alan

sistemalan · #6 10-03-2014, 03:46 AM

Hi again,

One more question. This works fine with a unique name, however If I type "Ali", then it affects the first three letters of Alison (we have lots of staff and volunteers so sometimes names are similar). Any simple way to avoid that?

Thanks!

Alan

gmayor · #7 10-03-2014, 03:57 AM

Add the match whole word option (or the wildcard option)
e.g

Code:

With oRng.Find
        .MatchWholeWord = True
        .Text = InputBox("Enter text to find.")
        Do

sistemalan · #8 10-03-2014, 07:28 AM

Thanks Graham,

That does the trick. Only weird thing I've noticed now is that if the name is not found within the selection the macro applies the formatting to all text within the selection. Any way to avoid that?

Thanks!

Alan

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find text, format part of text in italic	d4okeefe	Word VBA	18	06-30-2022 11:35 PM
Macro to find text in between two characters and then format selected text?	qcom	Word	5	02-19-2015 11:23 PM
Way to search for a string in text file, pull out everything until another string?	omahadivision	Excel Programming	12	11-23-2013 12:10 PM
Find and replace a string of text	errtu	Word	1	01-31-2013 02:09 PM
Bad view when using Find and Find & Replace - Word places found string on top line	paulkaye	Word	4	12-06-2011 11:05 PM