Close the find box with a command key?

[Charles Bouldin]

Microsoft, bless their hearts, has decided that in Excel the "Find"
dialog is modal. Worse, command-W doesn't close the find dialog. Even
worse, NO command key equivalent closes the find dialog. In desperation,
I tried to make an Applescript that would do it.

The script below sure should; in fact you can see the button highlight 3
times, but the dialog doesn't close.

activate application "Microsoft Excel"
tell application "System Events"
tell process "Microsoft Excel"
delay 2
click button "Close" of window "Find"
click button "Close" of window "Find"
click button "Close" of window "Find"
end tell
end tell

Any suggestions on this? I downloaded the MS document
Excel2004AppleScriptRef.pdf and found that the dialog I'm looking at can
be displayed by
tell application "Microsoft Excel"
show (get dialog dialog formula find)
end tell

But I can't figure out how to CLOSE the darn thing.

Pointers much appreciated. If a script works, then I can put in Quickeys
and command-W will activate the script and close the dialog. So much for
user interface standards.

I have the nagging feeling that either (1) I'm making this much harder
than it needs to be, or (2) this is truly bad UI design on something so
basic.

 

[Paul Berkowitz]

Don't try GUI scripting with Excel, Word, or PowerPoint. Nothing works
except simple things like 'keystroke' - no buttons are accessible, with the
exception of the three red/yellow/green OS X buttons in "main" windows.
Nothing in an auxiliary window. No controls. GUI scripting is only going to
be guaranteed for Cocoa apps and is limited in Carbon apps. Office apps are
the worst. You can control menu items in Entourage, but that's it.

It would help if you explain what you need this for. Just to close the Find
window? That's it? You could do the whole Find by AppleScript without
opening any window, but that's not what you want, right? you just want to
run a script instead of clicking "Close"? How are you going to run the
script? Pulling down and clicking in the system Script menu involves far
more mouse manipulation than clicking a Close button. And you can't add
keyboard shortcuts to scripts running in the system script menu.

VBA has xlDialog constants for buttons on the built-in dialogs, which have
not been translated to AppleScript. But the Find and Replace dialogs don't
seem to be represented in the Dialogs collections at all!.

However there's a simple way to close many dialogs in many apps, effectively
clicking a "Cancel" button (here "Close"): just press cmd-esc. It works in
Excel.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[J Laroche]

Paul Berkowitz wrote on 2005/04/01 22:32:

VBA has xlDialog constants for buttons on the built-in dialogs, which have
not been translated to AppleScript. But the Find and Replace dialogs don't
seem to be represented in the Dialogs collections at all!.

Yes they are:
Application.Dialogs(xlDialogFormulaFind).Show
Application.Dialogs(xlDialogFormulaReplace).Show

But maybe Charles is taking a too difficult route to achieve what he wants.
Perhaps a simple VBA with
Cells.Find What:=TextToFind, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, Activate:=True
Cells.FindNext(After:=ActiveCell).Activate
would do the trick. If he wants to let the user select the text to find,
without opening the Find dialog, he could do it with InputBox.

JL
Mac OS X 10.3.8, Office v.X 10.1.6

 

[CyberTaz]

Hi Charles-

If I am interpreting your inquiry right, you simply want to be able to close
the Find dialog box from the keyboard, not as a command in a
script/procedure designed for some other purpose.


If so, just press the esc key, which also works for most other dialog boxes
& menus,

HTH |:>)

On 4/1/05 6:03 PM, in article

Microsoft, bless their hearts, has decided that in Excel the "Find"
dialog is modal. Worse, command-W doesn't close the find dialog. Even
worse, NO command key equivalent closes the find dialog. In desperation,
I tried to make an Applescript that would do it.

The script below sure should; in fact you can see the button highlight 3
times, but the dialog doesn't close.

activate application "Microsoft Excel"
tell application "System Events"
tell process "Microsoft Excel"
delay 2
click button "Close" of window "Find"
click button "Close" of window "Find"
click button "Close" of window "Find"
end tell
end tell

Any suggestions on this? I downloaded the MS document
Excel2004AppleScriptRef.pdf and found that the dialog I'm looking at can
be displayed by
tell application "Microsoft Excel"
show (get dialog dialog formula find)
end tell

But I can't figure out how to CLOSE the darn thing.

Pointers much appreciated. If a script works, then I can put in Quickeys
and command-W will activate the script and close the dialog. So much for
user interface standards.

I have the nagging feeling that either (1) I'm making this much harder
than it needs to be, or (2) this is truly bad UI design on something so
basic.

-- (e-mail address removed)

 

[Charles Bouldin]

It would help if you explain what you need this for. Just to close

the Find
window? That's it? You could do the whole Find by AppleScript without
opening any window, but that's not what you want, right? you just want to
run a script instead of clicking "Close"? How are you going to run the
script? Pulling down and clicking in the system Script menu involves far
more mouse manipulation than clicking a Close button. And you can't add
keyboard shortcuts to scripts running in the system script menu.

VBA has xlDialog constants for buttons on the built-in dialogs, which have
not been translated to AppleScript. But the Find and Replace dialogs don't
seem to be represented in the Dialogs collections at all!.

However there's a simple way to close many dialogs in many apps, effectively
clicking a "Cancel" button (here "Close"): just press cmd-esc. It works in
Excel.[/QUOTE]

I just wanted to close the darn window without using the mouse.

I run Excel on a dual monitor setup and it is a long way to the close
box on the Find dialog.

Thanks for the cmd-esc tip, it works fine, and I can use Quickeys to
make cmd-W send cmd-esc to Excel. BTW, my original thought with the
script was to get an AS that would close the dialog and then put that
into a QK script activated by cmd-W, so it wasn't really as crazy as it
might have seemed.

 

[Paul Berkowitz]

I just wanted to close the darn window without using the mouse.

I run Excel on a dual monitor setup and it is a long way to the close
box on the Find dialog.

Thanks for the cmd-esc tip, it works fine, and I can use Quickeys to
make cmd-W send cmd-esc to Excel. BTW, my original thought with the
script was to get an AS that would close the dialog and then put that
into a QK script activated by cmd-W, so it wasn't really as crazy as it
might have seemed.

As CyberTaz pointed out the cmd- is superfluous here. esc all on its own
closes the Find dialog - real simple.


--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Paul Berkowitz]

Paul Berkowitz wrote on 2005/04/01 22:32:


Yes they are:
Application.Dialogs(xlDialogFormulaFind).Show
Application.Dialogs(xlDialogFormulaReplace).Show

Thanks, JL. I noticed those, but was zooming past too quickly to actually
try them out ;-) - I thought that they'd be referring to something to do
with formulas or the formula bar. I'm sort of used to the fact that the
first part of command names refer to the menu in the Windows version, not
Mac Find and Replace are in the Edit menu here), but I didn't think that
even XLWin had a Formula menu so it must refer to the Formula bar. Does
anyone have a good idea as to why these command names start with "Formula"?

But maybe Charles is taking a too difficult route to achieve what he wants.
Perhaps a simple VBA with
Cells.Find What:=TextToFind, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, Activate:=True
Cells.FindNext(After:=ActiveCell).Activate
would do the trick. If he wants to let the user select the text to find,
without opening the Find dialog, he could do it with InputBox.

He just wants to close the Find dialog, having brought it up manually.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.