Run macro when selecting specific worksheet

[drex79]

Hello,

I am looking for a solution on how I can run a macro when selecting a specific worksheet (eg. "Sheet1").
I am currently using this in the code section of Sheet1 in VB:

Sub Worksheet_SelectionChange()
(code)
End Sub

This method is working perfectly until I switch (Alt+Tab) to another workbook.
Since the sheet is still active on the other workbook, the macro is still engaged.

Is there a way to contain the macro to a specific workbook?

[Kenneth Hobs]

Welcome to the forum!

I don't see that problem. SelectionChange fires when that sheet is active and you select cell(s). The Activate event will fire when you change worksheets in that workbook.

Private Sub Worksheet_Activate()  MsgBox ActiveSheet.Name, , ActiveWorkbook.Name
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  MsgBox ThisWorkbook.Name, , ActiveWorkbook.Name
End Sub

[drex79]

Hello Kenneth,

Thank you for your validation that above would work.

Perhaps it is the application I am using it for. I have created an example.xls.

Essentially, if the cell matches a target format "#,##0" in Sheet1 only (see cell filled with yellow);
CTRL + UP adds 1 to the active cell...
CTRL + DOWN subtracts 1 to the active cell...

Changing to anything other than Sheet1 will return the above to normal Excel behaviour (eg. selecting Sheet2 and performing the above on the yellow cell does nothing).

However, if you copy either yellow cell to a new book, the above binding is still in place when it should not be.

Any further insight would be greatly appreciated.

Sincerely,

Neil

[Paul_Hossler]

What seems to be happening is that you're using setting OnKey which remains in force after you do the add_1 or subtract_1 and go to the other sheet

So if you add or subtract in the yellow and then go right to the second the OnKey is still set

If you change the selection on Sheet1 and it's not the yellow-type cells, the Selection_Change has a chance to clear the OnKey

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim DateFormats, DF
   
DateFormats = Array("#,##0")
        
For Each DF In DateFormats
If DF = Target.NumberFormat Then
    Application.OnKey "^{UP}", "add_1"
    Application.OnKey "^{DOWN}", "subtract_1"
Else
    Application.OnKey "^{UP}"
    Application.OnKey "^{DOWN}"
End If
Next
End Sub

You should clear the OnKey when you leave the Sheet1

Private Sub Worksheet_Deactivate()
    Application.OnKey "^{UP}"
    Application.OnKey "^{DOWN}"
End Sub

[drex79]

Hello Paul,

Thank you for your proposed solution. Unfortunately, that is something I thought as well might work in my many attempts at a solution.

Creating a new book (CTRL+N) and copying the yellow cell, the keymaps follow as I believe the example.xls!Sheet1 is still active on the other book.

If I switch to example.xls!Sheet2 then go back to the new book i created, they keymaps are no longer add_1 and subtract_1.

Perhaps this is due to the workstation environment or Excel version to cause the deactivate not to fire? (Win7x64 / Excel 2010)

Any other theories, I'm all eyes!

[Paul_Hossler]

Again, that is because OnKey is an Application level setting

When you select example.xls!Sheet1 C8 to copy it, that sets the Application.OnKey and leaves it in effect

When you go to a new or other WB the OnKey is still set

Try this

1.Select the Yellow cell on sheet1
2. Copy with control-c
3. Click B11 (this changes the selection, and the

[CODE]
Else
Application.OnKey "^{UP}"
Application.OnKey "^{DOWN}"
End If[
/CODE]

part will clear the OnKey settings)

4. Control-N
5. Now paste the yellow cell
6. The OnKey works as 'normal'



Try adding this to the ThisWorkbook module of example.xls

Option Explicit
Private Sub Workbook_Deactivate()
    Application.OnKey "^{UP}"
    Application.OnKey "^{DOWN}"
End Sub

Private Sub Workbook_Activate()
    Dim rSelectedCell As Range
    If TypeOf Selection Is Range Then
        Set rSelectedCell = Selection
        rSelectedCell.Offset(1, 1).Select
        rSelectedCell.Select
    End If
End Sub

and this to the code module of

Sheet1

Private Sub Worksheet_Deactivate()
    Application.OnKey "^{UP}"
    Application.OnKey "^{DOWN}"
End Sub

Private Sub Worksheet_Activate()
    Dim rSelectedCell As Range
    If TypeOf Selection Is Range Then
        Set rSelectedCell = Selection
        rSelectedCell.Offset(1, 1).Select
        rSelectedCell.Select
    End If
End Sub

[drex79]

You probably get this all too often, but you're amazing!

I see what you mean (Step 3) and it makes perfect sense.

Your solution in Workbook_Activate() amazes me, I'll have to read more on TypeOf.

Again, thank you so much for taking the time to respond and help with something I've dealt with for way too long!

[Paul_Hossler]

<blush>

Since the Selection can be object types other than just Ranges, I think it's just defensive programming to test for things like that.

Normally, there's no issue for code operating complete under your control, but once the user gets involved .... sigh


If the user happened to have a Textbox selected and the macro was expecting to get (say) a Range address of the Selection, then you'll get #438, Object doesn't support property or method

Option Explicit
Sub TestTypeof()
    Worksheets("Sheet1").Shapes("TextBox 1").Select
    
    If Not TypeOf Selection Is Range Then
        MsgBox TypeName(Selection)
    Else
        MsgBox Selection.Address
    End If
    MsgBox Selection.Address   ' <<< 438
End Sub