Hidden Macro Shortcut Using Shift, Ctrl, Alt Keys?

[CaliKidd]

I am using Excel 2007. I am designing a workbook that others will use. I want to create a "reset" feature, which is a hidden macro that launches a userform that will allow the user to reset the workboook (i.e., clear saved settings in various cells).

I want to create a Reset subroutine that meets the following criteria:

1. Macro can only be launched if a certain keystroke combination is used; e.g., {Shift}{Ctrl}{Alt}{~}

Note: It doesn't have to be that exact keyboard combo. I just want it to be a multi-key combo of keys not commonly used so it isn't triggered by accident.

2. It is not visible from the macro box that shows a list of macros available to run. I don't want the users running this macro by mistake or tempting nosey users to run it to "see what it does."

How is this best accomplished in VBA?

 

[CaliKidd]

I found a link (http://msdn.microsoft.com/en-us/library/aa195807(v=office.11).aspx) that looks promising. It is for Excel 2003, but I suspect it would work for Excel 2007 also.

If this is the solution to my problem, the only question I have is -- where would I put this Application.OnKey command so it is "available" to be run at any time?

 

[CaliKidd]

For the record, I found the answer after researching the matter further.

This helpful link (http://www.rondebruin.nl/key.htm) explains the Application.OnKey command should be placed in the Workbook_Activate and Workbook_Deactivate events.

Issue resolved.

 

[CaliKidd]

Oops, I jumped the gun with my last statement. To be clear, I should state that issue #1 is resolved. The second issue is still open.

When I use the Application.OnKey "+^{RIGHT}", "YourMacroName" command in the Workbook_Activate event, the problem is the macro has to have public scope in order to run. The macro I am using is very simple:

Sub YourMacroName

  MyForm.Show 'this displays a userform
End Sub

** Sorry, for some unknown reason, the forum software is splitting my code.


Since it has public scope, it shows up in the list of macros. I don't want this macro to appear in this list of macros that can be seen (and, therefore, run without using the multi-key combination). The only way I know how to remove a macro from the list is to make it a private sub, but in doing so it is not within scope of the Application.OnKey command.

Is there a way to make a global macro not appear in the list of macros (the dialog box that appears when you click Developer-Macro)? In other words, hide a public macro from view, but not execution?

 

[CaliKidd]

Okay, I realize I am talking to myself on this board, lol, but I am posting follow-ups for the benefit of anyone else that may have the same questions in the future.

I think I found the answer to the second issue -- putting 'Option Private Module' at the beginning of the standard module makes it not appear in the list of macros, but the macros are still available for execution.

I haven't fully tested it yet, but for now consider the issues solved.