Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > pivot managment through VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 23
Default pivot managment through VBA
hi all
could someone give me a hand with this piece of code which I can't get to
work for me:
indPlan = Sheets("pivot").Range("L46").Value
Select Case indPlan
Case indPlan <= 3
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = False
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = False
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = False
Case indPlan = 4 & indPlan <= 6
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = False
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = False
Case indPlan = 7 & indPlan <= 9
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = False
Case indPlan = 10 & indPlan <= 12
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = True
End Select
thanks a lot in advance!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 119
Default pivot managment through VBA
What is it doing? I think pivotitems might be 0 based rather than 1 based.
But why not use the names instead?

tptPlanMonths.PivotFields("quarter").PivotItems("1 ").Visible =
True
tptPlanMonths.PivotFields("quarter").PivotItems("2 ").Visible =
False
or
tptPlanMonths.PivotFields("quarter").PivotItems("1 ST").Visible
= True
tptPlanMonths.PivotFields("quarter").PivotItems("2 ND").Visible
= False


whatever the values are for the field.
"catrrmg" wrote:

hi all
could someone give me a hand with this piece of code which I can't get to
work for me:
indPlan = Sheets("pivot").Range("L46").Value
Select Case indPlan
Case indPlan <= 3
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = False
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = False
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = False
Case indPlan = 4 & indPlan <= 6
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = False
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = False
Case indPlan = 7 & indPlan <= 9
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = False
Case indPlan = 10 & indPlan <= 12
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = True
End Select
thanks a lot in advance!
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 23
Default pivot managment through VBA
hi
the idea is whenever a user selects an item (in this case annual quarters)
from a drop down list, the result through Sheets("pivot").Range("L46").Value
is processed by the code, which in case the user selects let's say 2 should
show only the first two quarters and so on... unfortunatelly the case
statement I'm using just goes through the code to the end sub not finding any
result to match... your help will be much appreciated...

"barnabel" wrote:

What is it doing? I think pivotitems might be 0 based rather than 1 based.
But why not use the names instead?

tptPlanMonths.PivotFields("quarter").PivotItems("1 ").Visible =
True
tptPlanMonths.PivotFields("quarter").PivotItems("2 ").Visible =
False
or
tptPlanMonths.PivotFields("quarter").PivotItems("1 ST").Visible
= True
tptPlanMonths.PivotFields("quarter").PivotItems("2 ND").Visible
= False


whatever the values are for the field.
"catrrmg" wrote:

hi all
could someone give me a hand with this piece of code which I can't get to
work for me:
indPlan = Sheets("pivot").Range("L46").Value
Select Case indPlan
Case indPlan <= 3
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = False
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = False
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = False
Case indPlan = 4 & indPlan <= 6
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = False
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = False
Case indPlan = 7 & indPlan <= 9
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = False
Case indPlan = 10 & indPlan <= 12
tptPlanMonths.PivotFields("quarter").PivotItems(1) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(2) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(3) .Visible = True
tptPlanMonths.PivotFields("quarter").PivotItems(4) .Visible = True
End Select
thanks a lot in advance!
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Capability / Resource Managment Paul Browning Excel Discussion (Misc queries) 0 March 12th 09 12:03 PM
Yardi Property Managment Program Deborah Excel Discussion (Misc queries) 2 August 8th 08 11:22 PM
Information Right Managment Julian Glass Excel Programming 0 November 6th 06 08:53 AM
ODBC managment with VB Excel Jean-Pierre Bidon Excel Programming 4 May 17th 06 10:27 AM
contact managment util Excel Discussion (Misc queries) 0 May 5th 05 10:12 PM


All times are GMT +1. The time now is 12:49 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement