Solved Getting Error Intersect of Object Globad Failed (1 Viewer)

rehanemis

Registered User.
Local time
Today, 23:31
Joined
Apr 7, 2014
Messages
195
I am trying to delete record but getting error while clicking on cell .
Code added on Sheet2:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("q:q")) Is Nothing Then
    If ActiveCell.Value = "Add to Orders List" Then
        rowNum = Target.Cells.Row
        cellval = WorksheetFunction.Sum(Sheet2.Range("E" & rowNum + 3 & ":O" & rowNum + 3))
        If cellval = 0 Then
            MsgBox "Please Add Number of Order/s (Yellow Cells)", vbInformation, "Input Required"
            Sheet2.Range("E" & rowNum + 3 & ":O" & rowNum + 3).Select
        Else
            id = Range("A" & rowNum).Value
                Set FindRow = Sheet3.Range("A:A").Find(What:=id, LookIn:=xlValues, LookAt:=xlWhole) 'Searching for Combobox1 value
                    If Not (FindRow Is Nothing) Then
           'checking if same product is already added
                    MsgBox "Already Added"
             Else
                        lastRow = Sheet3.Range("A10000").End(xlUp).Row 'Getting last row in orders sheet
                    If lastRow < 3 Then
                        Sheet2.Range(rowNum & ":" & rowNum + 4).Copy Sheet3.Range("A" & lastRow + 2)
                        Sheet3.Range("Q" & lastRow + 2).Value = "DELETE"
                        Sheet2.Range("O" & rowNum + 1).Select
                    ElseIf lastRow >= 3 Then
                        Sheet2.Range(rowNum & ":" & rowNum + 4).Copy Sheet3.Range("A" & lastRow + 6)
                        Sheet3.Range("Q" & lastRow + 6).Value = "DELETE"


                        Sheet2.Range("q" & rowNum + 1).Select
                    End If
            End If
        End If
    End If

End If
End Sub

On Sheet3 I have code
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("Q:Q")) Is Nothing Then
    If ActiveCell = "DELETE" Then
        MsgBox "Delete is clicked"
    End If

End If
End Sub

When I click on Sheet2 on specific cell there is error. Error 1004 " Method Intersect of Object Global Failed"
Any solution ?
 

cheekybuddha

AWF VIP
Local time
Today, 19:31
Joined
Jul 21, 2014
Messages
2,237
Replace all instances of 'ActiveCell' with 'Target':
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("q:q")) Is Nothing Then
    If Target.Value = "Add to Orders List" Then
' ...'
 

rehanemis

Registered User.
Local time
Today, 23:31
Joined
Apr 7, 2014
Messages
195
After changing to Target it thronging error "Error 13" Type mismatch. and highlight the "If Target.Value = "Add to Orders List" Then"
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:31
Joined
Sep 21, 2011
Messages
14,046
So what is the address and value in Target?
 

cheekybuddha

AWF VIP
Local time
Today, 19:31
Joined
Jul 21, 2014
Messages
2,237
After changing to Target it thronging error "Error 13" Type mismatch. and highlight the "If Target.Value = "Add to Orders List" Then"
You may have selected more than one cell.

Add a check for Target.Cells.Count = 1 before trying to access its value (untested)
 

rehanemis

Registered User.
Local time
Today, 23:31
Joined
Apr 7, 2014
Messages
195
I tried on single cell not on merged one but no luck.
The target value I am looking for is in column Q so when I click on the cell of column Q program should check the cell value and do action accordingly.
 

cheekybuddha

AWF VIP
Local time
Today, 19:31
Joined
Jul 21, 2014
Messages
2,237
Take it one step at a time:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("q:q")) Is Nothing Then
    MsgBox Target.Address & ": " & Target.Value
  End If
End Sub
 

rehanemis

Registered User.
Local time
Today, 23:31
Joined
Apr 7, 2014
Messages
195
Thanks for all to reply me on this.
using application.enableevent=false and then true works fine.
 

Isaac

Lifelong Learner
Local time
Today, 12:31
Joined
Mar 14, 2017
Messages
8,738
FYI you can also simply test for the column property and or the row property of the target. I find this slightly simpler to read and type but either way works you can do the intersect too if you prefer.
If target.column=5, etc
 

Users who are viewing this thread

Top Bottom