Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel - Multiple Pivot Table Updates from a Single ListBox

Posted on 1999-07-30
25
Medium Priority
?
618 Views
Last Modified: 2012-06-27
I have inherited an Excel spreadsheet from an engineer that has 4 pivot tables in it going against the same data set (same worksheet).  Each has a drop-down listbox that is the same for each table.  For the sheet to work correctly, you have to go and set each listbox to the same value separately.

I'd like to either run all of these off the same listbox, or run a macro that updates them all simultaneously.  Am looking forward to your recommendations; thanks.
0
Comment
Question by:kaldrich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 4
  • +2
25 Comments
 
LVL 3

Expert Comment

by:knowme
ID: 1610397
kaldrich,

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

http://support.microsoft.com/support/tshoot/pivot97.asp
0
 
LVL 3

Author Comment

by:kaldrich
ID: 1610398
knowme, thanks for the direction, but the site doesn't seem to address my question.  Seems to be useful for other things, though.
0
 
LVL 13

Expert Comment

by:cri
ID: 1610399
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
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 3

Author Comment

by:kaldrich
ID: 1610400
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.
0
 
LVL 13

Expert Comment

by:cri
ID: 1610401
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
0
 
LVL 13

Expert Comment

by:cri
ID: 1610402
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.
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1610403
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.

0
 
LVL 3

Author Comment

by:kaldrich
ID: 1610404
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.

0
 
LVL 3

Author Comment

by:kaldrich
ID: 1610405
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.
0
 
LVL 13

Expert Comment

by:cri
ID: 1610406
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
0
 
LVL 3

Author Comment

by:kaldrich
ID: 1610407
Thanks, CRI.  Will do, & make changes.

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


0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1610408
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.
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1610409
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
0
 
LVL 3

Author Comment

by:kaldrich
ID: 1610410
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.
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1610411
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.
0
 
LVL 3

Author Comment

by:kaldrich
ID: 1610412
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.
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1610413
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?
0
 
LVL 3

Author Comment

by:kaldrich
ID: 1610414
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.

     
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1610415
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 :-)
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1610416
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!

0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1610417
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...
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1610418
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.
0
 
LVL 3

Author Comment

by:kaldrich
ID: 1610419
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.
0
 
LVL 5

Accepted Solution

by:
vboukhar earned 600 total points
ID: 1610420
Thanks!
Below is sample of VBA code, that links two PageField dropdowns of two pivotTables on the same worksheet (for PAQ readers):
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

Kevin, happy vacation to you! And I'll be on-line waiting to help you in further improvement of VBA code to achieve best performance :-)
Good luck!
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6872264
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.

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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question