Problem With a Worksheet_Change sub

[TonySondergeld]

Hi, Just looking for some help with this one. When the user clicks a cell, then the active cell is within the range of Column list in the Code below then it will open the a user form however at the moment when you click on any cell not in the list it opens the user form. If you take the NOT Out of [If Not intersect] and it still does work.
Not sure if this is the best way to do this type if thing?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cancel As Variant
On Error GoTo exitS
If Not Intersect(Target, Range("G9:G372, J3:J372, M9:M372, P9:P372, S9:S372, V9:V372, Y9:Y372") _
, Range("AB9:AB372, AE3:AE372, AH9:AH372, AK9:AK372, AN9:AN372, AQ9:AQ372, AT9:AT372, AW9:AW372, AZ9:AZ372") _
, Range("BC9:BC372, BF3:BF372, BI9:BI372, BL9:BL372, BO9:BO372, BR9:BR372, BU9:BU372, BX9:BX372") _
, Range("CA9:CA372, CD3:CD372, CG9:CG372, CJ9:CJ372, CM9:CM372, CP9:CP372, CS9:CS372, CV9:CV372, CY9:CY372") _
, Range("DB9:DB372, DE3:DE372, DH9:DH372, DK9:DK372, DN9:DN372, DQ9:DQ372, DT9:DT372, DW9:DW372, DZ9:DZ372") _
, Range("EC9:EC372, EF3:EF372, EI9:EI372, EL9:EL372, EO9:EO372, ER9:ER372, EU9:EU372, EX9:EX372") _
, Range("FA9:FA372, FD3:FD372, FG9:FG372, FJ9:FJ372, FM9:FM372, FP9:FP372, FS9:FS372, FV9:FV372, FY9:FY372") _
, Range("GB9:GB372, GE3:GE372, GH9:GH372, GK9:GK372, GN9:GN372, GQ9:GQ372, GT9:GT372, GW9:GW372, GZ9:GZ372") _
, Range("HC9:HC372, HF3:HF372, HI9:HI372, HL9:HL372, HO9:HO372, HR9:HR372, HU9:HU372, HX9:HX372") _
, Range("IA9:IA372, ID3:ID372, IG9:IG372, IJ9:IJ372, IM9:IM372, IP9:IP372, IS9:IS372, IV9:IV372, IY9:IY372") _
, Range("JB9:JB372, JE3:JE372, JH9:JH372, JK9:JK372, JN9:JN372, JQ9:JQ372, JT9:JT372, JW9:JW372, JZ9:JZ372") _
, Range("KC9:KC372, KF3:KF372, KI9:KI372, KL9:KL372, KO9:KO372, KR9:KR372, KU9:KU372, KX9:KX372") _
, Range("LA9:LA372, LD9:LD372, LG9:LF372 , LJ9:LJ372, LM9:LM372, LP9:LP372"), Me.Range("LS9:LS372")) Is Nothing Then '
MsgBox "The active cell does NOT Intersect"
GoTo exitS
Else
MsgBox "The active cell does Intersect"
Cancel = True
 UF_DataInput.Show
exitS:
 End If
End Sub

 

[skywriter]

Are you sure you posted the right code?

You claim it opens the form when you click on a cell.

This is worksheet change code, it only runs when you make a change to a cell.

The selection change event is triggered by just selecting a cell.

 

[Peter_SSs]

I agree with skywriter, this code is triggered by a cange to a cell/range, not by selection.

In any case, if you are wanting to use the Worksheet_Change event, this code will always open the userform, as you have found.

