Excel Pivot Table: Hide Drop-Down Arrow button from all pivot fields

Hello,
I'd like to hide Drop-Down Arrow button from all pivot fields so it resembles a regular table.  Can that be done in VBA?

Thanks in advance,
salesprodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
You will need to make a copy of the formatting and data to an alternate location. VBA something like the following will do this:

    Dim PivotTable As PivotTable
    Set PivotTable = Sheets("Sheet1").PivotTables("PivotTable1")
    PivotTable.TableRange1.Copy
    Sheets("Sheet2").[A1].PasteSpecial xlPasteValuesAndNumberFormats

You can run this code everytime the pivot table changes.

Kevin
salesprodAuthor Commented:
Thanks for the quick reply Kevin, I really need to have the controls disabled because I am dealing with a large number of pivot tables (at least 16), one table per tab.  Believe this will add more complexity and replicated tables?

Rgds,


zorvek (Kevin Jones)ConsultantCommented:
Yes, it will. But you may not have any other choice. By default a pivot table implies user control over how the data is displayed and hence the drop down menu controls. If you were somehow able to prevent those controls from displaying then it wouldn't be a pivot table.

Kevin
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

salesprodAuthor Commented:
I do understand the dynamic nature of pivots and cubes, I thank you for your input.  Will wait till someone shows me how to hide the drop-down controls.

zorvek (Kevin Jones)ConsultantCommented:
This code will not change the look of the pivot table fields but it will get rid of the drop down menu (and the arrow):

Public Sub HideWorkbookPivotTableMenus()

' Hide menus in all workbook PivotTables.

   Dim Worksheet As Worksheet

   On Error Resume Next
   For Each Worksheet In ActiveWorkbook.Worksheets
      HideWorksheetPivotTableMenus Worksheet
   Next

End Sub

Public Sub HideWorksheetPivotTableMenus(ByRef Worksheet As Worksheet)

' Hide menus in all PivotTables in a specific worksheet.

   Dim PivotTable As PivotTable

   On Error Resume Next
   For Each PivotTable In Worksheet.PivotTables
      HidePivotTableMenus PivotTable
   Next

End Sub

Public Sub HidePivotTableMenus(ByRef PivotTable As PivotTable)

' Hide menus in a specific PivotTable.

   Dim PivotField As PivotField

   On Error Resume Next
   For Each PivotField In PivotTable.VisibleFields
      PivotField.EnableItemSelection = False
   Next

End Sub

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
salesprodAuthor Commented:
Ohhhhh YEAH!!
That's exactly what I was talking about - Thank you so much.  You rock!

:)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.