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

user_6552440 0
Viewed 45

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?

vba excel
2 Answers

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?

user_24191280

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
user_65524400
Related