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!

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

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

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!

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

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

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