Events in Pivot Table

[Vandermiet]

Hi,

I've been searching high and low but found nothing. I am struggling to implement the following idea:

I am using a pivot table built on a fairly simple select query to show the pricing and sales history of some products in a given period of time. The X axis represents the time (catalogue number) and the Y axis lists the products. The values of the table are some consolidated figures.

I'd like to create a macro which would read the catalogue number (columns) and product ID (rows) from a pivot table. Out of that I will generate a link to a site where I can see the layout of the catalogue page where the product was placed (this part is not a problem).

I have two questions:

1 - is it possible to read the column and row category value into a variable using VBA? My idea was to select any cell within the pivot table being an intersection of a row and column and trigger the macro getting a result similar to: varCatno = 201001 and varProdID = 123. If it's possible - how to achieve it?

2 - are there Events (or anything similar) available in Pivot Table - ideally my macro would be triggered by a double-click. I suppose the answer to this is negative, but a workaround solution would be to launch the macro by a hot-key, so this is a secondary issue.

Any help will be aprreciated!

Bests,

Lucas

 

[tehNellie]

1) I'd perhaps be placing the contents of the Pivot/crosstab query into a Listview, that gives you a "clickable" interface where the user can select the row that they want to see and allows you to retrieve values from columns in that row to pass elsewhere.

2) For the query itself, no, but most controls (such as a listview) have a double click event associated with it. so you could build your code within that event. i.e double clicking on the row that you're interested in the listview would then trigger the necessary code to extract the needed information and do stuff with it.

 

[Vandermiet]

Thank for your reply. I never used the ListView before, but will definitely look into that. If I have any serious trouble I'll be probably back with questions

 

[Vandermiet]

Me again

I've reading and testing this idea but I cannot go over one issue. It seems to me that you can select a given row from the listview, but not a given column. Below the idea in "list-view-language"

- List view filled with crosstab
- User double-clicks a given "cell" inside the listview, let's say row(4) x column(3)
- Two variables are fed with data: varProdID = product_id (value from row(4) column(0) ) and varCatid = header_column(3)

Is this achievable? Of course the row and column number is dynamic, so we cannot hard-code it by ListView.SelectedItem.SubItems as the n is changing.

 

[mistera]

Did you ever find a way to do this? I have a similar situation in that I have a sub-form that is a pivot table and I want to change info displayed in the main form based on the user "selecting" or clicking a cell in the pivot table.

Basically, all I need is to capture the row/column info from the pivot table at the intersection that is clicked/selected in the detail data section.