dynamically filtered pivot table

I'm trying to make a pivot table that will dynamically hide a section o
its contents based on a boolean operator the user can set.  Becaus
this boolean is used in several places, I don't want to require th
user to manually set the visibility parameters for the pivot table.

It seems like the only way to do this effectively is to have th
booleans set by a button, and have the button not only toggle th
boolean, but also change the visibility in the pivot table.

here's the code i've tried:

Sub ToggleButton1_Click()


If ToggleButton1.Caption = "Include" Then     'Check caption, the
change it.
ToggleButton1.Caption = "Exclude"
ToggleButton1.BackColor = 4966415

Else
ToggleButton1.Caption = "Include"
ToggleButton1.BackColor = 16776960



End If

With ActiveSheet.PivotTables("PivotTable1").PivotFields("MODEL2")
'.PivotItems("20_40_60").Visible = bTFE20_40_60
.PivotItems("2").Visible = bTFE2
.PivotItems("3").Visible = bTFE3
.PivotItems("4").Visible = bTFE4
.PivotItems("5").Visible = bTFE5
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub



Unfortunately, it tells me "Unable to set the Visible property of th
PivotItem class"

That part of the function I took right out of the macro editor (
created a macro to find out the correct function calls, the contents o
the macro work fine until i copy them into this function).  I realiz
it's messy to update all the booleans every time, but i just want t
get SOMETHING to work.

I don't know what to do!! help!!

Andr

--
Message posted from http://www.ExcelForum.com

0
7/13/2004 8:47:14 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
349 Views

Similar Articles

[PageSpeed] 20

I think the problem has to do with setting the visibility of th
pivotitem class from within a button

--
Message posted from http://www.ExcelForum.com

0
7/13/2004 8:58:46 PM
To prevent the error, set the Sort for the field to Manual. You can do 
this in the code, for example:

'==================================
Sub PivotShowItemResetSort()
'For version 2000 -- show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
'returns sort order to previous setting
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim intASO As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
     For Each pf In pt.VisibleFields
       intASO = pf.AutoSortOrder
       pf.AutoSort xlManual, pf.SourceName
        For Each pi In pf.PivotItems
          If pi.Visible <> True Then
            pi.Visible = True
          End If
        Next pi
       pf.AutoSort intASO, pf.SourceName
      Next pf
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'================================


mrsckum < wrote:
> I'm trying to make a pivot table that will dynamically hide a section of
> its contents based on a boolean operator the user can set.  Because
> this boolean is used in several places, I don't want to require the
> user to manually set the visibility parameters for the pivot table.
> 
> It seems like the only way to do this effectively is to have the
> booleans set by a button, and have the button not only toggle the
> boolean, but also change the visibility in the pivot table.
> 
> here's the code i've tried:
> 
> Sub ToggleButton1_Click()
> 
> 
> If ToggleButton1.Caption = "Include" Then     'Check caption, then
> change it.
> ToggleButton1.Caption = "Exclude"
> ToggleButton1.BackColor = 4966415
> 
> Else
> ToggleButton1.Caption = "Include"
> ToggleButton1.BackColor = 16776960
> 
> 
> 
> End If
> 
> With ActiveSheet.PivotTables("PivotTable1").PivotFields("MODEL2")
> '.PivotItems("20_40_60").Visible = bTFE20_40_60
> PivotItems("2").Visible = bTFE2
> PivotItems("3").Visible = bTFE3
> PivotItems("4").Visible = bTFE4
> PivotItems("5").Visible = bTFE5
> End With
> End Sub
> 
> Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
> 
> End Sub
> 
> 
> 
> Unfortunately, it tells me "Unable to set the Visible property of the
> PivotItem class"
> 
> That part of the function I took right out of the macro editor (i
> created a macro to find out the correct function calls, the contents of
> the macro work fine until i copy them into this function).  I realize
> it's messy to update all the booleans every time, but i just want to
> get SOMETHING to work.
> 
> I don't know what to do!! help!!
> 
> Andre
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
7/13/2004 9:32:55 PM
Well, the table already is set to sort manually.  I've been scourin
around the net today, and it looks like it has something to do with th
scope of the function.

Let me simplify the problem even further...


Public Sub ToggleButton_TFE2_Click()
'for the TFE -2 this will toggle the named field "bTFE2"

