shortcut accelerators

[errtu]

Is there anyway to enable the accelerators for the Command buttons (activex control) WITHOUT ALT or CONTROL or any of those keys? Meaning, instead of pressing Button1 with ALT+A, that it should be activated with only pressing A?

That would be soooo useful for me

 

[shg]

See Help for the OnKey method.

 

[errtu]

See Help for the OnKey method.

awesome, just what I needed. Is there anyway to isolate them so that the onkey will only work on one specific spreadsheet, and not the whole workbook?

 

[GTO]

Either in the individual worksheet's module, or in ThisWorkbook module, the sheet activate/deactivate events could be used.

 

[errtu]

Either in the individual worksheet's module, or in ThisWorkbook module, the sheet activate/deactivate events could be used.

mmmm... an example of this, please? so i can work it out from there?

say i have in ThisWorkbook:

Private Sub Workbook_Open()
Run "RunOnOpen"
End Sub

module RunOnOpen is:

Sub RunOnOpen()
Application.OnKey "B", "Button"
sub end

and button is:

Sub Button()
 Sheets("Hello").Select
End Sub

If I go to spreadsheet "Close", I don't want the whole "B" button procedure to happen (so it wont take me back to "Hello").

 

[GTO]

Hi there,

I personally would not use Workbook_Open or _Close for this. Rather, use the workbook's activate and deactivate events. These run upon opening/closing anyways, and additionally, catch when the user flips between workbooks in the same instance.

A quick example:

In a Standard Module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> ActivateKey()<br>    Application.OnKey "B", "Sheet2.CommandButton1_Click"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br>    <br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> DeactivateKey()<br>    Application.OnKey "B"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

In the Worksheet's Module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    Application.Goto Sheet1.Range("A10"), <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()<br>    <SPAN style="color:#00007F">Call</SPAN> ActivateKey<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Deactivate()<br>    <SPAN style="color:#00007F">Call</SPAN> DeactivateKey<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

In ThisWorkbook Module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Activate()<br>    <br>    <SPAN style="color:#00007F">If</SPAN> TypeName(ActiveSheet) = "Worksheet" <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> ActiveSheet.CodeName = "Sheet2" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Call</SPAN> ActivateKey<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Deactivate()<br>    <SPAN style="color:#00007F">Call</SPAN> DeactivateKey<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark

 

[GTO]

ACK! I forgot to mention that the OnKey is case sensitive.

 

[errtu]

ACK! I forgot to mention that the OnKey is case sensitive.

I figured as much. Thanks for the example. I'll try it out today.