Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-02-2020, 04:08 AM
grahamsims grahamsims is offline Macro to delete cross-reference error message when bookmark deleted Mac OS X Macro to delete cross-reference error message when bookmark deleted Office 2019
Novice
Macro to delete cross-reference error message when bookmark deleted
 
Join Date: Dec 2020
Posts: 5
grahamsims is on a distinguished road
Default Macro to delete cross-reference error message when bookmark deleted

I have a document which uses many cross references linked to bookmarks. When the document is edited, some of these bookmarks are intentionally deleted. When all of the fields in the document are updated, the deleted bookmarks generate "Error! Reference source not found" where the cross reference field occurs.
Using find and replace will remove the error message text but not the underlying {REF field}. If the fields are updated again or the document is printed, the deleted error message text reappears.


I do not want to unlink fields and do not want to remove all cross references. Going through the document to manually delete would take for ever. Can anyone help with a macro which locates all of these error messages and deletes the underlying cross reference fields? Would be hugely grateful if a solution could be found.
Reply With Quote
  #2  
Old 12-02-2020, 05:02 AM
gmayor's Avatar
gmayor gmayor is offline Macro to delete cross-reference error message when bookmark deleted Windows 10 Macro to delete cross-reference error message when bookmark deleted Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

I have not worked with the Mac version, but the following should work


Code:
Sub Macro1()
'Graham Mayor - https://www.gmayor.com - Last updated - 02 Dec 2020
Dim i As Integer
Dim oStory As Range
    For Each oStory In ActiveDocument.StoryRanges
        For i = oStory.Fields.Count To 1 Step -1
            If oStory.Fields(i).Type = wdFieldRef And _
               oStory.Fields(i).Result = "Error! Reference source not found." Then
                oStory.Fields(i).Delete
            End If
        Next i
        If oStory.StoryType <> wdMainTextStory Then
            While Not (oStory.NextStoryRange Is Nothing)
                Set oStory = oStory.NextStoryRange
                For i = oStory.Fields.Count To 1 Step -1
                    If oStory.Fields(i).Type = wdFieldRef And _
                       oStory.Fields(i).Result = "Error! Reference source not found." Then
                        oStory.Fields(i).Delete
                    End If
                Next i
            Wend
        End If
    Next oStory
lbl_Exit:
    Set oStory = Nothing
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 12-02-2020, 06:19 AM
grahamsims grahamsims is offline Macro to delete cross-reference error message when bookmark deleted Mac OS X Macro to delete cross-reference error message when bookmark deleted Office 2019
Novice
Macro to delete cross-reference error message when bookmark deleted
 
Join Date: Dec 2020
Posts: 5
grahamsims is on a distinguished road
Smile Thank you

That is brilliant. Thank you so much. Works like a dream. Couldn't have worked that out for myself!! Really appreciate your time in posting such a helpful reply.
Reply With Quote
  #4  
Old 12-02-2020, 08:02 AM
Charles Kenyon Charles Kenyon is offline Macro to delete cross-reference error message when bookmark deleted Windows 10 Macro to delete cross-reference error message when bookmark deleted Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,081
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

I have the habit, when I see excellent code, to save it for future use and perhaps tweak it. In this case, I realize that this works when the fields have been updated. This requires an extra step, which may be desirable to actually see the broken Ref fields before deleting them. Here is code that should delete any broken Ref fields even if you do not see the error message because you have not updated the fields.

I added a step to update Ref fields when located.


See following post for corrected code.


Code:
Sub RefBrokenRemove2()
'Graham Mayor - https://www.gmayor.com - Last updated - 02 Dec 2020
'https://www.msofficeforums.com/word/46089-macro-delete-cross-reference-error-message-when.html
' Remove broken Cross-Reference Fields
' Modified by Charles Kenyon to first update ref fields
'
    Dim i As Integer
    Dim oStory As range
    For Each oStory In ActiveDocument.StoryRanges
        For i = oStory.Fields.Count To 1 Step -1
            If oStory.Fields(i).Type = wdFieldRef Then
                oStory.Fields(i).Update
                If oStory.Fields(i).Result = "Error! Reference source not found." Then oStory.Fields(i).Delete
            End If
        Next i
        If oStory.StoryType <> wdMainTextStory Then
            While Not (oStory.NextStoryRange Is Nothing)
                Set oStory = oStory.NextStoryRange
                For i = oStory.Fields.Count To 1 Step -1
            If oStory.Fields(i).Type = wdFieldRef Then
                oStory.Fields(i).Update
                If oStory.Fields(i).Result = "Error! Reference source not found." Then oStory.Fields(i).Delete
            End If
            '        End If   ' This line commented out to correct code
                Next i
            Wend
        End If
    Next oStory
lbl_Exit:
    Set oStory = Nothing
    Exit Sub
End Sub

Last edited by Charles Kenyon; 12-03-2020 at 10:52 AM.
Reply With Quote
  #5  
