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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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,522
Messages
5,548,545
Members
410,848
Latest member
anuradhagrewal
Top