Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table Code
I have a pivot table that help me analyze a big amount of data. I examine
several scenarios that depends on the selections that I make in the page fields. I have to select different combinations in the page fields to produce the scenarios that I want. I want to simplify the process for other users. I want to use a combo box that commands the page fields combinations that I desire. The combo box should have a list of scenarios = scenario 1, scenario 2, etc. The selection will change more than one page field in order to provide the needed information. How can I do this? I though about using the combo box to run several macros. The problem is that I don't know how to make the combo box run the different macros. I know how to do it with Option bottons for example. Please help! |
#2
|
|||
|
|||
I created a pivottable on Sheet2.
I added a combobox onto Sheet2. I put this code behind the worksheet: Option Explicit Private Sub ComboBox1_Change() If Me.ComboBox1.ListIndex < 0 Then Exit Sub End If Select Case LCase(Me.ComboBox1.Value) Case Is = "scenario 1" With Me.PivotTables("pivottable1") .PageFields("Name1").CurrentPage = "asdf1" .PageFields("name2").CurrentPage = "qwer1" End With Case Is = "scenario 2" With Me.PivotTables("pivottable1") .PageFields("Name1").CurrentPage = "asdf2" .PageFields("name2").CurrentPage = "qwer3" End With End Select End Sub You'll have to modify the names of the page fields and the values you want for the currentpage. I put this behind the ThisWorkbook module--to populate that combobox when the workbook is opened: Option Explicit Private Sub Workbook_Open() With Worksheets("Sheet2").ComboBox1 .Clear .AddItem "Scenario 1" .AddItem "Scenario 2" End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm FA wrote: I have a pivot table that help me analyze a big amount of data. I examine several scenarios that depends on the selections that I make in the page fields. I have to select different combinations in the page fields to produce the scenarios that I want. I want to simplify the process for other users. I want to use a combo box that commands the page fields combinations that I desire. The combo box should have a list of scenarios = scenario 1, scenario 2, etc. The selection will change more than one page field in order to provide the needed information. How can I do this? I though about using the combo box to run several macros. The problem is that I don't know how to make the combo box run the different macros. I know how to do it with Option bottons for example. Please help! -- Dave Peterson |
#3
|
|||
|
|||
That combobox on sheet2 was from the Control Toolbox toolbar--not the Forms
toolbar. Dave Peterson wrote: I created a pivottable on Sheet2. I added a combobox onto Sheet2. I put this code behind the worksheet: Option Explicit Private Sub ComboBox1_Change() If Me.ComboBox1.ListIndex < 0 Then Exit Sub End If Select Case LCase(Me.ComboBox1.Value) Case Is = "scenario 1" With Me.PivotTables("pivottable1") .PageFields("Name1").CurrentPage = "asdf1" .PageFields("name2").CurrentPage = "qwer1" End With Case Is = "scenario 2" With Me.PivotTables("pivottable1") .PageFields("Name1").CurrentPage = "asdf2" .PageFields("name2").CurrentPage = "qwer3" End With End Select End Sub You'll have to modify the names of the page fields and the values you want for the currentpage. I put this behind the ThisWorkbook module--to populate that combobox when the workbook is opened: Option Explicit Private Sub Workbook_Open() With Worksheets("Sheet2").ComboBox1 .Clear .AddItem "Scenario 1" .AddItem "Scenario 2" End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm FA wrote: I have a pivot table that help me analyze a big amount of data. I examine several scenarios that depends on the selections that I make in the page fields. I have to select different combinations in the page fields to produce the scenarios that I want. I want to simplify the process for other users. I want to use a combo box that commands the page fields combinations that I desire. The combo box should have a list of scenarios = scenario 1, scenario 2, etc. The selection will change more than one page field in order to provide the needed information. How can I do this? I though about using the combo box to run several macros. The problem is that I don't know how to make the combo box run the different macros. I know how to do it with Option bottons for example. Please help! -- Dave Peterson -- Dave Peterson |
#4
|
|||
|
|||
I can't make it work. The combobox is from the Control toolbox. But it is not
populated when opened. This is what I have: Option Explicit Private Sub ComboBox1_Change() If Me.ComboBox1.ListIndex < 0 Then Exit Sub End If Select Case LCase(Me.ComboBox1.Value) Case Is = "OPH" With Me.PivotTables("MDlist") ..PageFields("Terr GT").CurrentPage = "(All)" ..PageFields("Terr AA").CurrentPage = "(All)" ..PageFields("MSR Name").CurrentPage = "(All)" ..PageFields("Sales Group").CurrentPage = "OPH" End With Case Is = "Angelica" With Me.PivotTables("MDList") ..PageFields("Terr GT").CurrentPage = "Angelica" ..PageFields("Terr AA").CurrentPage = "(All)" ..PageFields("MSR Name").CurrentPage = "(All)" ..PageFields("Sales Group").CurrentPage = "OPH" End With End Select End Sub Option Explicit Private Sub Workbook_open() With Worksheets("Rxs Profile").ComboBox1 ..Clear ..AddItem "OPH" ..AddItem "Angelica" End With End Sub Any suggestions? "FA" wrote: I have a pivot table that help me analyze a big amount of data. I examine several scenarios that depends on the selections that I make in the page fields. I have to select different combinations in the page fields to produce the scenarios that I want. I want to simplify the process for other users. I want to use a combo box that commands the page fields combinations that I desire. The combo box should have a list of scenarios = scenario 1, scenario 2, etc. The selection will change more than one page field in order to provide the needed information. How can I do this? I though about using the combo box to run several macros. The problem is that I don't know how to make the combo box run the different macros. I know how to do it with Option bottons for example. Please help! |
#5
|
|||
|
|||
Maybe it's a simple change....
This line: Select Case LCase(Me.ComboBox1.Value) Says you want compare lower case text. So this will never work: Case Is = "OPH" But this might: Case Is = "oph" Same with: Case Is = "Angelica" to Case Is = "angelica" if you're really worried about future typing, you could use: Case Is = lcase("OPH") and Case Is = lcase("Angelica") FA wrote: I can't make it work. The combobox is from the Control toolbox. But it is not populated when opened. This is what I have: Option Explicit Private Sub ComboBox1_Change() If Me.ComboBox1.ListIndex < 0 Then Exit Sub End If Select Case LCase(Me.ComboBox1.Value) Case Is = "OPH" With Me.PivotTables("MDlist") .PageFields("Terr GT").CurrentPage = "(All)" .PageFields("Terr AA").CurrentPage = "(All)" .PageFields("MSR Name").CurrentPage = "(All)" .PageFields("Sales Group").CurrentPage = "OPH" End With Case Is = "Angelica" With Me.PivotTables("MDList") .PageFields("Terr GT").CurrentPage = "Angelica" .PageFields("Terr AA").CurrentPage = "(All)" .PageFields("MSR Name").CurrentPage = "(All)" .PageFields("Sales Group").CurrentPage = "OPH" End With End Select End Sub Option Explicit Private Sub Workbook_open() With Worksheets("Rxs Profile").ComboBox1 .Clear .AddItem "OPH" .AddItem "Angelica" End With End Sub Any suggestions? "FA" wrote: I have a pivot table that help me analyze a big amount of data. I examine several scenarios that depends on the selections that I make in the page fields. I have to select different combinations in the page fields to produce the scenarios that I want. I want to simplify the process for other users. I want to use a combo box that commands the page fields combinations that I desire. The combo box should have a list of scenarios = scenario 1, scenario 2, etc. The selection will change more than one page field in order to provide the needed information. How can I do this? I though about using the combo box to run several macros. The problem is that I don't know how to make the combo box run the different macros. I know how to do it with Option bottons for example. Please help! -- Dave Peterson |
#6
|
|||
|
|||
The Combo box is empty. I understood the code was going to populate the
combobox. Is that correct? How can I make this work? The cases are correct. Any other suggestions? "Dave Peterson" wrote: Maybe it's a simple change.... This line: Select Case LCase(Me.ComboBox1.Value) Says you want compare lower case text. So this will never work: Case Is = "OPH" But this might: Case Is = "oph" Same with: Case Is = "Angelica" to Case Is = "angelica" if you're really worried about future typing, you could use: Case Is = lcase("OPH") and Case Is = lcase("Angelica") FA wrote: I can't make it work. The combobox is from the Control toolbox. But it is not populated when opened. This is what I have: Option Explicit Private Sub ComboBox1_Change() If Me.ComboBox1.ListIndex < 0 Then Exit Sub End If Select Case LCase(Me.ComboBox1.Value) Case Is = "OPH" With Me.PivotTables("MDlist") .PageFields("Terr GT").CurrentPage = "(All)" .PageFields("Terr AA").CurrentPage = "(All)" .PageFields("MSR Name").CurrentPage = "(All)" .PageFields("Sales Group").CurrentPage = "OPH" End With Case Is = "Angelica" With Me.PivotTables("MDList") .PageFields("Terr GT").CurrentPage = "Angelica" .PageFields("Terr AA").CurrentPage = "(All)" .PageFields("MSR Name").CurrentPage = "(All)" .PageFields("Sales Group").CurrentPage = "OPH" End With End Select End Sub Option Explicit Private Sub Workbook_open() With Worksheets("Rxs Profile").ComboBox1 .Clear .AddItem "OPH" .AddItem "Angelica" End With End Sub Any suggestions? "FA" wrote: I have a pivot table that help me analyze a big amount of data. I examine several scenarios that depends on the selections that I make in the page fields. I have to select different combinations in the page fields to produce the scenarios that I want. I want to simplify the process for other users. I want to use a combo box that commands the page fields combinations that I desire. The combo box should have a list of scenarios = scenario 1, scenario 2, etc. The selection will change more than one page field in order to provide the needed information. How can I do this? I though about using the combo box to run several macros. The problem is that I don't know how to make the combo box run the different macros. I know how to do it with Option bottons for example. Please help! -- Dave Peterson |
#7
|
|||
|
|||
It worked with lcase. I selected the list through properties and now is
working. Thank you very much! "FA" wrote: The Combo box is empty. I understood the code was going to populate the combobox. Is that correct? How can I make this work? The cases are correct. Any other suggestions? "Dave Peterson" wrote: Maybe it's a simple change.... This line: Select Case LCase(Me.ComboBox1.Value) Says you want compare lower case text. So this will never work: Case Is = "OPH" But this might: Case Is = "oph" Same with: Case Is = "Angelica" to Case Is = "angelica" if you're really worried about future typing, you could use: Case Is = lcase("OPH") and Case Is = lcase("Angelica") FA wrote: I can't make it work. The combobox is from the Control toolbox. But it is not populated when opened. This is what I have: Option Explicit Private Sub ComboBox1_Change() If Me.ComboBox1.ListIndex < 0 Then Exit Sub End If Select Case LCase(Me.ComboBox1.Value) Case Is = "OPH" With Me.PivotTables("MDlist") .PageFields("Terr GT").CurrentPage = "(All)" .PageFields("Terr AA").CurrentPage = "(All)" .PageFields("MSR Name").CurrentPage = "(All)" .PageFields("Sales Group").CurrentPage = "OPH" End With Case Is = "Angelica" With Me.PivotTables("MDList") .PageFields("Terr GT").CurrentPage = "Angelica" .PageFields("Terr AA").CurrentPage = "(All)" .PageFields("MSR Name").CurrentPage = "(All)" .PageFields("Sales Group").CurrentPage = "OPH" End With End Select End Sub Option Explicit Private Sub Workbook_open() With Worksheets("Rxs Profile").ComboBox1 .Clear .AddItem "OPH" .AddItem "Angelica" End With End Sub Any suggestions? "FA" wrote: I have a pivot table that help me analyze a big amount of data. I examine several scenarios that depends on the selections that I make in the page fields. I have to select different combinations in the page fields to produce the scenarios that I want. I want to simplify the process for other users. I want to use a combo box that commands the page fields combinations that I desire. The combo box should have a list of scenarios = scenario 1, scenario 2, etc. The selection will change more than one page field in order to provide the needed information. How can I do this? I though about using the combo box to run several macros. The problem is that I don't know how to make the combo box run the different macros. I know how to do it with Option bottons for example. Please help! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table version issue | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
pivot table | Excel Worksheet Functions | |||
Pivot Table services | Excel Discussion (Misc queries) | |||
combining 3 columns of same info for a pivot table | Excel Worksheet Functions |