Tags: activate, application, applicationonkey, button, command, enter, excel, macro, microsoft, msdn, negative, onkey, pressed, programmed, software, spreadsheet

A negative to using Application.Onkey

On Microsoft » Microsoft Excel

12,469 words with 6 Comments; publish: Fri, 23 May 2008 00:37:00 GMT; (30678.13, « »)

I have programmed an excel spreadsheet to activate a macro when the ENTER

button is pressed using the Application.Onkey command.

My problem is that if I then open another excel spreadsheet, and I press

ENTER, then the macro for the 1st speadsheet tries to run.

Is there a way to ensure that this macro runs only in the specific

speadsheet and not in every speadsheet I have open?

NB/ I have currently written the onkey command into Sheet1 of Microsoft

Excel Objects for the speadsheet.

Thanks

All Comments

Leave a comment...

  • 6 Comments
    • Set the key on the worbook activate event, and reset it on the workbook

      deactivate event.

      --

      HTH

      Bob

      (there's no email, no snail mail, but somewhere should be gmail in my addy)

      "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:444A6128-7340-476A-958A-2EB522F36BE2.excel.todaysummary.com.microsoft.com...

      >I have programmed an excel spreadsheet to activate a macro when the ENTER

      > button is pressed using the Application.Onkey command.

      > My problem is that if I then open another excel spreadsheet, and I press

      > ENTER, then the macro for the 1st speadsheet tries to run.

      > Is there a way to ensure that this macro runs only in the specific

      > speadsheet and not in every speadsheet I have open?

      > NB/ I have currently written the onkey command into Sheet1 of Microsoft

      > Excel Objects for the speadsheet.

      > Thanks

      #1; Fri, 23 May 2008 00:38:00 GMT
    • Thanks Bob - I will give it a go (new to VBA so learning as I go along).

      "Bob Phillips" wrote:

      > Set the key on the worbook activate event, and reset it on the workbook

      > deactivate event.

      > --

      > HTH

      > Bob

      > (there's no email, no snail mail, but somewhere should be gmail in my addy)

      > "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:444A6128-7340-476A-958A-2EB522F36BE2.excel.todaysummary.com.microsoft.com...

      > >I have programmed an excel spreadsheet to activate a macro when the ENTER

      > > button is pressed using the Application.Onkey command.

      > >

      > > My problem is that if I then open another excel spreadsheet, and I press

      > > ENTER, then the macro for the 1st speadsheet tries to run.

      > >

      > > Is there a way to ensure that this macro runs only in the specific

      > > speadsheet and not in every speadsheet I have open?

      > >

      > > NB/ I have currently written the onkey command into Sheet1 of Microsoft

      > > Excel Objects for the speadsheet.

      > >

      > > Thanks

      >

      >

      #2; Fri, 23 May 2008 00:39:00 GMT
    • Do you understand that Workbook activate and deactivate code MUST go in the

      ThisWorkbook code module?

      --

      HTH

      Bob

      (there's no email, no snail mail, but somewhere should be gmail in my addy)

      "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:4E74C000-BC2E-4B46-A11A-03963F5C3AD7.excel.todaysummary.com.microsoft.com...

      > Thanks Bob - I will give it a go (new to VBA so learning as I go along).

      > "Bob Phillips" wrote:

      >> Set the key on the worbook activate event, and reset it on the workbook

      >> deactivate event.

      >> --

      >> HTH

      >> Bob

      >> (there's no email, no snail mail, but somewhere should be gmail in my

      >> addy)

      >> "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> news:444A6128-7340-476A-958A-2EB522F36BE2.excel.todaysummary.com.microsoft.com...

      >> >I have programmed an excel spreadsheet to activate a macro when the

      >> >ENTER

      >> > button is pressed using the Application.Onkey command.

      >> >

      >> > My problem is that if I then open another excel spreadsheet, and I

      >> > press

      >> > ENTER, then the macro for the 1st speadsheet tries to run.

      >> >

      >> > Is there a way to ensure that this macro runs only in the specific

      >> > speadsheet and not in every speadsheet I have open?

      >> >

      >> > NB/ I have currently written the onkey command into Sheet1 of Microsoft

      >> > Excel Objects for the speadsheet.

      >> >

      >> > Thanks

      >>

      #3; Fri, 23 May 2008 00:40:00 GMT
    • No - I can accept this but I don't understand why. The activate and

      deactivate events are available for each sheet, but I will follow your

      instruction.

      Thanks

      "Bob Phillips" wrote:

      > Do you understand that Workbook activate and deactivate code MUST go in the

      > ThisWorkbook code module?

      > --

      > HTH

      > Bob

      > (there's no email, no snail mail, but somewhere should be gmail in my addy)

      > "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:4E74C000-BC2E-4B46-A11A-03963F5C3AD7.excel.todaysummary.com.microsoft.com...

      > > Thanks Bob - I will give it a go (new to VBA so learning as I go along).

      > >

      > > "Bob Phillips" wrote:

      > >

      > >> Set the key on the worbook activate event, and reset it on the workbook

      > >> deactivate event.

      > >>

      > >> --

      > >> HTH

      > >>

      > >> Bob

      > >>

      > >> (there's no email, no snail mail, but somewhere should be gmail in my

      > >> addy)

      > >>

      > >> "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> news:444A6128-7340-476A-958A-2EB522F36BE2.excel.todaysummary.com.microsoft.com...

      > >> >I have programmed an excel spreadsheet to activate a macro when the

      > >> >ENTER

      > >> > button is pressed using the Application.Onkey command.

      > >> >

      > >> > My problem is that if I then open another excel spreadsheet, and I

      > >> > press

      > >> > ENTER, then the macro for the 1st speadsheet tries to run.

      > >> >

      > >> > Is there a way to ensure that this macro runs only in the specific

      > >> > speadsheet and not in every speadsheet I have open?

      > >> >

      > >> > NB/ I have currently written the onkey command into Sheet1 of Microsoft

      > >> > Excel Objects for the speadsheet.

      > >> >

      > >> > Thanks

      > >>

      > >>

      > >>

      >

      >

      #4; Fri, 23 May 2008 00:41:00 GMT
    • They are, but we are talking about WORKBOOK activate,deactivate.

      --

      HTH

      Bob

      (there's no email, no snail mail, but somewhere should be gmail in my addy)

      "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:F6C0464C-B7BF-4AAD-AC75-7865FFC70B7C.excel.todaysummary.com.microsoft.com...

      > No - I can accept this but I don't understand why. The activate and

      > deactivate events are available for each sheet, but I will follow your

      > instruction.

      > Thanks

      > "Bob Phillips" wrote:

      >> Do you understand that Workbook activate and deactivate code MUST go in

      >> the

      >> ThisWorkbook code module?

      >> --

      >> HTH

      >> Bob

      >> (there's no email, no snail mail, but somewhere should be gmail in my

      >> addy)

      >> "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> news:4E74C000-BC2E-4B46-A11A-03963F5C3AD7.excel.todaysummary.com.microsoft.com...

      >> > Thanks Bob - I will give it a go (new to VBA so learning as I go

      >> > along).

      >> >

      >> > "Bob Phillips" wrote:

      >> >

      >> >> Set the key on the worbook activate event, and reset it on the

      >> >> workbook

      >> >> deactivate event.

      >> >>

      >> >> --

      >> >> HTH

      >> >>

      >> >> Bob

      >> >>

      >> >> (there's no email, no snail mail, but somewhere should be gmail in my

      >> >> addy)

      >> >>

      >> >> "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> >> news:444A6128-7340-476A-958A-2EB522F36BE2.excel.todaysummary.com.microsoft.com...

      >> >> >I have programmed an excel spreadsheet to activate a macro when the

      >> >> >ENTER

      >> >> > button is pressed using the Application.Onkey command.

      >> >> >

      >> >> > My problem is that if I then open another excel spreadsheet, and I

      >> >> > press

      >> >> > ENTER, then the macro for the 1st speadsheet tries to run.

      >> >> >

      >> >> > Is there a way to ensure that this macro runs only in the specific

      >> >> > speadsheet and not in every speadsheet I have open?

      >> >> >

      >> >> > NB/ I have currently written the onkey command into Sheet1 of

      >> >> > Microsoft

      >> >> > Excel Objects for the speadsheet.

      >> >> >

      >> >> > Thanks

      >> >>

      >> >>

      >> >>

      >>

      #5; Fri, 23 May 2008 00:42:00 GMT
    • Bob, you need to change your name to "The Oracle".

      All done and working. Big Thanks.

      Andy

      "Bob Phillips" wrote:

      > They are, but we are talking about WORKBOOK activate,deactivate.

      > --

      > HTH

      > Bob

      > (there's no email, no snail mail, but somewhere should be gmail in my addy)

      > "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:F6C0464C-B7BF-4AAD-AC75-7865FFC70B7C.excel.todaysummary.com.microsoft.com...

      > > No - I can accept this but I don't understand why. The activate and

      > > deactivate events are available for each sheet, but I will follow your

      > > instruction.

      > > Thanks

      > >

      > > "Bob Phillips" wrote:

      > >

      > >> Do you understand that Workbook activate and deactivate code MUST go in

      > >> the

      > >> ThisWorkbook code module?

      > >>

      > >> --

      > >> HTH

      > >>

      > >> Bob

      > >>

      > >> (there's no email, no snail mail, but somewhere should be gmail in my

      > >> addy)

      > >>

      > >> "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> news:4E74C000-BC2E-4B46-A11A-03963F5C3AD7.excel.todaysummary.com.microsoft.com...

      > >> > Thanks Bob - I will give it a go (new to VBA so learning as I go

      > >> > along).

      > >> >

      > >> > "Bob Phillips" wrote:

      > >> >

      > >> >> Set the key on the worbook activate event, and reset it on the

      > >> >> workbook

      > >> >> deactivate event.

      > >> >>

      > >> >> --

      > >> >> HTH

      > >> >>

      > >> >> Bob

      > >> >>

      > >> >> (there's no email, no snail mail, but somewhere should be gmail in my

      > >> >> addy)

      > >> >>

      > >> >> "Andybro" <Andybro.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> >> news:444A6128-7340-476A-958A-2EB522F36BE2.excel.todaysummary.com.microsoft.com...

      > >> >> >I have programmed an excel spreadsheet to activate a macro when the

      > >> >> >ENTER

      > >> >> > button is pressed using the Application.Onkey command.

      > >> >> >

      > >> >> > My problem is that if I then open another excel spreadsheet, and I

      > >> >> > press

      > >> >> > ENTER, then the macro for the 1st speadsheet tries to run.

      > >> >> >

      > >> >> > Is there a way to ensure that this macro runs only in the specific

      > >> >> > speadsheet and not in every speadsheet I have open?

      > >> >> >

      > >> >> > NB/ I have currently written the onkey command into Sheet1 of

      > >> >> > Microsoft

      > >> >> > Excel Objects for the speadsheet.

      > >> >> >

      > >> >> > Thanks

      > >> >>

      > >> >>

      > >> >>

      > >>

      > >>

      > >>

      >

      >

      #6; Fri, 23 May 2008 00:43:00 GMT