I can only use the code below once then it won`t fire the code again.

[Eric Penfold]

Also I would like to end sub when user presses Cancel how is this possible when it`s a range of cells in inputbox

Public Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dim Rng As Range
Dim NoData As Boolean
Dim Cell As Range
Dim Text As Variant
               
            Set Rng = Application.InputBox( _
                Prompt:="Select the range to Delete", _
                  Title:="Select Range", _
                      Type:=8)
                Application.EnableEvents = False
                 Rng.EntireRow.Delete
                 Exit Sub
                 Application.EnableEvents = True
                 
        End Sub

 

[KOKOSEK]

After deleting selected range you've put Exit Sub, so EnableEvents stays False and XL does not know that you double click sheet again.
Try like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng As Range
Dim NoData As Boolean
Dim Cell As Range
Dim Text As Variant
               
On Error Resume Next
Set Rng = Application.InputBox(Prompt:="Select the range to Delete", Title:="Select Range", Type:=8)
On Error GoTo 0
If Rng Is Nothing Then
    Cancel = True
    Exit Sub
End If
Application.EnableEvents = False
Rng.EntireRow.Delete
Application.EnableEvents = True
End Sub

 

[Eric Penfold]

Thanks but also if I run the code it needs to end sub after the process.
Otherwise it goes to the next code which I don`t want.

 

[Peter_SSs]

Otherwise it goes to the next code which I don`t want.

There is no "next code" in what you posted. Have you only given us part of the information?

 

[Eric Penfold]

Sorry here is the full code. So after the first code it needs to exit sub before it goes to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng As Range
Dim NoData As Boolean
Dim Cell As Range
Dim Text As Variant
              
On Error Resume Next
Set Rng = Application.InputBox(Prompt:="Select the range to Delete", Title:="Select Range", Type:=8)
On Error GoTo 0
If Rng Is Nothing Then
    Cancel = True
    Exit Sub
End If
Application.EnableEvents = False
Rng.EntireRow.Delete
Application.EnableEvents = True
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim ws As Worksheet

Set ws = ActiveSheet

        If ws.Name <> "Summary" And ws.Name <> "Trend" And ws.Name <> "Supplier BO" And ws.Name <> "Dif Depot" _
        And ws.Name <> "BO Trend WO" And ws.Name <> "BO Trend WO 2" And ws.Name <> "Different Depot" Then

If Target.Column = 10 Then
If ws.Range("AA1") = "" Then
                ActiveSheet.Range("AA1") = 1
               Call BO_Drop_DownList
               Call BO_Reason
                 End If
            End If
        End If


     If Target.Column = 1 Then
     Call Group_OrderNos
     End If


End Sub

 

[KOKOSEK]

Before it reach Workbook_SheetChange there is End Sub.

 

[Eric Penfold]

Yes but it when I double click in the cell the

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

runs and not the first code?
Because i think both are working of the same column A

 

[KOKOSEK]

Add some conditions in your code, like:

If not intersect(target,[range]) is nothing then
   ' rest of code
End if

 

[Eric Penfold]

I tried to add your code but it still manages to go to next code. Where do you think I should put it in my code?

  Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
        Dim Rng As Range
        Dim NoData As Boolean
        Dim Cell As Range
        Dim Range As Range
        Dim Text As Variant
        
        If Sh.Name <> "Summary" And Sh.Name <> "Trend" And Sh.Name <> "Supplier BO" And Sh.Name <> "Dif Depot" _
        And Sh.Name <> "BO Trend WO" And Sh.Name <> "BO Trend WO 2" And Sh.Name <> "Different Depot" Then
               
        On Error Resume Next
        Set Rng = Application.InputBox(Prompt:="Select the range to Delete", Title:="Select Range", Type:=8)
        On Error GoTo 0
        If Rng Is Nothing Then
            Cancel = True
            Exit Sub
        End If
        Application.EnableEvents = False
        Rng.EntireRow.Delete
        Application.EnableEvents = True
        On Error Resume Next
        If Intersect(Target, [Range]) Is Nothing Then
        Exit Sub
        End If
        End If
        
        End Sub

 

[Peter_SSs]

.. here is the full code.

Thanks. I'm somewhat confused though:

In post #1 and post #9 your double-click code is ..
Public Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
.. and I presumed that is in the ThisWorkbook module.

In post #5 your double-click code is ..
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
.. and I assume that is in the particular sheet's module where you want this double-click action to happen.

Can you clarify how many double-click codes you have and where it/they are and confirm exactly what the code is?