Attaching code to ThisWorkbook

[Patrick C. Simonds]

I placed this code under ThisWorkbook but it does not run. If I place the
code on a worksheet it runs fine for that worksheet. My workbook has over 90
worksheets. I was hoping not to have to attach this code to each worksheet.
Am I missing something?



Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.range)

If Not Application.Intersect(Target, range("H11:H22")) Is Nothing Then
VacationTaken.Show


End Sub

 

[Jacob Skaria]

Double click This workbook. and drop down to get the below event

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

If Not Application.Intersect(Target, range("H11:H22")) Is Nothing Then
VacationTaken.Show

End Sub

 

[Patrick Molloy]

selection change is a workSHEET event, and should be in the worksheet for
which you want to trap the event. Sheet Selection Change is the workbook
event, and this should be in the code page for ThisWorkbook.
HINT: in the code module for the worksheet, your objects are Worksheet and
general, for ThisWorkbook, the objects are Workbook and general

 

[Dave Peterson]

Just to add...

I wouldn't type those events. Use the dropdown and choose the event that you
want. All you can do by typing is screw it up <vbg>.

And on top of that, I'd qualify the range, too:

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

If Application.Intersect(Target, Sh.Range("H11:H22")) Is Nothing Then
'do nothing
Else
vacationTaken.Show
End If

End Sub

I didn't use "not" just because I find this syntax easier to understand. But
it's a personal choice.

 

[meh2030]

I placed this code under  ThisWorkbook  but it does not run. If I place the
code on a worksheet it runs fine for that worksheet. My workbook has over90
worksheets. I was hoping not to have to attach this code to each worksheet.
Am I missing something?

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.range)

If Not Application.Intersect(Target, range("H11:H22")) Is Nothing Then
VacationTaken.Show

End Sub

Patrick,

Your Worksheet event does not run in the Workbook class because the
Workbook class handles Workbook events. In other words,
Worksheet_SelectionChange is specific to a worksheet class.
Workbook_SheetSelectionChange is specific to a workbook class. Try
placing your code into the Workbook_SheetSelectionChange event within
ThisWorkbook and see if you receive the results you are looking for.

Best,

Matthew Herbert