question re "for each range in target"

[werafa]

Hi All,

I have a worksheet change event that passes a range from a table to a sub that sets validation for the next column - based on the user input that triggers the change.

For Each myRange In Target
            If Not Intersect(Target, myTable.ListColumns("Agency_State").Range) Is Nothing Then
                Call ApplySuburbValidation(myRange)
                myRange.Offset(0, 1).Activate
            End If
        Next myRange

I have just discovered that this treats a cell that is in column 1 of the table as if it was in column 2 ("Agency_State"), and proceeds to apply the validation to the wrong column.
how can I correctly apply the 'if not intersect' to test each cell in the range individually?

many thanks
Werafa

[SamT]

This says that only if a cell in Agency State column changes do the work

If Not Intersect(Target, myTable.ListColumns("Agency_State").Range) Is Nothing Then

This says to activate the cell just to the right of myRange

myRange.Offset(0, 1).Activate

IMO, it is best practice to use Event Subs to only check which and where the Event occurred, then to direct the execution flow to the appropriate Sub procedure.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Calc As Long
With Application
    Calc = .Calculation
   .EnableEvents = False
   .Calculation = xlCalculationManual
End With
   
'This assumes that there are three different Events that might occur and you want to handle each differently
If Not Intersect(Target, myTable.ListColumns("Agency_State").Range) Is Nothing Then SuberbValidate Target
If Not Intersect(Target, SecondEventRange) Is Nothing Then Sub2 Target
If Not Intersect(Target, ThirdEventRange) Is Nothing Then Sub2 Target

With Application
   .Calculation = Calc
   .EnableEvents = True
End With

End Sub

Private Sub SuberbValidate(ByVal Target As Range)
Dim Cel As Range

For Each Cel In Target
   ApplySuburbValidation Cel
Next Cel
End Sub

Private Sub Sub2(ByVal Target As Range)
 'do something with SecondEventRange
End Sub

Private Sub Sub3(ByVal Target As Range)
 'do something with ThirdEventRange
End Sub

[werafa]

Thanks,

first - re the best practice, you are, of course, correct.

re the 'This says that only if a cell in Agency State column changes do the work', that is what I thought; however:

my table has three columns, Agency, Agency_State and Suburb.
the workflow is: you type in the agency name, add the state, and the code is then supposed to read the list of available suburbs into the validation list for the cell in the third column.
I selected Agency and state cells, and copied down - intending to add (an office in ) a new suburb.

the code above applied the suburb validation to the state column - meaning that what I thought is not correct - and I need to understand why I got this result and/or how to get the result I need

[p45cal]

perhaps it needs to be:
If Not Intersect(myRange, myTable.ListColumns("Agency_State").Range) Is Nothing Then
instead of:
If Not Intersect(Target, myTable.ListColumns("Agency_State").Range) Is Nothing Then
?

[werafa]

doh

what do you say when you realise that you have been a total fool.........