user forms and pivot tables question

[bennyob]

Hello
I would like to have a drop down list in a user form to select a value, in
my case a month, that will select the matching value from the manual filter
(on the column labels) or the report filter options in a pivot table.

Any help would be greatly appreciated
cheers ben

My pivot table looks a little like this:
Sum of TOTAL $ month DATE
January February
Category 12/01/2007 19/01/2007 20/01/2007 21/01/2007 2/02/2007 3/02/2007
Apartment rent 1500 1500
Assistant salary 580 580
Bank Commission 22 55
Car petrol 33 23.53

 

[Roger Govier]

Hi

Take a look at the GetPivotData function.
You can find more information on this and its application at Debra
Dalgleish's site
http://www.contextures.com/xlPivot06.html

 

[bennyob]

Thanks for the link, not what I am looking for but I sure can use this
function elsewhere.

What I am trying looks like this, but I have a bug in the 'pi.Visible =
True" line:

Sub Macro1()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strMon As String

Application.ScreenUpdating = False

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("month")
strMon = ActiveSheet.Range("A30").Value

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

pf.CurrentPage = strMon

For Each pi In pf.PivotItems
If pi.Month = strMon Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi

Application.ScreenUpdating = True

End Sub

cheers Ben

 

[Debra Dalgleish]

It would be easier to help if you mention what the error is, but you
could try setting the sort for the field to manual sort, instead of
automatic.

To manually change the setting, double-click on the field button, and
click Advanced. Under AutoSort, choose Ascending or Descending.

 

[bennyob]

It gives me:

Run-time error '1004':
Unable to set the Visible property of the PivotItem class

This is the code I would like for one application if I can get it to work,
however I would also live to have a drop down box in a user form to select an
item each from 2 different fields, using the items available from the pivot
table in the user form.

Incidently the contextures website is brilliant!

Cheers Ben

 

[Debra Dalgleish]

Try setting the field to manual sort, as I suggested, and that should
fix the Visible property error.
Why are you creating a user form? Could you just let users select from
the pivot table's page fields?

Thanks for letting me know that you like the site. It has some sample
files that might give you ideas for your code:

http://www.contextures.com/excelfiles.html

Under PivotTables, look for 'PT0003 - Change Page Field' and 'PT0013 -
Filter From Worksheet Selection'

 

[bennyob]

No go, got a different error message on that line though!

Run-Time error '438':
Object doesnt support this property or method

Set to manual sort didnt work.

I am working in Azerbaijan and the end users are less excel litterate than
me, so i need simple instructions to automatically generate reports on
invoices. Would be easier if they just bought Quicken!

Thanks for your help

Cheers Ben

 

[Debra Dalgleish]

Well, if the visible property message disappeared, then the manual sort
worked for that part of the problem.

Could you record the code as you manually change the page field, or
filter the column fields? That might give you sample code that you coul
incorporate into your procedure.
From your code, it's not clear to me what you're trying to do.

 

[bennyob]

I have gone throughthe contextures site and found that the Pivot Show
Specific Items code is close to want i want.

Is it possible to substitute the InputBox command with a list box or combo
box? I can create those but my programming skills isnt good enough to to get
this to trigger!

I have a pivot table with data by years, months, weeks and days. I would
like to have a user form where the user selects the a year item, ie 2007, the
month, ie march and the week to filter my pivot table from a list, maybe it
can be a button instead of a dropdown list?

Unlike the Pivot Show Specific Items code it is not necessary for user to
input the field, however I found I could sucessfully alter this part of the
code.

Cheers Ben

 

[Debra Dalgleish]

Instead of all those dropdown lists, how about letting the user enter
start and end dates, then show records from that range in the pivot
table? I've added a sample file here:

http://www.contextures.com/excelfiles.html

Under PivotTables, look for 'PT0014 - Filter from Worksheet Date Range'

 

[bennyob]

Thanks Debra
That worked well, a very simple solution indeed.
I was able to use data validation for the drop down lists i wanted in the
start date end date box, to select month, select week box
Your help much appreciated!
Cheers Ben