Running a macro for modifying dropdown lists in Pivot tables

[brevika]

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

 

[acw]

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

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.

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