method 'Intersect' of object '_Application' failed

[MeStillLearning]

Hi Folks,

Hope you can help.

I have a userform that uses an inkpicture object named "Signature_Pad" to obtain the users signature and place it on an excel sheet. The userform opens when a user selects one of the Named Ranges I have created (4 in total), using Worksheet_SelectionChange.
I have 2 named ranges on one sheet named "Handicap" and 2 named ranges on another sheet named "MatchSheet".
To determine which named range is currently selected, (so I may adjust the newInk), I use the Function InRange (seen Below) in code.

Now this works perfectly when either named range on the sheet named "Handicap" is selected but on the sheet "MatchSheet", it returns the error -:

Runtime error '1004'

method 'Intersect' of object '_Application' failed​

Error triggers on

[COLOR=#222222][FONT=Verdana]InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)[/FONT][/COLOR]

I just can't seem to figure out what I'm doing wrong. All my searches for this error only seem to come back with Global failed not application failed and don't give me the answer I'm searching for.

any help would be much appreciated.

cheers.

the code below is in my userform code.

Function InRange(Range1 As Range, Range2 As Range) As Boolean
    ' returns True if Range1 is within Range2
    InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)
End Function

Private Sub cmdbtnUse_Click()
    Signature_Area.Ink.ClipboardCopy
    On Error Resume Next
        Application.EnableEvents = False
        ActiveSheet.Paste
        Application.EnableEvents = True
        If Err Then Exit Sub
    On Error GoTo 0
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
    Set newInk = Selection
        With newInk
            If InRange(ActiveCell, Range("hanWinCapSign")) Then
                .Name = "WinnerSign"
                .Top = Range("hanWinCapSign").Top
                .Left = Range("hanWinCapSign").Left
            ElseIf InRange(ActiveCell, Range("hanLosCapSign")) Then
                .Name = "LoserSign"
                .Top = Range("hanLosCapSign").Top
                .Left = Range("hanLosCapSign").Left
            ElseIf InRange(ActiveCell, Range("matWinCapSign")) Then
                .Name = "WinnerSign"
                .Top = Range("matWinCapSign").Top
                .Left = Range("matWinCapSign").Left
            ElseIf InRange(ActiveCell, Range("matLosCapSign")) Then
                .Name = "LoserSign"
                .Top = Range("matLosCapSign").Top
                .Left = Range("matLosCapSign").Left
            End If
        End With
    Unload Me
End Sub

 

[steve the fish]

Try the function like this:

Function InRange(Range1 As Range, Range2 As Range) As Boolean

If Range1.Parent.Name = Range2.Parent.Name Then
    InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)
Else
    InRange = False
End If

End Function

Intersect wont like the ranges on different sheets.

 

[bobsan42]

Most probably the problem is that the ranges you want to intersect are on different sheets so it is impossible.
I guess that when you run the code from sheet 1 the two ranges on sheet 2 will trigger an error, if you run the code from sheet 2 - the ranges on sheet 1 will do it.

So one way to work around it is to work around this is to set InRange = False if an error occurres.

Function InRange(Range1 As Range, Range2 As Range) As Boolean
[COLOR=#ff0000]On error resume next[/COLOR]
    ' returns True if Range1 is within Range2
    InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)
[COLOR=#ff0000]If Err > 0 then InRange = False[/COLOR]
End Function

However what I would do is to check on which sheet is the active cell and only then check for intersection with only two ranges of the same sheet:

[COLOR=#ff0000]if activecell.parent.name =  Range("hanWinCapSign").parent.name then [/COLOR]
If InRange(ActiveCell, Range("hanWinCapSign")) Then
                .Name = "WinnerSign"
                .Top = Range("hanWinCapSign").Top
                .Left = Range("hanWinCapSign").Left
            ElseIf InRange(ActiveCell, Range("hanLosCapSign")) Then
                .Name = "LoserSign"
                .Top = Range("hanLosCapSign").Top
                .Left = Range("hanLosCapSign").Left

HTH

 

[MeStillLearning]

Thanks for that Steve.

Works like a charm. I knew it had to do with being on different sheets.

Hadn't read about Parent before but does open up avenues of improving other code. Cheers for the lesson.

 

[MeStillLearning]

Thanks bobsan,

Code works just as well.
Just goes to show, there is always more than 1 way to approach things.