Excel - Multiple Pivot Table Updates from a Single ListBox

kaldrich,

How about of going to this site, it might help.

http://support.microsoft.com/support/tshoot/pivot97.asp

knowme, thanks for the direction, but the site doesn't seem to address my question.  Seems to be useful for other things, though.

kaldrich,
I have a quick solution for you:
a) Select the first listbox then right click and choose FormatControl/Control and see which cell address the cell link has. If your engineer used active X controls then switch to edit mode.
b) Select the second, right click again etc and define the cell link as the one of the first
c) Repeat for 3th and 4th.
d) Perhaps you reduce the size of the slaves.
Now all are interconnected, changing the input in one will set the other to the same entry number, so attention double check that they have the exact same order, otherwise you will wreck the logic of the spreasheet.

Hope this helps. If it does not work for pivot tables, reject my answer right away, I do not use them.
\cri

I'm striking out, CRI.  The problem is I do not have a Format Control option on my right-click (I tried all 4 list boxes).  The closest I get is the Format Cells option at the top of the menu.  I've also tried bringing up the Control Toolbox and putting things into Design mode: Same problem.  Got any other rabbits up your sleeve?  I sure could use them!  Thanks.

kaldrich,
This is strange that you do not have the FormatControl. The only way I can reproduce this is locking the listbox and protecting the worksheet, but then you can not change the selection anymore, you certainly would have noticed that before asking. This leaves four possibilities:

a) This workbook was made in an old version of Excel, probably 4.0. Open it in your Excel version, make a _Save As_ Test(.xls) and make sure that you have Excel Workbook as File Format. Perhaps the listbox are accessible then, but it is not unlikely that the whole workbook does not work right away, you might need to adapt the last 1-2%...

b) As I do not use the Control Toolbox, the linking of the cells is done elsewhere. I will look it put, but probably someone else will come first.

c) The workbook or your Excel setup is damaged. To determine which make two listboxes in a new workbook and interconnect them.

d) Your engineer defined the listboxes in VBA as insert form. Press Alt+F11 and have a look.

Hope this helps. More later.
\cri

Add-on to b) of previous post: If your engineer used the Control Toolbox then you still have a Format Control when right clicking in Design Mode. However the 'LinkedCell' is defined under Properties. And my method still works.

You can do it easy with VBA. Assume your sheets with PivotTables have names "one" and "two", and your data filter is at B1 cell. Press Alt-F11, and in Worksheet_calculate procedure add this code (for worksheet "one" - refer to sheet "ywo" and visa verse)
Private Sub Worksheet_Calculate()
 If Worksheets("two").Range("B1").Value <> Range("B1").Value Then
   Worksheets("two").Range("B1").Value = Range("B1").Value
 End If
End Sub
That's all. When you change filter on any sheet, it will be reflected at the another one.
You can easy use this approach for any numbers of PivotTables.
Hope it helps.

Just letting you know I am monitoring, CRI & vboukhar.  Will try both methods today at some point (maybe this evening) and let you know what I find.

CRI, FYI, the workbook is Excel 97.  I'm sure it was created in Excel 4, but I have done the conversion.  I did my tests at home; will confirm the workbook behaves the same way here at work.

Thanks for your help, folks.  I'll get back to you by tomorrow.

Okay, here is an update: I was imprecise in my terms.  When you create a pivot table, you can create a drop-down box by dragging one of the items to the left of the screen.  It allows a cell to behave like a listbox, but really the value of the cell is what is fed in.

