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

 

[Eric Penfold]

Sorry this is the right code

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

I have one double click code for the workbook code sorry my mistake....

Full code below[/B]

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)
                 Set Text = .InputBox.Value
                 If TypeName(Text) = vbBoolean And Rng = False Then
                 If Not Text Then
                 Exit Sub
                 End If
                 End If
                Application.EnableEvents = False
                 Rng.EntireRow.Delete
                 Exit Sub
                 Application.EnableEvents = True
                 

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

Dim ws       As Worksheet
Dim MyCell As Range
Dim Rng     As Range
Dim LRow   As Long


Set ws = ActiveSheet


        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

If Target.Column = 10 Then
If Sh.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

 

[Peter_SSs]

Full code below

Hmm, that code does not even compile.

Try this as your double-click code in the ThisWorkbook module.

Public Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  Dim Rng As Range
                 
  On Error Resume Next
  Set Rng = Application.InputBox(Prompt:="Select the range to Delete", Title:="Select Range", Type:=8)
  On Error GoTo 0
  Cancel = True
  If Not Rng Is Nothing Then
    Application.EnableEvents = False
    Rng.EntireRow.Delete
    Application.EnableEvents = True
  End If
End Sub

 

[Eric Penfold]

This works but i need it to exit sub and not go to next sub routine?

 

[Peter_SSs]

This works but i need it to exit sub and not go to next sub routine?

Assuming that by "next sub" you mean the Workbook_SheetChange code then it does not go to that for me. That is prevented by these lines

    Application.EnableEvents = False
    Rng.EntireRow.Delete
    Application.EnableEvents = True

Are you sure that you do not have some other code that is that causing the issue?

Put a Break Point on this line in the code in the ThisWorkbook module and then double-click in your sheet. When the code stops at the break point press F8 to work through the other steps to the end of the code and that may shed some light on what is happening.

 

[Eric Penfold]

This is if user presses cancel then the code exits and it works fine.

 

[Peter_SSs]

This is if user presses cancel then the code exits and it works fine.

So, are you all good now?

For me,
- if Cancel is clicked on the Input box then nothing happens on the the sheet and the Workbook_SheetChange code is not triggered.
- if a range is selected in the Input Box and OK is clicked then the code deletes the rows from the sheet and the Workbook_SheetChange code is not triggered.
- if OK is clicked on the Input Box while the 'Select the range to Delete' box is still empty then then a 'problem' warning box comes up and once you click OK on that you are returned to the Input Box again

 

[Eric Penfold]

For me - if a range is selected in the Input Box and OK is clicked then the code deletes the rows from the sheet and the Workbook_SheetChange code is not triggered.
It does trigger for me but could you say some how if a cell is double clicked then the next sub routine won`t trigger?

 

[Peter_SSs]

It does trigger for me ..

Then have you tried what I suggested earlier to see what is causing that?

Are you sure that you do not have some other code that is that causing the issue?

Put a Break Point on this line in the code in the ThisWorkbook module and then double-click in your sheet. When the code stops at the break point press F8 to work through the other steps to the end of the code and that may shed some light on what is happening.

 

[Eric Penfold]

Yes i have

 

[Peter_SSs]

Then at what point did the code jump from the double-click code to the sheet change code?