Worksheet_Change with named range

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,474
I have a named range 'DailyDB_Crew' which consists of a non contiguous range - K28:K35, R28:R35, K44:K51, R44:R51

If any of these cells are selected I would like the Worksheet_Change or similar to execute so a userform can be displayed.

I hoped that
Code:
If Not Intersect(Target.Address, Range("DailyDB_Crew").Address) Then
    Exit Sub
    Else
    'user form code'
End If
would have worked but bugs out before starting.


Thanks
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
If Application.Intersect(Target, Range("DailyDB_Crew")) Is Nothing Then
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
mikeymay,

.Address creates a string.
Named range is effectively the range address.
Target is by definition a Range.
Plus need to establish if the intersection exists by compare to Nothing

Try....

Code:
If Not Intersect(Target, Range("DailyDB_Crew")) Is Nothing Then
    Exit Sub
    Else
    'userform code
End If

Hope that helps.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,582
Messages
5,548,878
Members
410,881
Latest member
toonces
Top