INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to identify a phantom Excel VBA routine that uses keystroke shortcut

How to identify a phantom Excel VBA routine that uses keystroke shortcut

(OP)
I have an Excel workbook with 303 VBA routines in it. There is a "phantom" routine that I just found uses the keystroke shortcut: Ctrl + r. This file has been around for 20+ years so who knows when/where/why I set that up. I sure don't.

The phantom routine is capable of running as soon as the workbook is opened. I didn't really notice this particular routine until I started using the same shortcut keys for some new routines.

I want to find this "phantom" VBA routine but I don't want to look at all 303 entries in the "Run a Macro" dialog (I [u]have[/u] started).

Is there a way to audit the shortcut keys for each VBA routine? Is there a way to use VBA to reset all the keystroke shortcuts in this file to ""?

I actually found a few routines on the web on how to compile a list of VBA routines. The only one I got working was this: https://msdn.microsoft.com/en-us/library/dd890502(v=office.11).aspx

This works for me after a couple simplifications (although I haven't yet gone through the gory details yet). But I don't see how to audit or change the shortcut keys.

In fact, it seems that the shortcut keys are different technology, per Help they are Excel4 macro shortcuts ("Returns or sets the shortcut key for a name defined as a custom Microsoft Excel 4.0 macro command. Read/write String."). So I don't know if it's even possible to audit or change these via VBA. Except by pushing keystrokes.

Any tricks or tips?


RE: How to identify a phantom Excel VBA routine that uses keystroke shortcut

(OP)
For example, I use these routines to set the shortcut when the particular sheet is activated, then remove the shortcut when the sheet is no longer active:

Private Sub Worksheet_Activate()
Application.MacroOptions Macro:="Sheet62.bentonite", Description:="", ShortcutKey:="r"
End Sub
Private Sub Worksheet_Deactivate()
Application.MacroOptions Macro:="Sheet62.bentonite", Description:="", ShortcutKey:=""
End Sub

RE: How to identify a phantom Excel VBA routine that uses keystroke shortcut

(OP)
Thanks! That would certainly do the trick. The link to the file seems to be "dead". But the idea gives me some direction for Google searching and brainstorming.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close