Prevent Range.Find() from changing the "match entire cell content" user interface setting

Question

After running the first line of the following code, Excel changes the Match entire cell contents setting in the user interface. The result is that the next time the user presses Ctrl+F the search is done in the whole cell content. I don't like a VBA macro that changes a setting that affects the user experience, so I always execute the second line, which performs another search and sets the Match entire cell contents back to its default.

'execute the find and changes the entire cell content setting
Set C = MyRange.Find(SomeText, LookAt:=xlWhole)

'put the setting back to the default value (not the previous value)
MyRange.SomeText ParName, LookAt:=xlPart

The problem is that it sets it back to the default, not to the value the user set it last.

I would rather have something like:

'store the current entire cell content setting
OldLookAt = Application.FindLookAt

'execute the find
Set C = MyRange.Find(SomeText, LookAt:=xlWhole)

'restore the original setting
Application.FindLookAt = OldLookAt

I made up the Application.FindLookAt because I wasn't able to find it.

Is there a way to Range.Find something without affecting the user experience?

Answer 1

I spent a fair bit of time looking through the Excel object library and MSDN documentation and it doesn't seem this is possible to retrieve.

Unfortunately it also looks like Excel actually overrides and sets your user setting if you don't include the parameter to the default.

Private Sub testXLLookAT()

    'Setup fake falues
    Dim mCurLookup As XlLookAt
    Range("A1").value = "Test1"
    Range("A2").value = "Test"

    'get current setting, but this doesn't work as it defaults...
    If Range("A1:A2").Find("Test").Address = "A1" Then
        mCurLookup = xlPart
    Else
        mCurLookup = xlWhole
    End If

    'your find here

    'note - mCurLookup always becomes xlWhole...
    Range("A1:A2").Find What:="Test", LookAt:=mCurLookup



End Sub

Otherwise, the above code would work - but it doesn't as the default gets overwritten even when it's not included.

Answer 2

Here's a function that can be used to return the current LookAt default value so it may be reset.

Function lGetCurLookAt() As Long
'--returns current default value for Range.Find method's
'    LookAt parameter.

 '--use helper cell after all data in col A
 With Cells(Rows.Count, "A").End(xlUp)(3)
   .Value = "TestPart"
   lGetCurLookAt = IIf( _
    .Find(What:="Part", SearchFormat:=False) Is Nothing, _
    xlWhole, xlPart)
   .Value = vbNullString
 End With

End Function

My first post, so I'm not able to add comment to enderland's answer. In spirit of being helpful, that code didn't work as expected because it used A1 instead of $A$1.