(VBA) OLAP Pivot Table - Possible to add an inputbox?

GSanchez

New Member
Joined
Feb 23, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Hi Everyone,

I have a workbook that contains a lot of OLAP pivot tables and so the goal for me is to create a macro that will automatically change the filter known as "month year" for all of the pivot tables in this workbook. The good news is that the macro I wrote manages to do that, but now I am trying to figure out a way to add an inputbox for other users who can type the month & year rather than going inside the VBA Code and adjusting the code.

Any help or clue is appreciated!


VBA Code:
Sub Testing_Filter_Changes()

Dim ws As Worksheet
Dim changemonth As String

changemonth = InputBox(Prompt:="Month and Year?")

For Each ws In ThisWorkbook.Worksheets

ws.PivotTables("PivotTableGPR").PivotFields( "[Calendar].[Month Year].[Month Year]").CurrentPageName = _
            "[Calendar].[Month Year].&[changemonth]"

Next ws

End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

GSanchez

New Member
Joined
Feb 23, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Perhaps:

VBA Code:
 "[Calendar].[Month Year].[" & changemonth & "]"

So close! It seems to accept whatever I type in the inputbox, but then it says

"Item could not be found in the OLAP Cube."

Which is weird because I am typing "Apr 2021" and this is definitely found in the OLAP Cube as I wrote it in the VBA code before.

Not sure why it is not accepting it from the inputbox.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Did you record the macro when filtering to check the format?

My work OLAP selection is "2008-01 January" but I need to write it as "[Actual Delivery Date].[Month].&[2008}&{1]"

Data validation may be needed if other users are typing in whatever, or at least a message telling them the format.
 

GSanchez

New Member
Joined
Feb 23, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I did record the macro when filtering, here were the results:

VBA Code:
ActiveSheet.PivotTables("PivotTableGPR").PivotFields( _

 "[Calendar].[Month Year].[Month Year]").CurrentPageName = _

 "[Calendar].[Month Year].&[Apr 2021]"
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
I think I see it, my fault there sorry.

VBA Code:
 "[Calendar].[Month Year].&[" & changemonth & "]"
 
Solution

GSanchez

New Member
Joined
Feb 23, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Thank you so much! This works!

I am definitely going to research more about this.

By any chance can you explain why [" & changemonth & "] allows the inputbox to be used?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
"making some kind of string"

=

"making some " & "kind of " & "string"

pretty much like concatenating, if that makes sense
 

GSanchez

New Member
Joined
Feb 23, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
That makes sense! I appreciate you taking the time to write that for me.

Thanks again!
 

Forum statistics

Threads
1,136,803
Messages
5,677,823
Members
419,722
Latest member
Rizzol

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top