• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do you call the Insert PivotTable dialog box from VBA

jeffreyweir

jeffreyweir

Active Member
Greetings, hive mind. Anyone know how you bring up the Create PivotTable dialog box in Excel? I can get the old wizard via application.Dialogs(xlDialogpivottablewizard).Show but I can't see where to get the new dialog box.
 
application.Dialogs(xlDialogpivottablewizard).Show brings up the old style wizard. I was trying to bring up the new style one, but I don't think you can.

I'll just punch up a userform. A userform will work better in any case, because I want to add some other options to it, and let the user store their default settings in the registry.

Thanks Narayan. You getting excited yet 'bout heading down my way? Bring some sunscreen - the sun is brutal at the moment! But nice and hot, for New Zealand.
 
Hi SirJB7. What I was wanting to do was actually show the dialog box as part of a routine, so that users could manually enter some info re the PivotTable, and then after they'd done that my code could do something else.

Basically I wanted to use the Insert PivotTable dialog box to capture some information from the user.

Whereas the link above shows how to create a Pivot Table using the PivotTableWizard Method and some supplied parameters.

To make it clearer, type this in the VBA immediate pane:
application.Dialogs(xlDialogpivottablewizard).Show
You'll see that this brings up the PivotTable and PivotChart wizard, and you can actually use this to create a pivottable.

I was looking for some code that would similarly launch the Insert PivotTable dialog box.
 
Hi, jeffreyweir!
I yet tried the PT wizard and I couldn't find out how to display the actual PT dialog. Will keep on searching and come back if anything found.
Regards!
 
Is this what you are after:
Code: 
Application.CommandBars.ExecuteMso "PivotTableInsert"

However, I'd imagine it will be hard to control this as it will not return results.
 
Yeah, that's it. How did you track it down?

You're right - it probably isn't going to help me unless I get users to create the table and then read the properties of the newly created pivot and change what I need to.
 
You must log in or register to reply here.
Back
Top