Worksheet_Change with named range

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,473
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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,518
Messages
5,548,521
Members
410,844
Latest member
Juno49
Top