Running a macro for modifying dropdown lists in Pivot tables

brevika

New Member
Joined
Oct 22, 2006
Messages
5
I have a current issue trying to automize a scorecard on financial data.

I use Microsoft Query to read data into a Pivottable. My ambition is to make a VB dropdown box linked to the dropdown boxes in the pivot tables.
This would create a very user friendly and quick updating of the relevant reports.

I am not sure if it is possible, but I would appreciate constructive feedback.

I started out with a more ambitous plan of doing most of this without pivot tables, using VBA all the way, but due to time constraints and holes in skillbase this seems like a mouthful.

Thanks for your help,

Regards,

Atle M Brevik
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Atle

Not sure what you mean by VB Dropdown so here are a couple of thoughts.

Active sheet has a pivottable called pivottable1 with a pivot field called product.
1) Data Validation drop down on sheet.
the following code will set the current cell drop down list to match the pivotfield items

Code:
Set holder = ActiveSheet.PivotTables("pivottable1").PivotFields("Product").PivotItems
    For i = 1 To holder.Count
      listhold = listhold & holder(i) & ","
    Next i
    listhold = Left(listhold, Len(listhold) - 1)
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=listhold
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

2) Assuming that you have a form with a combobox, then the following form initialize code will load the combobox.

Code:
Private Sub UserForm_Initialize()
  Set holder = ActiveSheet.PivotTables("Pivottable1").PivotFields("Product").PivotItems
  For i = 1 To holder.Count
    ComboBox1.AddItem holder(i)
  Next i
  
End Sub


Hope they point you in the right direction.


Tony
 

Forum statistics

Threads
1,137,205
Messages
5,680,184
Members
419,887
Latest member
Vasokir

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