Macro shortcut for Database Add-In

[cjhickman]

I am trying to create a macro that turns my F12 key into a shortcut
for ALT-S-R in all my workbooks. I use that series of keystrokes often
through Essbase, a Database add-in used through Excel.

ALT-S-R are the keystrokes to retrieve data out of a database. If no
range is highlighted it puts the top left corner of the data in the
active
cell; however, if you hit ALT-S-R while there is a range of cells
highlighted, you will only populate those cells in the highlighted
range with the data.

I know at least some version of what I'm trying to get is possible. I
had it working for a little while through trial and error but only for
the active cell. It ignored the fact that I had a range of cells
selected. So as I was trying to tweak it, I closed my VBA without
saving what I had, and now I can't even get back to that shortcut
working at all.

I currently have disabled my F1 key with:

Private Sub Workbook_Open()
Application.OnKey "{F1}", ""
End Sub

in my PERSONAL.XLS Objects folder.

When I try this...

Private Sub Workbook_Open()
Application.OnKey "{F1}", ""
Application.OnKey "{F12}", "%sr"
End Sub

I get "The macro '...\Excel\XLSTART\PERSONAL.XLS'!%sr' cannot be
found."

Also, I am a bit confused as to the difference between doing it this
way, or by putting it in a Module as a Auto_Open Macro, and which way
is more effective/efficient.

I would appreciate any feedback on any of the issues. Thanks!

 

[Stephen at ZennHAUS]

I am trying to create a macro that turns my F12 key into a shortcut
for ALT-S-R in all my workbooks. I use that series of keystrokes often
through Essbase, a Database add-in used through Excel.

ALT-S-R are the keystrokes to retrieve data out of a database. If no
range is highlighted it puts the top left corner of the data in the
active
cell; however, if you hit ALT-S-R while there is a range of cells
highlighted, you will only populate those cells in the highlighted
range with the data.

I know at least some version of what I'm trying to get is possible. I
had it working for a little while through trial and error but only for
the active cell. It ignored the fact that I had a range of cells
selected. So as I was trying to tweak it, I closed my VBA without
saving what I had, and now I can't even get back to that shortcut
working at all.

I currently have disabled my F1 key with:

Private Sub Workbook_Open()
Application.OnKey "{F1}", ""
End Sub

in my PERSONAL.XLS Objects folder.

When I try this...

Private Sub Workbook_Open()
Application.OnKey "{F1}", ""
Application.OnKey "{F12}", "%sr"
End Sub

I get "The macro '...\Excel\XLSTART\PERSONAL.XLS'!%sr' cannot be
found."

Also, I am a bit confused as to the difference between doing it this
way, or by putting it in a Module as a Auto_Open Macro, and which way
is more effective/efficient.

I would appreciate any feedback on any of the issues. Thanks!

If the menu item accessed through the sequence ALT,S,R (not to be confused
with the keystroke ALT-S-R where all three keys would be pressed at the same
time), then you are activating an accessible command. So, you should be
able to record a macro of you pressing those keys in order which will create
codddde to execute that specific command.

THEN, you could use the Application.OnKey command to run the macro.

Assuming the macro is called Betty, the lines of code would look like:

Private Sub Workbook_Open()
Application.OnKey "{F12}", ""
Application.OnKey "{F12}", "Betty"
End Sub

The first OnKey command clears F12 so that will not execute any procedures
while the second sets F12 to execute your little macro called Betty. You
cannot set F12 to execute a series of keystrokes unless you code those
keystrokes into a macro or procedure first. It is easier to have a macro or
procedure that simply executes the command you want.

 

[cjhickman]

If the menu item accessed through the sequence ALT,S,R (not to be confused
with the keystroke ALT-S-R where all three keys would be pressed at the same
time), then you are activating an accessible command. So, you should be
able to record a macro of you pressing those keys in order which will create
codddde to execute that specific command.

THEN, you could use the Application.OnKey command to run the macro.

Assuming the macro is called Betty, the lines of code would look like:

Private Sub Workbook_Open()
Application.OnKey "{F12}", ""
Application.OnKey "{F12}", "Betty"
End Sub

The first OnKey command clears F12 so that will not execute any procedures
while the second sets F12 to execute your little macro called Betty. You
cannot set F12 to execute a series of keystrokes unless you code those
keystrokes into a macro or procedure first. It is easier to have a macro or
procedure that simply executes the command you want.

Thanks for your feedback. I still have one basic question. The macro
recorder is not recording my keystrokes Alt,S,R. I don't remember what
code I used to get the macro to perform those keystrokes. Do you have
any suggestions? Also, I would like it to perform that action for the
range of cells I have selected, would I throw a Selection. command
before the code?

Thanks again for your help.

Chris

 

[Stephen at ZennHAUS]

Thanks for your feedback. I still have one basic question. The macro
recorder is not recording my keystrokes Alt,S,R. I don't remember what
code I used to get the macro to perform those keystrokes. Do you have
any suggestions? Also, I would like it to perform that action for the
range of cells I have selected, would I throw a Selection. command
before the code?

Thanks again for your help.

Chris

Hi Chris

If the same range of cells is being selected everytime, yes you would put in
a selection line.

Instead of trying to work out how to "play back" the keystroke, work out
what the menu item does. It has to run a macro because it is part of an
addin. So, do the following:

Tools, Customize
Click on the relevant menu (the one you get to by pressing ALT,S) then
RIGHT-CLICK on the relevant menu item (the one that is selected by pressing
R).
Choose Assign Macro
Even though you will most likely NOT be able to see the list of macros
included in the Addin, you will see the name of the macro that menu item
executes.

Cancel Cancel Cancel

Once you have the name of the macro, in your code you would have the
following

Application.OnKey "{F12}","AddinMacroName"

where AddinMacroName is the name of the macro that the menu item executes.

Have fun