Intersect method error when changing work sheet

Question

Whenever a user is editing a cell and switching the sheet while editing, the following error message appears:

Run-time error '1004':
Method 'Intersect' of object '_Global' failed

Here's the code:

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    'If Changes Intersect with Data Inventory Range
    If Not Intersect(Target, Range(Range("E6"), Range("E" & Cells(Rows.Count, 2).End(xlUp).Row))) Is Nothing Then
         If (TypeName(Target.Value) = "String") Then 'Distinction between pressing button and modifying process name
            If Target.Value <> ChangeTrack Then 'Prevents duplicate check from being called if Process name stays the same
                Call generate_ID(Target.Value, Target, sh)
            End If
        End If
    End If
End Sub

Sorry for the nested if statements (for some reason it doesn't work if I link up multiple statements with the 'And' operator inbetween?)

I think it has to do with range comparisons between different sheet, that lead to a crash in the Intersect method but I also tried to explicitly define the Range with sh.Range(...) with no luck.

`I think it has to do with range comparisons between different sheet`. Yes you are somewhat correct. You need to `Explicitly` reference your objects. — L42, Aug 17 '18 at 08:42
did you try `If Not Intersect(Target, sh.Range(sh.Range("E6"), sh.Range("E" & sh.Cells(sh.Rows.Count, 2).End(xlUp).Row))) Is Nothing Then`? — Pᴇʜ, Aug 17 '18 at 08:46
If ChangeTrack is a string, you shouldn't have to determine the data type of Target before you make a comparison. I would be more worried about the two most common conflict areas: a) when Target is more than a single cell and b) when changing a value runs Workbook_SheetChange on top of itself. — , Aug 17 '18 at 09:05

score 0 · Answer 1 · answered Aug 17 '18 at 08:56

You're using a ThisWorkbook Workbook_SheetChange trigger but failing to take advantage of the Sh worksheet parameter being fed into it.

Additionally, a worksheet change will always occur within the worksheet's .UsedRange.

generate_ID(Target.Value, Target, sh) seems redundant. If you pass Target as a parameter then its value and its parent worksheet come along with it.

private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    'If Changes Intersect with Data Inventory Range
    If Not Intersect(Target, sh.Range("E:E")) Is Nothing Then
         If (TypeName(Target.Value) = "String") Then 'Distinction between pressing button and modifying process name
            If Target.Value <> ChangeTrack Then 'Prevents duplicate check from being called if Process name stays the same
                Call generate_ID(Target.Value, Target, sh)
            End If
        End If
    End If
End Sub

I'm guessing that ChangeTrack is some sort of public var.

Intersect method error when changing work sheet

1 Answers1