ToggleButton_TFE2.Caption = ToggleButton_TFE2.Value


ActiveSheet.PivotTables("PivotTable1").PivotFields("MODEL2").PivotItems("TFE2").Visibl
= Range("bTFE2").Value

End Sub


That SHOULD change the visibility of the corresponding TFE2 pivot ite
in the pivot table to match the value in the range.  It keeps tellin
me that it's unable to set the visible property of the pivotitem class
which is bunk!

What's really strange is that if i allow it to break when it fails
manually typing in TRUE or FALSE in the linked cell (and therefor
manually changing the value of bTFE2) DOES change the visibility in th
pivot table!!!

I'm flummoxed!

Andr

--
Message posted from http://www.ExcelForum.com

0
7/13/2004 10:58:07 PM
Reply:

Similar Artilces:

Filtered Form Query
I have a form that is filtered using Allen Browne's ajbFindAsUType module (http://allenbrowne.com/AppFindAsUType.html). I want to now create a query that utilizes only the filtered records as its source in order to perform a computation. Is there a way to pass the filtered Recordset into a query via a command button? You can use the RecordsetClone of the form to step through the records. You may be able to pass the Filter from the form on to whatever other operation you need to perform. For example, you could create a report that summarizes the data, and open it like ...

populating an Access table from a text file
A text file has data in a format like the below: Title1 Cell 1 data Title 2 Cell 2 data Title 3 Cell 3 data .. .. .. Title 10 Cell 10 data Title 11 Cell 11 data .. .. .. Title 15 Cell 15 data .. .. .. Title N Cell N data I guess I can read this using some VBA code posted at http://www.mvps.org/access/modules/mdl0057.htm and http://www.granite.ab.ca/access/readtextfile.htm, add an Instr function to it so that when I read the text file, I know when to insert the data properly in the table. 1. Is there another way I can directly import the data into an Access 2...

Running Excel Pivot Table with Local cube on http:// Sub-Web???
Hello I have a SQL 2000 local cube that I created on my computer that is accessed by an Excel (XP) pivot table workbook. I would like to place & run both on a secure sub-web on my company http:// website. Both files were FTP'ed to the sub-web successfully. When opening the Excel file I am prompted for the local cube: the prompt is referencing the cube location on my computer where it was originally created with SQL 2000. I am unable to point it to the sub-web location. It appears that the Excel workbook has hard coded the original cube location back to my computer. Is there a way ...

How to filter lower case characters in an edit control?
Hi to all, I created a class that enherits from CEdit and overrode its OnChar() message handler to intercept the entered keys and filter only those that are lower case. It also verifies if an entered key is upper case to convert it to lower case and filter it. Everything works good except the conversion from upper to lower case. The problem is that even if the OnChar() handler does convert the character to lower case, the character is still displayed in the edit box as it was entered, i.e. in upper case. Please note that if I remove the line: "nChar = tolower(nChar);", the u...

Pivot Charts
I noticed that you are limited to the amount of editing you can do to a pivot chart such as sizing it differently. Is there a way to allow me to size it, like you would in a regular Excel chart?? Thanks [This followup was posted to microsoft.public.excel.charting with an email copy to David M.. Please use the newsgroup for further discussion.] Not directly, no. PivotCharts are limited in their formatting capability. However, if you create a regular chart from the PivotTable data, you will have more control over the chart. Of course, to do so requires (a) the PT be formatted with...

Converting to an efficient set of tables
I was handed down an Excel file consisting of 47,000 rows and 20+ columns of data. In each column, there is an enormous amount of repetition. The file is over 20 MB and runs slowly. Because of reasons beyond my control at work, I cannot use MS Access to handle this data. So, I would like to create several tables containing the unique values that repeat so frequently and essentially structure the data like I would in Access with various relationships between fields. How can I (at least somewhat) automate this task to replace the cell values with references to another cell on another s...

Form
Hi, I am in the process of building an image database. I am almost done with my database. I included a field in a form which is not in my parent table. Now I need to include it in the table. Can somebody explain me how to do it? Thank you verymuch! I assume that the form is bounded aleady to the main table. 1. Open the table in design view and add the field you want 2. Open the form in design view 3. Open the form properties, by clicking on the top left corner of the form 4. Put the cursor in the RecordSource property of the form, on the right you'll see a button with three dots, cli...

