VBA USERFORMS : Combobox values into Pivot Chart table

[sachinns]

Hi Friends,

I have a User form with a comboBox caled "cmbpivotvalue" . When i press Submit/validate button "cmbValidate" in the userform , the values in this comboBox should go to the "VALUES" section on the PivotChartFields (please refer the screenshot) .

Please help me to create code for this.

Thanks in Advance.

 

[Domenic]

The following code assumes that Sheet1 contains the pivot table, and that the pivot table is named PivotTable1. Change these names accordingly. Note that the code will first remove any existing data fields from the pivot table, and then add the pivot field the user selected from the combo box.

Private Sub cmbValidate_Click()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pfName As String
    pfName = Me.cmbpivotvalue.Value
    If Len(pfName) = 0 Then
        MsgBox "Please enter a pivot field value, and try again!", vbExclamation
        Exit Sub
    End If
    Set pt = Worksheets("Sheet1").PivotTables("PivotTable1")
    For Each pf In pt.DataFields
        pf.Orientation = xlHidden
    Next pf
    On Error Resume Next
    Set pf = pt.PivotFields(pfName)
    On Error GoTo 0
    If pf Is Nothing Then
        MsgBox "Pivot field not found!", vbExclamation
    Else
        pf.Orientation = xlDataField
    End If
End Sub

Hope this helps!

P.S. No screenshot was posted.