Vboukhar, (and don't anyone else feel excluded from answering) because of this I tried a variant of your suggestion.  I created a macro that sets all of the cells equal to a single cell.  I've also created a true ActiveX listbox.  Since I haven't done this in Excel 97 and the procedures have changed, my question changes to:

1. (Like the initial question): is there a way to associate multiple pivot tables with
    the same screening cell?

2. If not, how do I tie a listbox to a cell and run a macro from it when I click on a
    selection?  I have the listbox and the macro; I just need to know how to tie
    them together.

Sure appreciate your help.  Thanks.

kaldrich,
Can not answer it right now because no real experience with pivot tables. However, I was advised by an colleague that is a programmer to not use ActiveX controls, therefore, if only on hearsay, I recommend to use the listboxes from 'Forms'.
\cri

Thanks, CRI.  Will do, & make changes.

Others who are monitoring this, I still can use info.  Thanks.

kaldrich!
It seems you misunderstood me.
My sample is what you need - insert address of your cell, that "behave like a listbox, but really the value of the cell is what is fed in." into Range("..") object in my code (for every sheet). Then every time, when you choose another item in this listbox (cell), value in linked listbox (cell) will be changed and your PivotTable will be recalculated.
Sorry, if my explanation isn't clear - English isn't my native language.

Hi, kaldrich!
Assume you have Workbook, where your pivot tables are in first sheets (1 and 2). Open VBA editor and in Thisworkbook general section add code (below), then run LinkTables - it creates VBA macroses for your sheets with PivotTables, so when you choose another item in listbox, it reflects on another PivotTable.

Sub LinkTables()
 fRange = Chr(34) & ThisWorkbook.Worksheets(1).PivotTables(1).PageRangeCells.Cells(1, 2).Address & Chr(34)
 sRange = Chr(34) & ThisWorkbook.Worksheets(2).PivotTables(1).PageRangeCells.Cells(1, 2).Address & Chr(34)
 tmpStr = "Sub Worksheet_Calculate()" & Chr(10)
 tmpStr = tmpStr & "If Worksheets(2).Range(" & sRange & ").Value <> Range(" & fRange & ").Value Then" & Chr(10)
 tmpStr = tmpStr & "Worksheets(2).Range(" & sRange & ").Value = Range(" & fRange & ").Value" & Chr(10)
 tmpStr = tmpStr & "End if" & Chr(10)
 tmpStr = tmpStr & "End Sub"
 ThisWorkbook.VBProject.VBComponents(2).CodeModule.AddFromString tmpStr
 tmpStr = "Sub Worksheet_Calculate()" & Chr(10)
 tmpStr = tmpStr & "If Worksheets(1).Range(" & sRange & ").Value <> Range(" & fRange & ").Value Then" & Chr(10)
 tmpStr = tmpStr & "Worksheets(1).Range(" & sRange & ").Value = Range(" & fRange & ").Value" & Chr(10)
 tmpStr = tmpStr & "End if" & Chr(10)
 tmpStr = tmpStr & "End Sub"
 ThisWorkbook.VBProject.VBComponents(3).CodeModule.AddFromString tmpStr
End Sub

Whew, Vboukhar!  It isn't your english that is the problem, it's my Excel VBA (grin!)  
Okay, reading through your code, it looks like you are building a macr on the fly (which you said), which is WAY outside of my experience.  I've copied your code over and am blissfully ignorant of what it does, other than create the text for the macro.  My questions:

1. All of my pivot tables are now on the same sheet ("Summary").  I assume I substitute that for the 1 & 2 in "Worksheets(1)  . . ."  and "Worksheets(2) . . " 

2. There are 4 pivot tables updating simultaneously.  Do I put in 3rdRange and 4thRange using the same syntax as fRange and sRange?

3. .PivotTables(1) . . .  How do I identify the specific pivot table to which I am referring?

4. Do I have to load this macro on startup using an Autoexec?

I'll up the points if this works; you've been doing a lot of work on it.  Sure do appreciate your help.

kaldrish!
If all of your PivotTables are on the same sheet - just open VBA editor, open Worksheet("Summary") _Calculate and copy-paste code:
Option Explicit
Public CommonValue
Private Sub Worksheet_Calculate()
 Dim i As Integer
 Application.EnableEvents = False
 For i = 1 To ActiveSheet.PivotTables.Count
   If ActiveSheet.PivotTables(i).PageFields(1).DataRange.Value <> CommonValue Then
     CommonValue = ActiveSheet.PivotTables(i).PageFields(1).DataRange.Value
     Exit For
   End If
 Next i
 For i = 1 To ActiveSheet.PivotTables.Count
   If ActiveSheet.PivotTables(i).PageFields(1).DataRange.Value <> CommonValue Then
     ActiveSheet.PivotTables(i).PageFields(1).DataRange.Value = CommonValue
   End If
 Next i
 Application.EnableEvents = True
End Sub

It doesn't matter how much PivotTables do you have.

Vboukhar, I've added your code and am getting nothing, probably because I can't see how to run it.  Do I do it as an Autoexec?  Or is there some way to tie it to the dropdown created in the Page function of the Pivot Table?  Please advise; thanks.

When you open this your file, do you check box "Allow macroses"?
You din't need to do it as auto_open etc. - it's Worksheet_Calculate() function, that fires on every recalcilation of sheet. When you change value in your dropdown, PivotTable has to be recalculated, so  this function will run too. Check, do you insert this code in Worksheet_Calculate() for proper worksheet (that contains your PivotTables)? And don't you have now two Worksheet_Calculate functions in this sheet module?

Well, We're getting better; at least I have an error message that shows the code working.

This is what I am getting: I copied the code into the Worksheet "Summary" as well as to the "ThisWorkbook".  I saved it, closed it, and reopened it.  Now, when I try changing the dropdown, starting with the value of "DA" in both dropdowns, changing it to "DDS", I get the error "No Item of this name exists in the pivot table.  Rename 'DDS' to "DA       '?"  I click okay, and it changes the value, but does not update the second pivot table.  I can then go into the second pivot table, select the value, and it updates, but we need to lose the prompt and the lack of updating.

More information: What appears to be happening is the macro adds an option to the dropdown which is the item with a space in front of it; e. g., "DA" becomes " DA".  When I go into the second dropdown and select the correct option (without the leading space), the offending option disappears.  I'll look in the code to see if something is being introduced with a space where it shouldn't be, but any recommendations will be appreciated.  Thanks for all the work; we're almost there.

     

Kaldrich!
I think, you'd better remove code from ThidWorkbook section of VBA project. About your  dropdowns - do you mean, that they are filled from code (add items)? And items in this dropdowns could differ - is it possible to choose in one dropdown item, that absent in another? If yes (even if difference in  blanks etc), it'll be hard to link these dropdowns-PivotTables (and in any case, code will be very sensitive to contents of dropdowns!)
If you data isn't too confidentional, could you send me sample of you sheet with PivotTables (and VBA code, if it fills dropdowns) My e-mail is in my profile.
Maybe it'll be simplest way to end up with this question :-)

Hi, Kevin!
I've got you file - it's really very strange - PivotTables have same data range, but dropdowns are filled with different items! I never see it before - maybe this abnormal behaviour is resalt of conversation from previous format?..
In any case, my new propositions:
At first, remove all previous version of code in ThisWorkbook and Worksheet("Summary") sections of VBA project.
Then open Worksheet("Summary") section  and copy-paste  this code:
Private Sub Worksheet_Activate()
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_Calculate()
    Dim i As Integer, j As Integer
    Dim Fld1 As PivotField, Fld2 As PivotField
    Static CommonValue
    Application.EnableEvents = False
    If IsEmpty(CommonValue) Then
      CommonValue = ActiveSheet.PivotTables(1).PageFields(1).DataRange.Value
    End If
    Set Fld1 = ActiveSheet.PivotTables(1).PageFields(1)
    Set Fld2 = ActiveSheet.PivotTables(2).PageFields(1)
    If Trim(Fld1.DataRange.Value) <> CommonValue Then
      CommonValue = Trim(Fld1.DataRange.Value)
    Else
      CommonValue = Trim(Fld2.DataRange.Value)
    End If
    For j = 1 To Fld1.PivotItems.Count
       If InStr(Fld1.PivotItems(j).Name, CommonValue) > 0 Then
          Fld1.DataRange.Value = Fld1.PivotItems(j).Name
          Exit For
       End If
    Next j
    For j = 1 To Fld2.PivotItems.Count
       If InStr(Fld2.PivotItems(j).Name, CommonValue) > 0 Then
          Fld2.DataRange.Value = Fld2.PivotItems(j).Name
          Exit For
       End If
    Next j
    Set Fld1 = Nothing
    Set Fld2 = Nothing
    Application.EnableEvents = True
End Sub

Some description:
I add code to Worksheet_Activate() - to avoid "getting nothing" if main macros (_Calculate) fails. _Calculate turns off Events processing, so if macros stopped somewhere in a middle, events don't invoke correspondent procedures, and _Calculate next time as well).
Changes in main procedure:
- I used Static variable instead of Public (more convenient);
- in CommonValue stored value without any blanks (Trim);
- every dropdown is searched for CommonValue in it (InStr instead of complete match)
It works fine at my PC.

Good luck!

It's me again.
I explored your file, so below is my comments:
- your second PivotTable are built on named range "all3new". This range includes empty columns (from AT to CU columns) - it makes Excel feel bad to process your PivotTable - try to Update data (right-click and choose Update). I guess, you need to change "all3new" named range to "$A$!:$AT$1484" and refresh both tables - you'll get messages about "PivotTables was changed!", but now your dropdowns will contains only the same items! and both version of my code will works well!
In any case, in current state of your file second dropdown haven't DDS item and I sure Summary sheet contains false information in Pivot tables (not updated), so I think you have to make proposed changes anyway.
And check every field in your pivot tables - do you reaaly need in that excludings etc? Maybe best solution IMHO is to rebuild both PivotTables...

P.S. Add in my code this lines after  Static CommonValue:

    If StrComp(ActiveSheet.Name, "Summary") <> 0 Then
       Exit Sub
    End If

- when you make some changes in all3new range, it invokes recalculation of pivottable, bit active sheet isn't Summary, so VBA error occures.

vboukhar, got your message.  I may have to add this info tonight at home (I'm going on vacation soon for a while.)  I hope to test it before I leave; Do me a favor and submit an answer to this question so we can lock it.  Then, even if I don't get the chance to answer it before I leave, I can do so on vacation to give you the points.  Thanks.

GREETINGS!

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.

https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange