Worksheet_Change with named range

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,507
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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,561
Office Version
  1. 365
  2. 2010
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,118
Messages
5,835,492
Members
430,359
Latest member
ManuelCukRia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top