excelvba

Using VBA/Macro to close the Finder window

I'm trying to write a simple VBA macro to run when I open a workbook. The macro will open the Finder window, change a couple of options, then close the window. This is to basically "set" the default search options since (as far as I can tell) Excel doesn't allow you to change the default options. The problem I'm having is that I cannot find a way to get the macro to close the Finder window once it's done. I'm still very much an amateur at this, which is probably contributing, but all my searches online either focus only on message boxes or aren't related to what I'm trying to do. The code I have (currently it only opens the window, I have to manually close it) is below.

Private Sub Workbook_Open()

    Application.Dialogs(xlDialogFormulaFind).Show , 2, 2

End Sub

The main thing I've actually tried is using sendkey to try and send an "Esc" command, but that hasn't worked; I saw no change at all in the window. I haven't found any subcommands for application or application.dialog that seems to work to close the window either.

Thanks in advance.

Solution

  • Settings for Find are sticky, so if you perform a search using VBA then the settings you use become the default.

    https://learn.microsoft.com/en-us/office/vba/api/excel.range.find#:~:text=The%20settings%20for%20LookIn%2C%20LookAt%2C,explicitly%20each%20time%20you%20use%20this%20method.

    The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

    So just perform a Find() in VBA using the default settings you want. No need to show the dialog.