PDA

View Full Version : Worksheet Change event not triggering sub



Aussiebear
05-16-2010, 04:40 AM
I had hoped that the following code will call a sub to bring into effect an advanced filter of a Employees Qualification matrix. However it fails because it claims the "Call Filter_Crews" line of code is not defined.

Cell B2 on the Crew Allocation sheet is a drop down list comprising the values A, B, C, D, All.

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Crew Allocations")
If Intersect(Target, Range("B2")) Is Nothing Then
Exit Sub
Else
With Worksheets("Qualifications")
Call Filter_Crews
End With
End If
End With
End Sub


The following code resides on the worksheet "Qualifications"and it meant to advance filter the Employees Authorisations Table.

Sub Filter_Crews()
Dim LastRow As Long, cel As Range

With Worksheets("Qualifications")
For Each cel In .Range("A3:O3")
If Len(cel) = 0 Then cel.ClearContents
Next
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

.Range("A6").Resize(LastRow - 5, 14).AdvancedFilter , Action:=xlFilterInPlace, _
CriteriaRange:=.Range("A2:O3")
End With

End Sub

mdmackillop
05-16-2010, 04:50 AM
Put Filter_Crews in a Standard Sub
You can simplify the Event code as the ranges can only be on that sheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
Call Filter_Crews
End Sub

Bob Phillips
05-16-2010, 05:15 AM
Why not just embed it, hardlky seems worth a separate sub for that code



Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long, cel As Range

If Not Intersect(Target, Me.Range("B2")) Is Nothing Then

With Me

For Each cel In .Range("A3:O3")

If Len(cel) = 0 Then cel.ClearContents
Next

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("A6").Resize(LastRow - 5, 14).AdvancedFilter , Action:=xlFilterInPlace, _
CriteriaRange:=.Range("A2:O3")
End With
End If
End Sub

Aussiebear
05-16-2010, 05:37 AM
@MD Sorry it continue to deny me. Fails at the line Call Filter_Crews, with the error msg of "Not defined"

Aussiebear
05-16-2010, 05:44 AM
Somewhere recently I thought I read something which suggested that a change in a dropdown box would not create a changed cell event..I think it was on Chip pearson's website.


The Value of the drop down in cell B2 (Crew Allocations worksheet) is copied to the Criteria range A3:O3 ( actually cell B3) on the Qualifications wo. Calling the sub to trigger the advanced filter is meant to enable seeing only those members of the correct crew

Simon Lloyd
05-16-2010, 06:36 AM
Ted, i use this kind of code for running a macro with a Data Validation using the string from the dropdown, will this help you work around what you are trying to do?.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyCode As String
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
If Target.Address = "$A$1" Then
MyCode = Target
Run MyCode
End If
End Sub