One workbook's sheet selection change code is running in all opened workbooks.

[omairhe]

Hey,

I have the following code that runs inside a workbook category of workbook A

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnKey "{b}", "search_b"
End Sub

and the following code that runs inside a module of the same workbook, workbook A.

Sub search_b()
   Dim Fnd As Range
   Set Fnd = Range("H:H").Find("b*", , xlValues, xlWhole, , , False, False)
If Not Fnd Is Nothing Then
  Application.Goto Cells(Fnd.Row, ActiveCell.Column), scroll:=True
End If
End Sub

Now if I open a new workbook, workbook B, and type in letter b and the code from workbook A will run in the newly opened workbook B.
If I close workbook A and then type b on workbook B. workbook A will open up by itself and run the code on the workbook B.

How do I stop this behaviour and run the code in only that workbook where the code resides? meaning in workbook A.

will appreciate help on this.
ty

 

[Jeffrey Mahoney]

In the help section for OnKey, MS says it continues to run until you run Onkey again with no parameters. Meaning, Onkey alters the keyboard. The letter b now wants to run the macro in that workbook. You will have to Run Onkey without any parameters in the "Private Sub Workbook_BeforeClose(Cancel As Boolean)" SUB.

Jeff

 

[omairhe]

In the help section for OnKey, MS says it continues to run until you run Onkey again with no parameters. Meaning, Onkey alters the keyboard. The letter b now wants to run the macro in that workbook. You will have to Run Onkey without any parameters in the "Private Sub Workbook_BeforeClose(Cancel As Boolean)" SUB.

Jeff

How about

Private Sub Workbook_Activate()
Application.OnKey "{b}", "search_b"
End Sub


Private Sub Workbook_Deactivate()
Application.OnKey "{b}"
End Sub

edit: figured it out

Thank you.

 

[Jeffrey Mahoney]

That'll work! ... and since Deactivate also gets triggered when you close the workbook, it should be good.