The reason is that all the ranges listed (I've coloured a few of them) are disjoint ranges, so there will never be an intersection between them, let alone with 'Target'.

Therefore, 'Intersect(....) is Nothing' will always be True
and hence
'Not Intersect(....) is Nothing' will always be False
and so the UserForm is shown.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cancel As Variant
On Error GoTo exitS
If Not Intersect(Target, [COLOR="#FF0000"]Range("G9:G372, J3:J372, M9:M372, P9:P372, S9:S372, V9:V372, Y9:Y372")[/COLOR] _
, [COLOR="#006400"]Range("AB9:AB372, AE3:AE372, AH9:AH372, AK9:AK372, AN9:AN372, AQ9:AQ372, AT9:AT372, AW9:AW372, AZ9:AZ372")[/COLOR] _
,[COLOR="#0000CD"] Range("BC9:BC372, BF3:BF372, BI9:BI372, BL9:BL372, BO9:BO372, BR9:BR372, BU9:BU372, BX9:BX372")[/COLOR] _
, [COLOR="#A52A2A"]Range("CA9:CA372, CD3:CD372, CG9:CG372, CJ9:CJ372, CM9:CM372, CP9:CP372, CS9:CS372, CV9:CV372, CY9:CY372")[/COLOR] _
, Range("DB9:DB372, DE3:DE372, DH9:DH372, DK9:DK372, DN9:DN372, DQ9:DQ372, DT9:DT372, DW9:DW372, DZ9:DZ372") _
, Range("EC9:EC372, EF3:EF372, EI9:EI372, EL9:EL372, EO9:EO372, ER9:ER372, EU9:EU372, EX9:EX372") _
, Range("FA9:FA372, FD3:FD372, FG9:FG372, FJ9:FJ372, FM9:FM372, FP9:FP372, FS9:FS372, FV9:FV372, FY9:FY372") _
, Range("GB9:GB372, GE3:GE372, GH9:GH372, GK9:GK372, GN9:GN372, GQ9:GQ372, GT9:GT372, GW9:GW372, GZ9:GZ372") _
, Range("HC9:HC372, HF3:HF372, HI9:HI372, HL9:HL372, HO9:HO372, HR9:HR372, HU9:HU372, HX9:HX372") _
, Range("IA9:IA372, ID3:ID372, IG9:IG372, IJ9:IJ372, IM9:IM372, IP9:IP372, IS9:IS372, IV9:IV372, IY9:IY372") _
, Range("JB9:JB372, JE3:JE372, JH9:JH372, JK9:JK372, JN9:JN372, JQ9:JQ372, JT9:JT372, JW9:JW372, JZ9:JZ372") _
, Range("KC9:KC372, KF3:KF372, KI9:KI372, KL9:KL372, KO9:KO372, KR9:KR372, KU9:KU372, KX9:KX372") _
, Range("LA9:LA372, LD9:LD372, LG9:LF372 , LJ9:LJ372, LM9:LM372, LP9:LP372"), Me.Range("LS9:LS372")) Is Nothing Then '
MsgBox "The active cell does NOT Intersect"
GoTo exitS
Else
MsgBox "The active cell does Intersect"
Cancel = True
 UF_DataInput.Show
exitS:
 End If
End Sub

If you are trying to show the userform when any cell(s) within any of those ranges change, then you need to
a) Remove the NOT, and
b) Include a Union of all those ranges

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cancel As Variant
  On Error GoTo exitS
  If Intersect(Target, Union(Range("G9:G372, J3:J372, M9:M372, P9:P372, S9:S372, V9:V372, Y9:Y372") _
      , Range("AB9:AB372, AE3:AE372, AH9:AH372, AK9:AK372, AN9:AN372, AQ9:AQ372, AT9:AT372, AW9:AW372, AZ9:AZ372") _
      , Range("BC9:BC372, BF3:BF372, BI9:BI372, BL9:BL372, BO9:BO372, BR9:BR372, BU9:BU372, BX9:BX372") _
      , Range("CA9:CA372, CD3:CD372, CG9:CG372, CJ9:CJ372, CM9:CM372, CP9:CP372, CS9:CS372, CV9:CV372, CY9:CY372") _
      , Range("DB9:DB372, DE3:DE372, DH9:DH372, DK9:DK372, DN9:DN372, DQ9:DQ372, DT9:DT372, DW9:DW372, DZ9:DZ372") _
      , Range("EC9:EC372, EF3:EF372, EI9:EI372, EL9:EL372, EO9:EO372, ER9:ER372, EU9:EU372, EX9:EX372") _
      , Range("FA9:FA372, FD3:FD372, FG9:FG372, FJ9:FJ372, FM9:FM372, FP9:FP372, FS9:FS372, FV9:FV372, FY9:FY372") _
      , Range("GB9:GB372, GE3:GE372, GH9:GH372, GK9:GK372, GN9:GN372, GQ9:GQ372, GT9:GT372, GW9:GW372, GZ9:GZ372") _
      , Range("HC9:HC372, HF3:HF372, HI9:HI372, HL9:HL372, HO9:HO372, HR9:HR372, HU9:HU372, HX9:HX372") _
      , Range("IA9:IA372, ID3:ID372, IG9:IG372, IJ9:IJ372, IM9:IM372, IP9:IP372, IS9:IS372, IV9:IV372, IY9:IY372") _
      , Range("JB9:JB372, JE3:JE372, JH9:JH372, JK9:JK372, JN9:JN372, JQ9:JQ372, JT9:JT372, JW9:JW372, JZ9:JZ372") _
      , Range("KC9:KC372, KF3:KF372, KI9:KI372, KL9:KL372, KO9:KO372, KR9:KR372, KU9:KU372, KX9:KX372") _
      , Range("LA9:LA372, LD9:LD372, LG9:LF372 , LJ9:LJ372, LM9:LM372, LP9:LP372"), Me.Range("LS9:LS372"))) Is Nothing Then '
          MsgBox "The active cell does NOT Intersect"
          GoTo exitS
  Else
          MsgBox "The active cell does Intersect"
          Cancel = True
           UF_DataInput.Show
