PDA

View Full Version : [SOLVED] Run macro when selecting specific worksheet



drex79
02-02-2015, 07:20 AM
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
02-02-2015, 10:08 AM
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
02-04-2015, 08:04 AM
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 12807.

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
02-04-2015, 05:10 PM
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
02-04-2015, 06:36 PM
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
02-04-2015, 07:57 PM
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



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

[CODE]
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
02-05-2015, 07:14 AM
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
02-05-2015, 07:28 AM
<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