assign onkey to 1 worksheet

[short_n_curly]

marco recorder allows you to record an onkey macro suck as on key CTRL+
execute procedure however i have a workbook with 80+ worksheets and onl
want the onkey command to work on certain sheets, can this command b
assigned into the code i any particular worksheet if so how?
i know that the command is application.onkey "^{t}" for the above onke
command but this does not seem to work when i enter it into vba cose fo
a particular worksheet please advise thank

 

[Jake Marx]

Hi short_n_curly,

marco recorder allows you to record an onkey macro suck as on key
CTRL+t execute procedure however i have a workbook with 80+
worksheets and only want the onkey command to work on certain sheets,
can this command be assigned into the code i any particular worksheet
if so how?
i know that the command is application.onkey "^{t}" for the above
onkey command but this does not seem to work when i enter it into vba
cose for a particular worksheet please advise thanks

Not directly. But in the procedure you assign to Ctrl+t via OnKey, you can
check the ActiveSheet to see if it matches the sheet you're targeting:

Sub startit()
Application.OnKey "^{t}", "test"
End Sub

Sub test()
If ActiveSheet Is Sheet1 Then
MsgBox ActiveSheet.Name
End If
End Sub

Sub endit()
Application.OnKey "^{t}"
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Roman]

You can paste a short test of activesheet name in the beginning of the
recorded macro. This will stop the macro if active sheet name is
"Sheet1", "Sheet2" or "Sheet3":


Select Case ActiveSheet.Name
Case "Sheet1", "Sheet2", "Sheet3"
Exit Sub
End Select

 

[Roman]

Vice versa:
this stops the macro if name of activesheet is not "Sheet4"

Sub macro_name()

Select Case ActiveSheet.Name
Case "Sheet4"
goto runit:
End Select
exit sub
runit:

<<<your code here>>>

End sub

 

[short_n_curly]

ok this helps but...... if i want ctrl+r to mean the same thing on each
individual worksheet (to reset it to a default state) but each
worksheet is different e.g one worksheet will have programmed via vba,
delete cells a30:a50. and a different worksheet via a different macro
procedure will delete a1:a10, with this proposed method is it possible
to assign ctrl+r to have more than 1 dirrerent function
what i intend to ask is if i wanted event procedure to on a cell entry
to trigger a macro i will programme in that worksheet

Private Sub Worksheet_Change(ByVal Target As Range)

set target.row and column and programme in the macro

end sub

what is the private sub header for an event to trigger in that
worksheet by pressing keys ctrl+r
if i could have this i could programme a different outcome in each
worksheet using the same key entry ctrl+r and therfor users will be
able to reset each sheet back to default with the same keystrokes
hope this explains things better thanks for the replies so far

 

[Tom Ogilvy]

Onkey is an application level assignment.

Your recourse would be
- to use the Activate event of each sheet to reasign it to a sheet
specific macro.
- have one macro, but have a big case statement that determines which
action to take based on the activesheet

--
Regards,
Tom Ogilvy


"short_n_curly" <[email protected]>
wrote in message
news:[email protected]...