simple HTML table generator
Greetings: I need a simple HTML table generator. I'm not proficient in VBA so can't write one from scratch myself. Microsoft provide one for use with excel97, but it no longer functions on newer versions. I need a "bare bones" table generator (i.e. no CSS) that will take a named range and convert it to a HTML table. The only formating other than <TR> and <TD> I want is cell background and font colors, period. Does anyone have such a function, or know where I can get one? Thanks, Scott ...

is there a way to enter data interactively and dynamically?
Hi all, Can I enter data interactively and dynamically? To give an example: Approach 1: Currently, when I enter grades for students, I have to find his/her name first, after finding that row, I have to scroll the row to the far right, since there are so many columns already there. Let's suppose that I scroll to column 32, and it is often times hard to remain on the same row(then I ended up entering a wrong score for this student ...) ; suppose I scrolled successfully with my eyes staring at that row, and then I enter score. And so on and so forth for the next student, etc. The wh...

displaying a table on a form so that we can insert a new record at any place rather that at the end
how can i display the table on a form so that the user is able to insert a new record not only in the last, but also in any place of his desire. he must be able to insert a new record even before the first record. how can i accomplish this? can any body help me out please........ hi Ravindar, ravindar thati wrote: > how can i display the table on a form so that the user is able to > insert a new record not only in the last, but also in any place of his > desire. he must be able to insert a new record even before the first > record. Tables in Access (in a database) are a differen...

Filter mode #2
Hello All I have a worksheet with autofilter set to on. In the bar just above the taskbar, on the far left, I notice that it says "Filter mode", and if I right-click on that I can select options Average, Count, etc. BUT: whichever option I select seems to have no effect - it always says "Filter mode"! Is there another setting that needs to be set somewhere? Hope someone can help. Many thanks Leslie Isaacs When your list has many formulas, or if a cell in the list is changed after an AutoFilter has been applied, the Status Bar may show "Filter Mode" inst...

Client-Side Junk E-Mail Filtering
We are using Intelligent Message Filtering with Exchange 2003 SP2. We have a user where e-mail sent to a distribution list hidden from the GAL is being moved to his Junk E-Mail folder by Outlook 2003 SP3 client-side filtering. The e-mail is not being moved if cached-mode is disabled. I added the distribution list address to the user’s Safe Recipients list of their Outlook. Does the distribution list have to be visible in the GAL for client-side filtering to function properly or am I missing something else? Thank you Is this mail entirely internal or does it come from outsi...

Strage function in worksheet {=TABLE(I64,)}
I am attempting to understand what appears to be a custom function in a workbook I was given, author unknown. It seems to be some type of index or look up but their is no help on it or macro defined for it. has anyone used or familiar with this? If you need more information I would be happy to provide it. {=TABLE(I64,)} Thanks, Mark On Thu, 26 Feb 2004 19:30:25 -0600, "mkadon" <mkadon@hotmail.com> wrote: >I am attempting to understand what appears to be a custom function in a >workbook I was given, author unknown. It seems to be some type of index or >look up...

Resource Assignments View Filtered on Booked Type Field
I have a project that has been cancelled part way through. To deal with this, I've set the Booked Type field to be proposed for all resources in this project. This works great in that the tasks from this project no longer appear in the resources My Tasks view but they do still appear in the Resource Assignments views I have and I don't seem to be able to filter Resource Assignnments on Booked Type. Is that possible or is there another way I should be going about what I want to achieve? Which is to keep a copy of the cancelled projects plan in Project Server for refere...

Automated values in a table
I have a table which inculde two fileds. Field 1 called "Injection" is a Yes/No box. Field 2 called "#of Injections" is a number column. I want to have an automated function, where if "No" is selected in "Injection", "# of Injections" automatically fills in a value of "0". How do i do this in a table? Thankyou! Hi What if you just made the default value of the second field "0" that way you wouldn't have to worry about it if its " No" and if is "Yes" you would have to edit it anyways with a qu...

