Pivot table function from a variable

[heideway]

Hi I'm trying to use the value of a variable that I get from a ComboBox to define the function that I want a PivotTable to display. I get the "type mismatch" error, but the watch shows that the value matches what I selected from the drop-down. I know about enough VBA for Excel to get myself into trouble - which I clearly have and I would appreciate some guidance on how to correct.
Thanks in advance, Pete

 

[Alex Blakenburg]

It looks to me that the enum xlCount is being used as a string.
Replace you df.Function = line with the below, and if that works the fact it was a string was the issue.
Someone might be able to give you a better way to convert the enum as a string to its value. The normal value conversion didn't work.

    Dim strEnum As String
    strEnum = frmPivotOptions.cbxOperation.Value

    Select Case strEnum
        Case "xlSum"
            df.Function = xlSum
        Case "xlCount"
            df.Function = xlCount
        Case "xlAverage"
            df.Function = xlAverage
    End Select

 

[heideway]

Hi Alex,

Thanks for your feedback, but I get the same error, even though I thought that you approach would work. Something to do with the string value that df.Function does not like.

Regards,
Pete

 

[Alex Blakenburg]

Sorry I will have to have a look tomorrow.

 

[RoryA]

I'd use a two column combobox with the actual numeric value stored in the second column.

 

[Alex Blakenburg]

If for some reason Rory's suggestion doesn work for you. The select case method worked for me.
Perhaps try adding the debug.print line to see if the values are true and/or F8 through the code to see it performs the right case statement.

Sub PivotFunctionChange()

    Dim pt As PivotTable
    Dim df As PivotField
    Dim sFunction As String
    
    'sFunction = "xlCount"                                       ' My test value worked
    sFunction = frmPivotOptions.cbxOperation.Value
    Debug.print frmPivotOptions.cbxOperation.Value = "xlCount" ' Maybe try this - s/be true

    For Each pt In ActiveSheet.PivotTables
        For Each df In pt.DataFields
            Select Case sFunction
                Case "xlSum"
                    df.Function = xlSum
                Case "xlCount"
                    df.Function = xlCount
                Case "xlAverage"
                    df.Function = xlAverage
            End Select
        Next df
    Next pt

End Sub

 

[heideway]

Thanks Alex,

Perfect - works great.

Really appreciate the work you guys do with helping us dummies.

Regards,
Pete

 

[Alex Blakenburg]

Thanks for letting us know and we appreciate the feedback. Glad we could help.