Forum Discussion

V-GEe7's avatar
V-GEe7
Brass Contributor
Aug 02, 2022
Solved

Application.intersect always goes to nothing

I have a code where the target is the entire sheet, and the intersection ranges are 2 tables in the sheet but the code always considers as intersection is nothing but if I forcefully move the debuggi...
excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Aug 02, 2022

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NOH As Range
    Dim OOH As Range
    Set NOH = Range("NewOH")
    Set OOH = Range("OldOOH")
    If Not Intersect(Target, NOH) Is Nothing Then
        Application.EnableEvents = False
        Range("AB4").Value = Application.Username
        MsgBox "Confirm if 'Edited By' username is correct"
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, OOH) Is Nothing Then
        Application.EnableEvents = False
        Range("AB25").Value = Application.Username
        MsgBox "Confirm if 'Edited By' username is correct"
        Application.EnableEvents = True
    End If
End Sub

    V-GEe7 

HansVogelaar's avatar
HansVogelaar
MVP
Aug 02, 2022

V-GEe7 

NOH and OOH are completely distinct (they don't have any cells in common), so Intersect(Target, NOH, OOH) will always be Nothing.

  • V-GEe7's avatar
    V-GEe7
    Brass Contributor
    Aug 02, 2022
    Oh, That makes sense!
    Is there anyway that I tell the program to say the intersection of Target AND (either NOH or OOH) and then go on to the corresponding IF statement?
    I know that we have formulas in excel for "and" / "or" but I'm not very sure of how to use similar language with vba
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Aug 02, 2022

       

      Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NOH As Range
    Dim OOH As Range
    Set NOH = Range("NewOH")
    Set OOH = Range("OldOOH")
    If Not Intersect(Target, NOH) Is Nothing Then
        Application.EnableEvents = False
        Range("AB4").Value = Application.Username
        MsgBox "Confirm if 'Edited By' username is correct"
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, OOH) Is Nothing Then
        Application.EnableEvents = False
        Range("AB25").Value = Application.Username
        MsgBox "Confirm if 'Edited By' username is correct"
        Application.EnableEvents = True
    End If
End Sub

      V-GEe7 

Resources