Newbee help
Hi,
What am I doing wrong... I have Pivot Table with all data and then I want a chart do display this, everything is fine, but now I have to buttons one for select and one for unselect, Unselect works fine, but select does not.... Unselect macro: Sub Reset_BB() For i = 1 To ActiveChart.PivotLayout.PivotFields("BB").PivotIte ms.Count - 1 With ActiveChart.PivotLayout.PivotFields("BB") .PivotItems(i).Visible = False End With Next End Sub Select macro: Sub Set_BB() MsgBox "Diese Funktion ist leider noch nicht implementiert." Exit Sub Sheets("Grafik nach BB").Select For i = 1 To ActiveChart.PivotLayout.PivotFields("BB").PivotIte ms.Count - 1 With ActiveChart.PivotLayout.PivotFields("BB") .PivotItems(i).Visible = True End With Next End Sub Thanx /goran |
Newbee help
According to Debra Dalgleish,
to make pivot items visible, you need to set the sort property for the field to manual. Here is one of her postings on the topic with sample code. It captures the current sort setting, sets the sort to manual, makes all items visible and set the sort option back to its original setting. Debra Dalgleish http://groups.google.com/groups?selm...&output=gplain Message-ID: Date: Sat, 16 Aug 2003 07:32:01 -0400 From: Debra Dalgleish User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0 X-Accept-Language: en-us, en MIME-Version: 1.0 Subject: changing sort for pivot table fields References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Newsgroups: microsoft.public.excel.programming NNTP-Posting-Host: hse-sudbury-ppp329931.sympatico.ca 64.231.156.34 Lines: 1 The following code will capture and reset the sort order for each field: '======================================== 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 '======================================== -- Regards, Tom Ogilvy "Goran" wrote in message ... Hi, What am I doing wrong... I have Pivot Table with all data and then I want a chart do display this, everything is fine, but now I have to buttons one for select and one for unselect, Unselect works fine, but select does not.... Unselect macro: Sub Reset_BB() For i = 1 To ActiveChart.PivotLayout.PivotFields("BB").PivotIte ms.Count - 1 With ActiveChart.PivotLayout.PivotFields("BB") .PivotItems(i).Visible = False End With Next End Sub Select macro: Sub Set_BB() MsgBox "Diese Funktion ist leider noch nicht implementiert." Exit Sub Sheets("Grafik nach BB").Select For i = 1 To ActiveChart.PivotLayout.PivotFields("BB").PivotIte ms.Count - 1 With ActiveChart.PivotLayout.PivotFields("BB") .PivotItems(i).Visible = True End With Next End Sub Thanx /goran |
Newbee help
hello tom,
thanx for help this works just fine.... cheers /goran "Tom Ogilvy" wrote in message ... According to Debra Dalgleish, to make pivot items visible, you need to set the sort property for the field to manual. Here is one of her postings on the topic with sample code. It captures the current sort setting, sets the sort to manual, makes all items visible and set the sort option back to its original setting. Debra Dalgleish http://groups.google.com/groups?selm...&output=gplain Message-ID: Date: Sat, 16 Aug 2003 07:32:01 -0400 From: Debra Dalgleish User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0 X-Accept-Language: en-us, en MIME-Version: 1.0 Subject: changing sort for pivot table fields References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Newsgroups: microsoft.public.excel.programming NNTP-Posting-Host: hse-sudbury-ppp329931.sympatico.ca 64.231.156.34 Lines: 1 The following code will capture and reset the sort order for each field: '======================================== 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 '======================================== -- Regards, Tom Ogilvy "Goran" wrote in message ... Hi, What am I doing wrong... I have Pivot Table with all data and then I want a chart do display this, everything is fine, but now I have to buttons one for select and one for unselect, Unselect works fine, but select does not.... Unselect macro: Sub Reset_BB() For i = 1 To ActiveChart.PivotLayout.PivotFields("BB").PivotIte ms.Count - 1 With ActiveChart.PivotLayout.PivotFields("BB") .PivotItems(i).Visible = False End With Next End Sub Select macro: Sub Set_BB() MsgBox "Diese Funktion ist leider noch nicht implementiert." Exit Sub Sheets("Grafik nach BB").Select For i = 1 To ActiveChart.PivotLayout.PivotFields("BB").PivotIte ms.Count - 1 With ActiveChart.PivotLayout.PivotFields("BB") .PivotItems(i).Visible = True End With Next End Sub Thanx /goran |
All times are GMT +1. The time now is 02:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com