I had a similar issue. I have button in a workbook. If the button is clicked I want excel to open the find dialogue with the option "search within all workbooks" already selected instead of the default "within sheet", no matter what.
Furthermore I wanted the button to work regardles off the options being hidden or shown and wether or not somebody made changes manually. This is tricky because Excel per default doesn't show the options on start but remembers and shows them again the next time if somebody displayed them before.
To make sure the keystrokes work we need a series of keystrokes that sets the setting regardless of the initial state. Took quite a bit of tinkering, but I found one that works.
Here's my code for the English version of Excel, for other languages you have to change the keys accordingly.
Sub Commanbutton_Click()
Cells(1, 1).Select
SendKeys "^f", True
SendKeys "{TAB 15}"
SendKeys " ", True
SendKeys "%t%t", True
SendKeys "{TAB 2}", True
SendKeys "{DOWN}{DOWN}{ENTER}", True
SendKeys "%t%t", True
End Sub
You can try it out manually:
Press CTRL+F for the find dialogue
Press TAB 15 times so that you either land on "Options" or on the "Search within" dropdown.
Press space and either
1) Options are displayed or
2) the dropdown gets activated and nothing happens
Press Alt+T twice to reset the cursor to the find input box
Press TAB twice, it will take you to the search within dropdown, no matter what (because the previous steps ensure that the options are in fact displayed!)
Press DOWN arrow twice to select "Workbook" and ENTER to select
Press ALT+T twice to reset the cursor to the find input box again.
Hope this helps somebody else who has a similar problem.
Lots of Googling only told me that it is not possible to preselect "search within all workbooks" with VBA but this way works!
To make it work with different languages you'd need to check for the language and use CASE to switch to the proper routine with the proper shortcuts.