|
Determine Find/Replace settings and then restore them?
Below is the code I use in Microsoft Word to preserve my Search/Replace
settings when I run VBA that uses Search/Replace and would normally leave the settings changed. It doesn't look as easy in Excel. Is there a way to obtain the settings, or at least the search string? If so, I guess a clumsy way to restore them would be to open a temporary window and do an actual Find using the original string and other settings, then close the window. But I expect there's a more elegant way. Dan Williams danwPlanet ________________________________________________ Call RememberFindReplaceSettings("Memorize") ' Find/Replace code goes here. Call RememberFindReplaceSettings("Restore") Sub RememberFindReplaceSettings(myMode As String) Static myOldFindText As String Static myOldReplText As String Static myOldDirectio As Boolean Static myOldWrapping As Integer Static myOldFormatNo As Boolean Static myOldMatchCas As Boolean Static myOldWholWord As Boolean Static myOldWildcard As Boolean Static myOldSounLike As Boolean Static myOldAllWdFms As Boolean Static myOldFindStyl As String Static myOldReplStyl As String With Selection.Find If myMode = "Memorize" Then myOldFindStyl = .Style myOldReplStyl = .Replacement.Style myOldFindText = .Text myOldReplText = .Replacement.Text myOldDirectio = .forward myOldWrapping = .Wrap myOldFormatNo = .Format myOldMatchCas = .MatchCase myOldWholWord = .MatchWholeWord myOldWildcard = .MatchWildcards myOldSounLike = .MatchSoundsLike myOldAllWdFms = .MatchAllWordForms ElseIf myMode = "Restore" Then If myOldFindStyl < "" Then .Style = myOldFindStyl If myOldReplStyl < "" Then .Replacement.Style = myOldReplStyl .Text = myOldFindText .Replacement.Text = myOldReplText .forward = myOldDirectio .Wrap = myOldWrapping .Format = myOldFormatNo .MatchCase = myOldMatchCas .MatchWholeWord = myOldWholWord .MatchWildcards = myOldWildcard .MatchSoundsLike = myOldSounLike .MatchAllWordForms = myOldAllWdFms End If End With End Sub |
Determine Find/Replace settings and then restore them?
They are arguments in Excel, not properties. I don't believe you can query
the find procedure for their current value with any built in functions. -- Regards, Tom Ogilvy "Dan Williams" wrote in message oups.com... Below is the code I use in Microsoft Word to preserve my Search/Replace settings when I run VBA that uses Search/Replace and would normally leave the settings changed. It doesn't look as easy in Excel. Is there a way to obtain the settings, or at least the search string? If so, I guess a clumsy way to restore them would be to open a temporary window and do an actual Find using the original string and other settings, then close the window. But I expect there's a more elegant way. Dan Williams danwPlanet ________________________________________________ Call RememberFindReplaceSettings("Memorize") ' Find/Replace code goes here. Call RememberFindReplaceSettings("Restore") Sub RememberFindReplaceSettings(myMode As String) Static myOldFindText As String Static myOldReplText As String Static myOldDirectio As Boolean Static myOldWrapping As Integer Static myOldFormatNo As Boolean Static myOldMatchCas As Boolean Static myOldWholWord As Boolean Static myOldWildcard As Boolean Static myOldSounLike As Boolean Static myOldAllWdFms As Boolean Static myOldFindStyl As String Static myOldReplStyl As String With Selection.Find If myMode = "Memorize" Then myOldFindStyl = .Style myOldReplStyl = .Replacement.Style myOldFindText = .Text myOldReplText = .Replacement.Text myOldDirectio = .forward myOldWrapping = .Wrap myOldFormatNo = .Format myOldMatchCas = .MatchCase myOldWholWord = .MatchWholeWord myOldWildcard = .MatchWildcards myOldSounLike = .MatchSoundsLike myOldAllWdFms = .MatchAllWordForms ElseIf myMode = "Restore" Then If myOldFindStyl < "" Then .Style = myOldFindStyl If myOldReplStyl < "" Then .Replacement.Style = myOldReplStyl .Text = myOldFindText .Replacement.Text = myOldReplText .forward = myOldDirectio .Wrap = myOldWrapping .Format = myOldFormatNo .MatchCase = myOldMatchCas .MatchWholeWord = myOldWholWord .MatchWildcards = myOldWildcard .MatchSoundsLike = myOldSounLike .MatchAllWordForms = myOldAllWdFms End If End With End Sub |
| All times are GMT +1. The time now is 11:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com