exitS:
  End If
End Sub

A few other observations:
- I'm not seeing any use for the 'Cancel' variable in the code & in any case naming a variable the same as a key word used by vba is not a good idea.
- I'm not seeing any use for the GoTo exitS towards the end of the code as that is where the code will go to next anyway.
- I don't see why you have changed how you have written all the other ranges & prefixed the final one with 'Me.'
- In general it is not a good idea to have an On Error GoTo in place throughout the whole of your code as it can mask errors that you probably should know about.

 

[TonySondergeld]

Oh, Yes I did forgot about that. however even after it was changed to SelectionChange(ByVal Target As Range) it still works on what ever cell you click weather or not it in the Range.
new Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cancel As Variant 
On Error GoTo exitS 
If Not Intersect(Target, Range("G9:G372, J3:J372, M9:M372, P9:P372, S9:S372, V9:V372, Y9:Y372") _ , Range("AB9:AB372, AE3:AE372, AH9:AH372, AK9:AK372, AN9:AN372, AQ9:AQ372, AT9:AT372, AW9:AW372, AZ9:AZ372") _ , Range("BC9:BC372, BF3:BF372, BI9:BI372, BL9:BL372, BO9:BO372, BR9:BR372, BU9:BU372, BX9:BX372") _ , Range("CA9:CA372, CD3:CD372, CG9:CG372, CJ9:CJ372, CM9:CM372, CP9:CP372, CS9:CS372, CV9:CV372, CY9:CY372") _ , Range("DB9:DB372, DE3:DE372, DH9:DH372, DK9:DK372, DN9:DN372, DQ9:DQ372, DT9:DT372, DW9:DW372, DZ9:DZ372") _ , Range("EC9:EC372, EF3:EF372, EI9:EI372, EL9:EL372, EO9:EO372, ER9:ER372, EU9:EU372, EX9:EX372") _ , Range("FA9:FA372, FD3:FD372, FG9:FG372, FJ9:FJ372, FM9:FM372, FP9:FP372, FS9:FS372, FV9:FV372, FY9:FY372") _ , Range("GB9:GB372, GE3:GE372, GH9:GH372, GK9:GK372, GN9:GN372, GQ9:GQ372, GT9:GT372, GW9:GW372, GZ9:GZ372") _ , Range("HC9:HC372, HF3:HF372, HI9:HI372, HL9:HL372, HO9:HO372, HR9:HR372, HU9:HU372, HX9:HX372") _ , Range("IA9:IA372, ID3:ID372, IG9:IG372, IJ9:IJ372, IM9:IM372, IP9:IP372, IS9:IS372, IV9:IV372, IY9:IY372") _ , Range("JB9:JB372, JE3:JE372, JH9:JH372, JK9:JK372, JN9:JN372, JQ9:JQ372, JT9:JT372, JW9:JW372, JZ9:JZ372") _ , Range("KC9:KC372, KF3:KF372, KI9:KI372, KL9:KL372, KO9:KO372, KR9:KR372, KU9:KU372, KX9:KX372") _ , Range("LA9:LA372, LD9:LD372, LG9:LF372 , LJ9:LJ372, LM9:LM372, LP9:LP372"), Me.Range("LS9:LS372")) Is Nothing Then 
' MsgBox "The active cell does NOT Intersect" 
GoTo exitS 
Else 
MsgBox "The active cell does Intersect" 
Cancel = True  
UF_DataInput.Show 
exitS:  
End If
End Sub

 

[Peter_SSs]

Oh, Yes I did forgot about that. however even after it was changed to SelectionChange(ByVal Target As Range) it still works on what ever cell you click weather or not it in the Range.

Not sure if you actually read my post?

The reason the code does not work & the actions required to fix that remain identical.

 

[codeedoc]

Oh, Yes I did forgot about that. however even after it was changed to SelectionChange(ByVal Target As Range) it still works on what ever cell you click weather or not it in the Range.

yes with SelectionChange, i have had cell selections jump out of a particular cell range because the logical conditions to stay within a cell range are not tested BEFORE a selection is made.