VBA Create a different "Target" inside each procedure.

[RuiFlora]

Hello. I am trying to make a code that calls different procedures depending on the ranges I select. The problem is, inside that procedures I have other Intersects to call other procedures. And it gives me errors because the target is not defined on those sub procedures. Here the code I have to better explain my issue:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim Bmw As Range
Dim Audi As Range


Set Bmw= Worksheets("Cars").Range("Bmw")
Set Audi = Worksheets("Cars").Range("Audi")


If Not Intersect(Target, Sheet12.Range("Bmw")) Is Nothing Then
Call BmwProc
ElseIf Not Intersect(Target, Sheet12.Range("Audi")) Is Nothing Then
Call AudiProc
End If
End Sub
_______________________________________________________________________________________
BmwProc()

If Not Intersect(Range("E55SeriesHide"), Target) Is Nothing Then
Call E55SeriesHide
End If
If Not Intersect(Range("E38SeriesHide"), Target) Is Nothing Then
Call E38SeriesHide
End If

End Sub
_______________________________________________________________________________________


AudiProc()

If Not Intersect(Range("A4SeriesHide"), Target) Is Nothing Then
Call A4SeriesHide
End If
If Not Intersect(Range("A6SeriesHide"), Target) Is Nothing Then
Call A6SeriesHide
End If

End Sub
________________________________________________________________________________________

It gaves me the error of "Object required", which is normal because target is not defined in BmwProc().
But if i replace Target by ActiveCell, the code works but only for 1 cell. And I need that being working for the whole selection of cells.
Hope someone can help me in this issue!

Many thanks in advance.

 

[dmt32]

Hi,
you need to pass the range (Target) as an argument to your procedures.

Try this change & see if helps:

Sub BmwProc(ByVal Target As Range)
    If Not Intersect(Range("E55SeriesHide"), Target) Is Nothing Then
        Call E55SeriesHide
    End If
    If Not Intersect(Range("E38SeriesHide"), Target) Is Nothing Then
        Call E38SeriesHide
    End If
End Sub

And from your worksheet_Change event call like this:

Call BmwProc(Target)

Hope helpful

Dave

 

[RuiFlora]

Hi,
you need to pass the range (Target) as an argument to your procedures.

Try this change & see if helps:

Sub BmwProc(ByVal Target As Range)
    If Not Intersect(Range("E55SeriesHide"), Target) Is Nothing Then
        Call E55SeriesHide
    End If
    If Not Intersect(Range("E38SeriesHide"), Target) Is Nothing Then
        Call E38SeriesHide
    End If
End Sub

And from your worksheet_Change event call like this:

Call BmwProc(Target)

Hope helpful

Dave

Touché. Perfect!

Many thanks!

 

[dmt32]

most welcome.

Dave