Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Filter 'AfterUpdate' event in Pivot Table
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Filter 'AfterUpdate' event in Pivot Table
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Filter 'AfterUpdate' event in Pivot Table
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" wrote in message ... 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Filter 'AfterUpdate' event in Pivot Table
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 "Alan Z. Scharf" wrote: 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" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
afterupdate event in Excel | Excel Programming | |||
Pivot table filter - Selected Item Event | Excel Programming | |||
PivotField AfterUpdate event? | Excel Programming | |||
Capture Pivot table filter count | Excel Programming | |||
AfterUpdate Event not Running | Excel Programming |