Excel VBA find method resets search box "Within" from workbook to sheet-CodePudding

I use Ctrl-F frequently in Excel to find things in workbooks across multiple sheets so the "Within" field is usually set to "Workbook". I also run macros and find that a side effect of running them is that when doing Ctrl-F from within Excel that the "Within" field is reset back to "Sheet" when it was "Workbook" prior to that. I tracked down that the find method is where this reset happens and was wondering how I can make sure the "Within" setting is back to whatever it was before the macro was run. My macro has the following:

lastCol = Sheets(sheetFocus).Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
lastRow = Sheets(sheetFocus).Cells.Find("*", [a1], , , xlByRows, xlPrevious).row

I don't see which parameter is resetting things in Excel for Ctrl-F. Is there a way to either stop the side effect or test what the "Within" setting was before doing the find method so that I can set it back afterwards?

CodePudding user response：

I know it's not usually acceptable to use SendKeys method to get something done in VBA, but I think this is the only solution for your scenario.

Create this sub and call it after you get the last row and column.

Public Sub ResetFind()
    
    ' Open Find/Replace dialog
    Application.CommandBars("Edit").Controls("Find...").Execute
    DoEvents
    
    'reset "Within:" entry using <Alt>-H
    Application.SendKeys ("%H{DOWN}") ' use Up Arrow to choose Sheet, Down arrow to choose Workbook
    DoEvents
    
    ' Set Focus to Find box (using <Shift><Tab>
    Application.SendKeys (" {TAB} {TAB}")
    DoEvents
    
    ' Clear out previous entry
    Application.SendKeys ("{BACKSPACE}")
    DoEvents
    
    ' Set Focus to Close button (using <Shift><Tab>
    Application.SendKeys (" {TAB} {TAB}")
    DoEvents
        
    ' Close Find box
    Application.SendKeys ("{ENTER}")
    DoEvents
    
End Sub

Then call it after your code:

lastCol = Sheets(sheetFocus).Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
lastRow = Sheets(sheetFocus).Cells.Find("*", [a1], , , xlByRows, xlPrevious).row
ResetFind()

NOTE:

I know it doesn't find or use the actual last value - I'm not sure how that could be done - maybe using a FindWindow API call and get the previous value that way. If you can find the name of the control (window) with SPY perhaps it could be done?

Or like you say - maybe you can be satisfied with it being a minor annoyance?

CodePudding user response：

Based on @dbmitch I used the below code. It presumes that "Options" in the Find and Replace box has been expanded.

Public Sub ResetFind()
    ' Open Find/Replace dialog
    Application.CommandBars("Edit").Controls("Find...").Execute
    DoEvents

    'reset "Within:" entry using <Alt>-H
    Application.SendKeys ("%H{DOWN}") ' use Up Arrow to choose Sheet, Down arrow to choose Workbook
    DoEvents
    
    ' Set Focus to Find box (using <Alt>-N
    Application.SendKeys ("%N{DOWN}") ' use Down Arrow to choose last search value
    DoEvents
    
    ' Close Find box
    Application.SendKeys ("{ESC}{ESC}")
    DoEvents
End Sub