Code giving error when in module

[Beneindias]

So, I'm having a problem with some code in VBA, I bet that it's an easy solution, but I'm a noob.
I created a module, to check if it should call another module to sort my table.
This code is throwing me an error "Object required" and it's pointing to the line "If Not Intersect(col, Target) Is Nothing Then".

I don't know how to fix this and already tried a lot of ideas.
This code was working when I had it in the worksheet, but as soon as I created the module, I'm having errors. Already fixed a few, but can't find a solution to this one.


This is in the first worksheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Call InstructionsToSort
End Sub

This is in a module (chbx is an ActiveX checkbox):

Sub InstructionsToSort()
    Dim col As Range
    Dim chbx As Variant
    
    
    Set col = ActiveSheet.ListObjects("Abastecimentos").ListColumns("Data").Range
    Set chbx = ActiveSheet.AutoSort
    
    If Not Intersect(col, Target) Is Nothing Then
        If chbx.Value = True Then
            'Selection IS inside the range.
            Call SortTable
        End If
    End If
End Sub

Again, the code that I have in the module was working when I had it directly in the worksheet's code.

Thanks for any help

 

[rlv01]

You have to pass the Target range object to your InstructionsToSort subroutine as a parameter. Something like this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Call InstructionsToSort(Target)
End Sub

Sub InstructionsToSort(ByRef Target As Range)
    Dim col As Range
    Dim chbx As Variant
    
    
    Set col = ActiveSheet.ListObjects("Abastecimentos").ListColumns("Data").Range
    Set chbx = ActiveSheet.AutoSort
    
    If Not Intersect(col, Target) Is Nothing Then
        If chbx.Value = True Then
            'Selection IS inside the range.
            Call SortTable
        End If
    End If
End Sub

 

[Beneindias]

You have to pass the Target range object to your InstructionsToSort subroutine as a parameter. Something like this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Call InstructionsToSort(Target)
End Sub

Sub InstructionsToSort(ByRef Target As Range)
    Dim col As Range
    Dim chbx As Variant
   
   
    Set col = ActiveSheet.ListObjects("Abastecimentos").ListColumns("Data").Range
    Set chbx = ActiveSheet.AutoSort
   
    If Not Intersect(col, Target) Is Nothing Then
        If chbx.Value = True Then
            'Selection IS inside the range.
            Call SortTable
        End If
    End If
End Sub

Thanks, that's it.

I tried that, but instead of ByRef, I used ByVal.
Now it makes sense.

Thank you, again

 

[Akuini]

Another thing, you need to define the range where a double-click will call Sub InstructionsToSort, otherwise it will happen on any cell. Say the range is Range("A2:A10").

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
            Call InstructionsToSort(Target)
            Cancel = True
    End If

End Sub

 

[Beneindias]

Another thing, you need to define the range where a double-click will call Sub InstructionsToSort, otherwise it will happen on any cell. Say the range is Range("A2:A10").

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
    If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
            Call InstructionsToSort(Target)
            Cancel = True
    End If

End Sub

Yeah, thats a nice idea. So I'm not calling InstructionsToSort everytime.

That way, I could even remove that part of the logic from my module, to avoid having the same logic two times.
But then, it's almost better to put all my code in the worksheet, instead of the module, like I had before...

What's the general consensus? Logic in worksheet or in modules?

Thanks for your help

 

[rlv01]

If the code is specific to the worksheet, keep it there. If it has general applicability beyond a single worksheet, put it in a general code module.