| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
user forms and pivot tables question
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
user forms and pivot tables question
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
user forms and pivot tables question
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
user forms and pivot tables question
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
user forms and pivot tables question
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
user forms and pivot tables question
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
user forms and pivot tables question
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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
user forms and pivot tables question
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 wrote: 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" wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
user forms and pivot tables question
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" wrote: 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 wrote: 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" wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
user forms and pivot tables question
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 he http://www.contextures.com/excelfiles.html Under PivotTables, look for 'PT0014 - Filter from Worksheet Date Range' bennyob wrote: 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" wrote: 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 wrote: 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" wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
user forms and pivot tables question
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 "Debra Dalgleish" wrote: 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 he http://www.contextures.com/excelfiles.html Under PivotTables, look for 'PT0014 - Filter from Worksheet Date Range' bennyob wrote: 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" wrote: 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 wrote: 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" wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Further Question about Pivot Tables - Multiple Consolidation Range | Excel Discussion (Misc queries) | |||
| Excel 97 Question (Pivot tables) | Excel Discussion (Misc queries) | |||
| User Forms | Excel Discussion (Misc queries) | |||
| Pivot Tables - "simple" question | Excel Discussion (Misc queries) | |||
| Question about Pivot Tables..why is that | Excel Discussion (Misc queries) | |||
Linear Mode