how do i show text in the data field of a pivot table?
If you put a text field into the data area of a pivot table, it will summarize the field to show a count, instead of showing the text. You can place the text fields in the Row, Column or Page areas, where their text will show. pum75 wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Old vb6 / mdb app with "Could not delete from specified tables" er
hi all, I am moving an old vb6 application from a W2K to an XP machine. If I run the app from the XP box and with the .mdb file also on the XP box, I get the above error. If I run the app from the XP box but with the .mdb file on the W2K box, I do not get the error. Any idea where I could start with this one? Thanks for any help! The error is "Could not delete from specified tables" Andy "AndyK" wrote: > hi all, I am moving an old vb6 application from a W2K to an XP machine. > > If I run the app from the XP box and with the .mdb...

tables #2
which tables should be deleted for posted trx in all modules. (financial, ap, ar,inventory, fixed assets, sop, pop). thank you .. ...

Pivot Table Chart Legend
Hello all. I am currently developng a Line chart via a pivot table. One thing I have noticed is that the legend runs in ascending order. E.g. Z at the top down to A's at the bottom. Is there any way of getting the legend "flipped" so it runs A-Z without amending the pivot table itself?? Thank you. Hi, Double check the chart type. A flipped legend would suggest a Stacked Line chart rather than a plain Line chart. Cheers Andy Chiccada wrote: > Hello all. > > I am currently developng a Line chart via a pivot table. One thing I have > noticed is that th...

Structured Table Refs in VBA
I'm confused about the relationship between tables (using structured references) and ranges. I can work on individual cells from a table column, similar to working with a range, using- Dim oSh As Worksheet Set oSh = ActiveSheet Dim r As Range For Each r In oSh.Range("MyRange[ColumnName]") r.Value = 10 Next But if I try to create a range object from a table, using any of- r = oSh.Range("MyRange") r = oSh.Range("MyRange[#Data]") r = oSh.Range("MyRange[ rowvariable, [ColumnName]]") I think I am fai...

Exporting from Smartlist to Dynamic Excel Spreadsheet
Hi, I have a smartlist in GP V10.0 that shows me Purchase Order line items that are outstanding with our suppliers. I currently export to Excel to track shipments as they arrive. My question is "How do I export the smartlist as a Dynamic Excel Spreadsheet so that it automatically updates each time I open it rather than my current manual update?" Thanks. Wayne The optimal solution is to create an excel sheet that has your Company database as the datasource to get updated records when needed. Regards, -- Mohammad R. Daoud MVP, MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 6...

Auto Filter and Grouping
Is there a way for Auto Filter of columns to work when I'm using Groups as well? If not are there similar options? Thanks, Dan Foxley I used Data|Subtotal to create my groups (not data|group). And autofilter worked ok on it. The =subtotal() formulas do get confused by rows hidden by filter or by the outlining symbols. When I shared workbooks with others, I would only use one or the other--not both. It seemed to make life a little easier. If this doesn't help, you may want to explain what doesn't work when you try it. Dan Foxley wrote: > > Is there a way for Auto...

Outlook 2003
I am a delegate receiving meeting requests for several people. In my Inbox, there is no way to tell which meeting requests are really for me (i.e. I am invited to the meeting) and which meeting requests I am receiving on behalf of others (I am not invited to the meeting but may accept or decline on behalf of the person whose delegate I am). I have created sub-folders for each of the managers whose meeting requests I receive. I would like meeting requests I receive on behalf of others to be filtered into the appropriate sub-folder, bypassing my Inbox. I have made many attempts ...

Clear all pivot table fields with a Macro
I have a two things I would like to do, ideally with a macro 1. Clear all fields from a pivot table 2. If any of the fields have been filtered - remove the filters I am hoping there is a simple command or two that I have not yet discovered. To put things into perspective: I have multiple pivot tables (all copies of the original table, and each on their own spreadsheet). My plan is to have each table set up for particular queries (that is with different fields and filters). I will then examine the data on each, and sometimes alter the fields as needed. But when I am done I want to press a b...

combining data from several worksheets in a pivot table
I have a workbook with data for sales there is a separate sheet for each month. Every time an agent makes a sale he records it using the date he processed the sale. the sheet then calculates the week number i want to produce a pivot table showing sales by agent for each week however as weeks cross between 2 months i need to be able to include all 12 sheets in the source data for pivot table can this be done. any ideas please Yes - by using "Multiple consolidation ranges" when setting up the table. In my experience this brings more problems than it is worth - because we do not g...