Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2015
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Loop macro message box

    I have a VBA macro in Word that works wonderfully. It loops through a list stored in Excel, searching for the word or phrase in Column A, replacing it with the word/phrase in Column B and highlighting any changes.
    I'm wondering if it's possible to add a message box that will tell me if no changes were made. I don't need to know how many changes (if more than 0), which I can see might be problematic, just if there were no changes.

    Code:
    Sub AAAChangeList1()
    'YELLOW, NO CASE
    'match case is FALSE; match whole words is TRUE
      'File name with terms to check
      Const strXLFile = "\\'full filepath\Replacements.xls"
      Dim xlApp As Object
      Dim xlWbk As Object
      Dim xlWsh As Object
      Dim blnStart As Boolean
      Dim r As Long
      Dim m As Long
          ' set highligher colour to yellow
        Options.DefaultHighlightColorIndex = wdYellow
    
    
    
    
      On Error Resume Next
      ' Get or start Excel
      Set xlApp = GetObject(, "Excel.Application")
      If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
        If xlApp Is Nothing Then
          MsgBox "Can't start Excel.", vbExclamation
          Exit Sub
        End If
        blnStart = True
      End If
    
    
      On Error GoTo ErrHandler
    
    
      Application.ScreenUpdating = False
      ' Open workbook
      Set xlWbk = xlApp.Workbooks.Open(strXLFile)
      ' Reference to worksheet
      Set xlWsh = xlWbk.Worksheets(1)
      ' Get last used row
      m = xlWsh.Cells(xlWsh.Rows.Count, 1).End(-4162).Row
    
    
      With ActiveDocument.Content.Find
        ' Initialize find/replace settings
        .ClearFormatting
        .Replacement.ClearFormatting
        .Replacement.Highlight = True
        .MatchCase = False
        .MatchWholeWord = True
        .MatchWildcards = False
        ' Loop through rows
        For r = 2 To m
          ' Get text to find
          .Text = xlWsh.Cells(r, 1)
          ' And replacement
          .Replacement.Text = xlWsh.Cells(r, 2)
          ' Replace all
          .Execute Replace:=wdReplaceAll
        Next r
      End With
    
    
    
    
    ExitHandler:
      ' Clean up
      On Error Resume Next
      Set xlWsh = Nothing
      xlWbk.Close SaveChanges:=False
      Set xlWbk = Nothing
      If blnStart Then
        xlApp.Quit
      End If
      Set xlApp = Nothing
      Application.ScreenUpdating = True
      Exit Sub
    
    
    ErrHandler:
      ' Inform user
      MsgBox Err.Description, vbExclamation
      ' And go to cleanup section
      Resume ExitHandler
    
    
    End Sub

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    After:
    .Execute Replace:=wdReplaceAll
    Insert:
    If .Found = False Then MsgBox "No matches found for " & xlWsh.Cells(r, 1), vbOKOnly
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Sep 2015
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Close, but not quite :-)
    My current list is nearly 300 rows long. I don't want to click ok for all 300. I just want a message at the end of the macro's run that says, 'No changes in total/for any row.'
    At the moment, I run the macro, then scroll through the document to look for any highlighting. Our documents go through a two person check. If I'm second person, 9 times out of 10, there are no changes. Rather than scrolling through to find nothing (or perhaps accidentally flick past something), I'd like a message to tell there were no changes.

  4. #4
    New Lounger
    Join Date
    Sep 2015
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I changed my Googling tactics and I've found code that counts highlighted text in a document. I've added that to my macro, as it serves the same purpose.
    Thanks for your time.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    There are numerous ways of skinning that cat, without having to highlight anything. For example, you could add whichever parts of:
    Code:
        If .Found = True Then
          i = i + 1
          strFndT = strFndT & vbCr & xlWsh.Cells(r, 1)
        Else
          j = j + 1
          strFndF = strFndF & vbCr & xlWsh.Cells(r, 1)
        End If
    you'd like to use, after:
    .Execute Replace:=wdReplaceAll
    and whichever correspionding parts of:
    Code:
      MsgBox "Matches found for:" & strFndT, vbOKOnly
      MsgBox "No matches found for:" & strFndF, vbOKOnly
      MsgBox "Matches found for " & i & " items", vbOKOnly
      MsgBox "No matches found for " & j & " items", vbOKOnly
    you'd like to use, after:
    Next r,
    along with the relevant Dim statements:
    Dim strFndT As String, strFndF As String, i As Long, j As Long
    at the top of the sub.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •