Tags: allows, assign, ctrl, excel, execute, key, macro, marco, microsoft, msdn, onkey, onl, procedure, record, recorder, software, suck, workbook, worksheet, worksheets

assign onkey to 1 worksheet

On Microsoft » Microsoft Excel

5,834 words with 5 Comments; publish: Fri, 23 May 2008 05:02:00 GMT; (30678.13, « »)

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

--

short_n_curl

----

short_n_curly's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2157

View this thread: http://www.excelforum.com/showthread.php?threadid=38387

All Comments

Leave a comment...

  • 5 Comments
    • Hi short_n_curly,

      short_n_curly wrote:

      > 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]

      #1; Fri, 23 May 2008 05:03:00 GMT
    • 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

      #2; Fri, 23 May 2008 05:04:00 GMT
    • 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

      #3; Fri, 23 May 2008 05:05:00 GMT
    • 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

      short_n_curly

      ---

      short_n_curly's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21576

      View this thread: http://www.excelforum.com/showthread.php?threadid=383876

      #4; Fri, 23 May 2008 05:07:00 GMT
    • 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" <short_n_curly.1ri0eh_1120233927.4843.excel.todaysummary.com.excelforum-nospam.com>

      wrote in message

      news:short_n_curly.1ri0eh_1120233927.4843.excel.todaysummary.com.excelforum-nospam.com...

      > 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

      >

      > --

      > short_n_curly

      > ---

      > short_n_curly's Profile:

      http://www.excelforum.com/member.php?action=getinfo&userid=21576

      > View this thread: http://www.excelforum.com/showthread.php?threadid=383876

      >

      #5; Fri, 23 May 2008 05:07:00 GMT