Application Intersect Issue

[timlh42]

I have the following code Written to open my calendar whenever someone clicks on the targeted cells.

However, I get a Method Range of Object worksheet failed


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range, rng3 As Range


Set rng1 = Range("B2:k2,B4:K4,B6:K6,B8:K8,B10:K10,B12:K12,B14:K14,B16:K16,B18:K18,B20:K20,B22:K22,B24:K24,B26:K26, B28:K28, b30:K30, B32:K32, B34:K34, B36:K36, B38:K38, B40:K40, B42:K42, B44:K44, B46:K46, B48:K48, B50:K50, B52:K52, B54:K54, B56:K56, B58:K58")
Set rng2 = Range("B60:k60,B62:K62,B64:K64,B66:K66,B68:K68,B70:K70,B72:K72,B74:K74,B76:K76,B78:K78,B80:K80,B82:K82,B84:K84, B86:K86, b88:K88, B90:K90, B92:K92, B94:K94, B96:K96, B98:K98, B100:K100, B102:K102, B104:K104, B106:K106, B108:K108, B110:K110, B112:K112, B114:K114, B116:K116")
Set rng3 = Range("B118:k118,B120:K120,B122:K122,B124:K124,B126:K126,B128:K128,B130:K130,B132:K132,B134:K134,B136:K136,B138:K138,B140:K140,B142:K142, B144:K144, b146:K146, B148:K148, B150:K150, B152:K152, B154:K154, B156:K156, B158:K158, B160:K160, B162:K162, B164:K164, B166:K166, B168:K168, B170:K170, B172:K172, B174:K174")

If Application.Intersect(Target, rng1) Is Nothing Then

Call OpenCalendar

If Application.Intersect(Target, rng2) Is Nothing Then

Call OpenCalendar

If Application.Intersect(Target, rng3) Is Nothing Then

Call OpenCalendar

End If
End If
End If

End Sub



I have also used:

If Application.Intersect(Target, Union(rng1, rng2, rng3)) Is Nothing Then

call opencalendar


but this does not work either.



Any help would be greatly appreciated.

 

[MickG]

It would appear Excel does not like split ranges as long as that.
Perhaps a work round, something like below:-
This allows you to select "Even" rows within the specified range.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]If[/COLOR] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not Intersect(Target, Range("B60:K116")) [COLOR="Navy"]Is[/COLOR] Nothing And Target.Row Mod 2 = 0 [COLOR="Navy"]Then[/COLOR]
MsgBox "Even row"
'[COLOR="Green"][B]Do somthing here[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Rick Rothstein]

The text string argument to the Range object cannot be longer than 255 characters... the length for what you are using for rgn2 is 265 characters long. Since your text string argument for rng1 is short enough and since rng2 is offset from rng1 by 58 rows and since rng2 is offset by another 58 rows, you can do your assignments this way...

[table="width: 500"]
[tr]
	[td]Set rng1 = Range("B2:k2,B4:K4,B6:K6,B8:K8,B10:K10,B12:K12,B14:K14,B16:K16,B18:K18,B20:K20,B22:K22,B24:K24,B26:K26, B28:K28, b30:K30, B32:K32, B34:K34, B36:K36, B38:K38, B40:K40, B42:K42, B44:K44, B46:K46, B48:K48, B50:K50, B52:K52, B54:K54, B56:K56, B58:K58")
Set rng2 = rng1.Offset(58)
Set rng3 = rng2.Offset(58)[/td]
[/tr]
[/table]

 

[timlh42]

Thanks Mick.

I tried your work around but I cant get it to fire.

 

[timlh42]

Thanks Rick.

This seems to work but for some reason it caused the odd cells to fire instead of the even.
However, the even cells fire on every cell past column K which is where I wanted the pop up calendar to stop.

 

[timlh42]

I just realized that I didn't substitute the "Open Calendar" where you had MsgBox "Even row"

I just tried it again with that little tweak and it works great!!!!!!

Thank You!!!!!!