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

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

--
Regards

Roger Govier


"bennyob" wrote in message
...
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

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

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"bennyob" wrote in message
...
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

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 wrote:
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

"Roger Govier" wrote:


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

--
Regards

Roger Govier


"bennyob" wrote in message
...

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







--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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" wrote:

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 wrote:
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

"Roger Govier" wrote:


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

--
Regards

Roger Govier


"bennyob" wrote in message
...

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







--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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 wrote:
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" wrote:


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 wrote:

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

"Roger Govier" wrote:



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

--
Regards

Roger Govier


"bennyob" wrote in message
...


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






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html