worksheet change event won't fire

[ajm]

folks, using a contextures macro to let users select how many suppliers they want to see in their pivot table.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws As Worksheet
Dim wsL As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pfD As PivotField
Dim rngType As Range
Dim rngTypeSel As Range
Dim rngNum As Range
Dim lType As Long

On Error GoTo errHandler

Set ws = ActiveSheet
'Set wsL = Worksheets("Supplier by Agency")
Set pt = ws.PivotTables(1)
Set pf = pt.RowFields(1)
Set pfD = pt.DataFields(1)
Set rngType = ws.Range("TypeSel")
Set rngTypeSel _
  = ws.Range("TypeValSel")
Set rngNum = ws.Range("NumSel")

Select Case Target.Address
  Case rngType.Address, rngNum.Address
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    pf.ClearAllFilters
    If rngNum.Value > 0 And _
          rngTypeSel > 0 Then
      pf.PivotFilters.Add _
          Type:=rngTypeSel.Value, _
          DataField:=pfD, _
          Value1:=rngNum.Value
    End If
End Select

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub

errHandler:
  MsgBox "Could not apply filter"
  Resume exitHandler
End Sub

it fires if you double click after manually making a change to the target cell but not when using the drop down list. In here example, she also allows the user to select which metric they wish to see (Top things, Bottom things) whereas I just want the Top x number of items. any suggestions?[/code]

 

[Peter_SSs]

it fires if you double click after manually making a change to the target cell but not when using the drop down list.

The code that you have posted is Worksheet_SelectionChange code. It fires when a cell/range selection is made. Choosing a value from a drop-down list is not altering which cell/range is selected hence it is not firing. Looks like you want Worksheet_Change code instead.

 

[ajm]

The code that you have posted is Worksheet_SelectionChange code. It fires when a cell/range selection is made. Choosing a value from a drop-down list is not altering which cell/range is selected hence it is not firing. Looks like you want Worksheet_Change code instead.

Thank you peter. How did i miss that!?

hope you are well and safely out of harms way down there in sydney. scary times.

 

[Peter_SSs]

Thank you peter.

You're welcome.

hope you are well and safely out of harms way down there in sydney.

I actually live about half way between Sydney & Brisbane, so not so much of a problem here thankfully. ?