Override a built-in command with a macro

[Robert Schwenn]

Hi,
I'd like to see a macro running when invoking a built-in command. I.e. when
choosing "File -> New" my own macro should run.

Is this possible without changes to the GUI, maybe by giving the macro a special
name...?


Robert

 

[Jay Freedman]

Hi,
I'd like to see a macro running when invoking a built-in command. I.e. when
choosing "File -> New" my own macro should run.

Is this possible without changes to the GUI, maybe by giving the macro a special
name...?


Robert

Yes, you can do that with most commands. For Word, see
http://www.word.mvps.org/FAQs/MacrosVBA/InterceptSavePrint.htm, which gives a
procedure for finding the proper name. I think most Office programs that support
macros will have a similar method.

 

[Klaus Linke]

Unless I missed it, the article doesn't mention the easiest way to find the
name:
Hit Ctrl+Alt+(num+) (where num+ is the + key on the numeric keypad on the
right of the keyboard).
Then with the cloverleaf cursor, click on the menu item or button.

Regards,
Klaus

 

[Robert Schwenn]

Thank You, both.

Very good hints for Word. Unfortunately the procedures for finding the built-in
command names don't work in Excel (2003). I will go on searching for a command list.

Robert

 

[JP]

You'll need to do a few things:

1) Store the existing environment, possibly in the registry;
2) Remap the shortcut key(s) for File>New (just Ctrl-N, or are there
others?) to your macro using the OnKey Method
3) Create an event that checks when a new workbook is created, cancel
it, and run your code instead. Or I'm sure there is a way to use the
FindControl ID to create an event for that menu item.
4) Write code that restores the original settings when Excel shuts
down.


HTH,
JP

 

[Robert Schwenn]

Thanks, I see. Would You say that the other way (macro with same name as the
built-in command) doesn't work in Excel (2003)? Could it be that there are no
equivalent command names in Excel?

Robert

 

[JP]

I'm not sure what you mean. You can't name a macro with the same name
as an existing command and expect it to override that command, that
doesn't make sense.

The closest equivalent is to remap the shortcut key(s) for File>New to
your macro using the OnKey Method. But you would still need to capture
the click event (or whatever) for that menu item, in case the end user
doesn't use the shortcut key but instead goes to File>New to create a
new workbook.

--JP

 

[Robert Schwenn]

I'm not sure what you mean. You can't name a macro with the same name
as an existing command and expect it to override that command, that
doesn't make sense.

In Word this works perfectly.

The closest equivalent is to remap the shortcut key(s) for File>New to
your macro using the OnKey Method. But you would still need to capture
the click event (or whatever) for that menu item, in case the end user
doesn't use the shortcut key but instead goes to File>New to create a
new workbook.

Yes, I've done it this way and it works. Thanks.

Robert

 

[JP]

OK, but we're talking about Excel, right?

I'd be glad to see an example of what you are referring to in Word,
can you post the instructions so I could try it?

--JP