Old 12-02-2020, 08:16 AM
grahamsims grahamsims is offline Macro to delete cross-reference error message when bookmark deleted Mac OS X Macro to delete cross-reference error message when bookmark deleted Office 2019
Novice
Macro to delete cross-reference error message when bookmark deleted
 
Join Date: Dec 2020
Posts: 5
grahamsims is on a distinguished road
Default

Thank you for the addition. I am constantly amazed by the helpfulness of experts who respond to queries like this. So helpful, and really appreciated.
Reply With Quote
  #6  
Old 12-03-2020, 02:26 AM
grahamsims grahamsims is offline Macro to delete cross-reference error message when bookmark deleted Mac OS X Macro to delete cross-reference error message when bookmark deleted Office 2019
Novice
Macro to delete cross-reference error message when bookmark deleted
 
Join Date: Dec 2020
Posts: 5
grahamsims is on a distinguished road
Default

Charles - looking at your solution again, which works beautifully, can you explain why within the first For…Next group there are two If statements but only one End If, whereas in the next For…If group there are a similar two If statements but two End Ifs?
Reply With Quote
  #7  
Old 12-03-2020, 08:30 AM
Charles Kenyon Charles Kenyon is offline Macro to delete cross-reference error message when bookmark deleted Windows 10 Macro to delete cross-reference error message when bookmark deleted Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,081
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Quote:
Originally Posted by grahamsims View Post
Charles - looking at your solution again, which works beautifully, can you explain why within the first For…Next group there are two If statements but only one End If, whereas in the next For…If group there are a similar two If statements but two End Ifs?

This is an example of programming cobbled together. I started with Graham's excellent code. The second End If is a mistake.



If the "Then" part of an IF statement is on the same line, no End If is needed.

That second End If should throw an error.


The corrected code is:
Code:
Sub RefBrokenRemove2()
'Graham Mayor - https://www.gmayor.com - Last updated - 02 Dec 2020
'https://www.msofficeforums.com/word/46089-macro-delete-cross-reference-error-message-when.html
' Remove broken Cross-Reference Fields
' Modified by Charles Kenyon to first update ref fields
'
    Dim i As Integer
    Dim oStory As range
    For Each oStory In ActiveDocument.StoryRanges
        For i = oStory.Fields.Count To 1 Step -1
            If oStory.Fields(i).Type = wdFieldRef Then
                oStory.Fields(i).Update
                If oStory.Fields(i).Result = "Error! Reference source not found." Then oStory.Fields(i).Delete
            End If
        Next i
        If oStory.StoryType <> wdMainTextStory Then
            While Not (oStory.NextStoryRange Is Nothing)
                Set oStory = oStory.NextStoryRange
                For i = oStory.Fields.Count To 1 Step -1
            If oStory.Fields(i).Type = wdFieldRef Then
                oStory.Fields(i).Update
                If oStory.Fields(i).Result = "Error! Reference source not found." Then oStory.Fields(i).Delete
            End If
                Next i
            Wend
        End If
    Next oStory
lbl_Exit:
    Set oStory = Nothing
    Exit Sub
End Sub
Reply With Quote
  #8  
Old 12-03-2020, 10:24 AM
grahamsims grahamsims is offline Macro to delete cross-reference error message when bookmark deleted Mac OS X Macro to delete cross-reference error message when bookmark deleted Office 2019
Novice
Macro to delete cross-reference error message when bookmark deleted
 
Join Date: Dec 2020
Posts: 5
grahamsims is on a distinguished road
Default

Thank you so much. I wish there were a really clear, helpful guide to writing macros!
Reply With Quote
  #9  
Old 12-03-2020, 10:50 AM
Charles Kenyon Charles Kenyon is offline Macro to delete cross-reference error message when bookmark deleted Windows 10 Macro to delete cross-reference error message when bookmark deleted Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,081
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Quote:
I wish there were a really clear, helpful guide to writing macros!
The best written resource I have found is old but still very useful.
Writing Word Macros: An Introduction to Programming Word using VBA: PhD Steven Roman

I got my start in programming as a result of "Word 97 Annoyances" by Woody Leonard where he gave short macros.

The Word MVP site has a lot of good material.
Word Macros and Visual Basic for Applications (VBA) FAQ

I have taken a couple of online video courses from Grant Gamble on Udemy. (Wait for a sale price.) They are good but he is not great at responding to user questions/comments.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to delete cross-reference error message when bookmark deleted Find {text} and insert cross reference from bookmark Slamzor Word VBA 1 12-01-2017 05:12 PM
Macro to delete cross-reference error message when bookmark deleted Hide error message from missing bookmark lodi123 Word 2 03-28-2017 11:24 PM
Macro to delete cross-reference error message when bookmark deleted Cross-reference does not maintain formatting from bookmark frannie Word 2 03-23-2017 04:33 PM
Macro to delete cross-reference error message when bookmark deleted Weird issue with bookmark and cross-reference Maddog32 Word 2 06-10-2016 02:39 PM
Macro to delete cross-reference error message when bookmark deleted Need help with using bookmark and cross-reference mpdsal Word 1 07-26-2012 01:05 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft