Capture Filter 'AfterUpdate' event in Pivot Table

[Alan Z. Scharf]

Hi,

1. I have a pivot table that shows price data vertically for 30 stock
ticker symbols, by stock symbol and date.. There is a verying number of
rows for each respepctive stock symbol.

2. A Filter dropdown box on the Stock Symbol column allows selection of all
or some of the stock symbols.

3. At the bottom of the entire table, at row 1650 when Show All is chosen,
is a TextBox with a 30-line disclaimer.

4. My goal is to have this disclaimer displayed immediately under the last
data row of the stock ticker symbol(s) selected. This is a legal
requirement.

5. PROBLEM: If I choose say only one stock and the last data row is 200,
the Disclalimer textbox is still displayed at row 1650, with 1450 blank rows
between the last data row and the disclaimer. .

6. Hence, I need a way to capture an 'AfterUpdate' event for the dropdown
box and

A. Get the last row number of the data displayed, based onn the stock
symbol(s) chosen, and
B. Move the Disclaimer textbox to that last row number + 2. It might be
easier if I put the text in a named range of cells instead of a
text box, but either way, I need to re-position the Disclaimer.


Is there a way to do this each time a different selection is made from the
filter dropdown??

Thanks for any help you can give.

Alan

 

[Barb Reinhardt]

Try adding this worksheet change event to the code for the Pivot Sheet. I'm
sure it can be cleaned up some more, but it's a starting point.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Excel.Range
Dim myRange As Excel.Range
Dim myTop As Long

Dim myShape As Excel.Shape
Set myCell = Me.Cells(Me.Rows.Count, 1).End(xlUp)
Debug.Print myCell.Address(External:=True)
myTop = 0

Set myRange = Me.Cells(1, 1)

Do While myRange.Address <> myCell.Offset(2, 0).Address
myTop = myTop + myRange.Height
Set myRange = myRange.Offset(1, 0)
Loop

For Each myShape In Me.Shapes
myShape.Top = myTop
Next myShape
End Sub

 

[Alan Z. Scharf]

Barb,

Thanks very much for your reply.

I will give it a try.

Anothehr approach I cn take is to detect when Autofilter criteria are
changed.

Can the Work_Change event detect that?

Or is there a separate event for AutoFilter criteria change?

Alan

 

[Barb Reinhardt]

As far as I know, the Worksheet_Change can't tell what caused the change, but
will just trigger on a change. You could add something like this at the
beginning

IF INTERSECT(TARGET,ME.Range("B1")) IS NOTHING THEN EXIT SUB

' After that do whatever you'd do if the target is the same as B1