Select Case Target.Address for range of cells

[rjvow]

Hi

I have a working code to make a calendar pop up when ever someone selects a specific cell. However I would like to make it work for whenever a cell in a range is selected ("G2:G3000 "I2:I3000" "J2:J3000 "L2:L3000") or anywhere in column G, I, J, and L.

The code I have at the moment will only work for cell L2

It reads:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$L$2"
Call OpenCalendar
End Select
End Sub

Any help would be much appreciated.

Thanks

 

[GTO]

Hi there,

You list partial columns that are all in the whole columns you list?

Presuming you want the whole columns, try:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>  <SPAN style="color:#00007F">If</SPAN> Selection.Count = 1 <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Application.Intersect(Me.Range("G:G,I:I,J:J,L:L"), Target) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>      MsgBox "OpenCalendar"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark

 

[Richard U]

try application.intersect for your named range and target address. Create a range and use the UNION function to make it go across multiple ranges

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange as range


set MyRange = union range(("G2:G3000"), Range ("I2:I3000"), range("J2:J3000"), range(L2:L3000")  'and any other areas you want.

If Not Application.Intersect(Target , myRange) Is Nothing Then Call OpenCalendar

 End Sub

 

[rjvow]

Hi there,

You list partial columns that are all in the whole columns you list?

Presuming you want the whole columns, try:

Option Explicit
**
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
**If Selection.Count = 1 Then
****If Not Application.Intersect(Me.Range("G:G,I:I,J:J,L:L"), Target) Is Nothing Then
******MsgBox "OpenCalendar"
****End If
**End If
End Sub

Hope that helps,

Mark

works perfectly thanks!

 

[GTO]

Glad that helped and thanks for the feedback