Excel 2003

I've just discovered Application.OnKey. I've put several assignments
in a Workbook_Open Event in ThisWorkbook in PERSONAL.XLS. This serves
a useful purpose, but only to a limited extent.

As far as I can tell, OnKey works to do the three following things:
- Disables an existing keyboard shortcut (by using "")
- Restores the default shortcut
- Adds a keyboard shortcut for a **MACRO**.

What I can't get OnKey to do is to assign a keyboard shortcut to a
built-in Excel dialog or other feature. For example, I'd like to
assign F3 to Find Next, currently assigned to Shift-F4. (I inevitably
hit Ctrl-F4 and close the workbook accidentally.) Does OnKey work for
this? If so, how? Or is there another method?

Thanks.

Most Dialogs can be evoked from macros. So have the OnKey call a macro (or
assign a macro shortcut in the usual way) and then have the macro open the
Dialog:

Sub servient()
fname = Application.GetOpenFilename()
Workbooks.Open (fname)
End Sub

--
Gary''s Student - gsnu200810


"wal" wrote:

> Excel 2003
>
> I've just discovered Application.OnKey. I've put several assignments
> in a Workbook_Open Event in ThisWorkbook in PERSONAL.XLS. This serves
> a useful purpose, but only to a limited extent.
>
> As far as I can tell, OnKey works to do the three following things:
> - Disables an existing keyboard shortcut (by using "")
> - Restores the default shortcut
> - Adds a keyboard shortcut for a **MACRO**.
>
> What I can't get OnKey to do is to assign a keyboard shortcut to a
> built-in Excel dialog or other feature. For example, I'd like to
> assign F3 to Find Next, currently assigned to Shift-F4. (I inevitably
> hit Ctrl-F4 and close the workbook accidentally.) Does OnKey work for
> this? If so, how? Or is there another method?
>
> Thanks.
>
>
>
>

Application.OnKey is all but obsolete. Just about all of the things that can
be done with it also have built in constants available in VBA to do the same
thing. It is sometimes useful in working with forms to simulate a keystoke
action, since the user cannot access the worksheet if the form is modal. I
suppose it is really a matter of design. If you design a program to work
without it, you don't miss it. If you design the program to run with it, it
can be useful. But it is not an essential facility to using VBA.

"wal" wrote:

> Excel 2003
>
> I've just discovered Application.OnKey. I've put several assignments
> in a Workbook_Open Event in ThisWorkbook in PERSONAL.XLS. This serves
> a useful purpose, but only to a limited extent.
>
> As far as I can tell, OnKey works to do the three following things:
> - Disables an existing keyboard shortcut (by using "")
> - Restores the default shortcut
> - Adds a keyboard shortcut for a **MACRO**.
>
> What I can't get OnKey to do is to assign a keyboard shortcut to a
> built-in Excel dialog or other feature. For example, I'd like to
> assign F3 to Find Next, currently assigned to Shift-F4. (I inevitably
> hit Ctrl-F4 and close the workbook accidentally.) Does OnKey work for
> this? If so, how? Or is there another method?
>
> Thanks.
>
>
>
>

Sub AssignF3ToFindNext()
Application.OnKey "{F3}", "MyFindNext"
End Sub

Sub MyFindNext()
Cells.FindNext(Selection).Select
End Sub


--
Jim
"wal" <(E-Mail Removed)> wrote in message
news:2c904dfc-6cfe-49ce-8529-(E-Mail Removed)...
> Excel 2003
>
> I've just discovered Application.OnKey. I've put several assignments
> in a Workbook_Open Event in ThisWorkbook in PERSONAL.XLS. This serves
> a useful purpose, but only to a limited extent.
>
> As far as I can tell, OnKey works to do the three following things:
> - Disables an existing keyboard shortcut (by using "")
> - Restores the default shortcut
> - Adds a keyboard shortcut for a **MACRO**.
>
> What I can't get OnKey to do is to assign a keyboard shortcut to a
> built-in Excel dialog or other feature. For example, I'd like to
> assign F3 to Find Next, currently assigned to Shift-F4. (I inevitably
> hit Ctrl-F4 and close the workbook accidentally.) Does OnKey work for
> this? If so, how? Or is there another method?
>
> Thanks.
>
>
>

Forget that, I'm thinking SendKeys.

"JLGWhiz" wrote:

> Application.OnKey is all but obsolete. Just about all of the things that can
> be done with it also have built in constants available in VBA to do the same
> thing. It is sometimes useful in working with forms to simulate a keystoke
> action, since the user cannot access the worksheet if the form is modal. I
> suppose it is really a matter of design. If you design a program to work
> without it, you don't miss it. If you design the program to run with it, it
> can be useful. But it is not an essential facility to using VBA.
>
> "wal" wrote:
>
> > Excel 2003
> >
> > I've just discovered Application.OnKey. I've put several assignments
> > in a Workbook_Open Event in ThisWorkbook in PERSONAL.XLS. This serves
> > a useful purpose, but only to a limited extent.
> >
> > As far as I can tell, OnKey works to do the three following things:
> > - Disables an existing keyboard shortcut (by using "")
> > - Restores the default shortcut
> > - Adds a keyboard shortcut for a **MACRO**.
> >
> > What I can't get OnKey to do is to assign a keyboard shortcut to a
> > built-in Excel dialog or other feature. For example, I'd like to
> > assign F3 to Find Next, currently assigned to Shift-F4. (I inevitably
> > hit Ctrl-F4 and close the workbook accidentally.) Does OnKey work for
> > this? If so, how? Or is there another method?
> >
> > Thanks.
> >
> >
> >
> >