Prevent Macro From Running In Other Workbooks

[J Lee]

I have a workbook Productivity.xls where users will enter data. The data fields are scattered and do not follow any sort of organized pattern and can not be moved. Everything except the fields they can access are locked. Because of this, when you press Enter or down in certain cells the screen will jump to the bottom of the workbook. To prevent this, I created a macro to execute the Tab key instead which works great. The problem is, if I am in another workbook (Book1.xls) and Productivity.xls is open and I press enter or down, the macro runs and my active cell Tabs across the screen of Book1 instead of going down.

In the ThisWorkbook object (in Productivity.xls) I have the following code:

Private Sub Workbook_Open()
Application.OnKey "~", "PressedEnter"
Application.OnKey "{ENTER}", "PressedEnter"
Application.OnKey "{DOWN}", "PressedEnter"
End Sub

And in a regular Module in the same workbook, I have this:

Private Sub PressedEnter()
Application.SendKeys "{TAB}"
End Sub

Does anyone have any suggestions on how I can make sure the macro from the Productivity workbook doesn't run in any other workbook I have open at the same time?

Thanks!

 

[Gary McMaster]

Put your code in the workbook "Activate" event. Then write the opposite procedure (to turn off the key intercepts) and put that in the workbook "DeActivate" event.

Gary

 

[J Lee]

Thank you, it worked perfectly!