• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Parameters With Multiple Value Check Box

Need Help!!!

I need to add two parameters at Run tab.
1. MediaType
2. ContractTitle

Both parameters should be dynamic and should be able to pick "ALL" or multiple values.

So when we pick mediatype and contracttitle from the parameters and refresh data, it should update data accordingly in the digital_combined_data_view and digital_contract_title tab.
 

Attachments

  • BankTracker.xlsm
    849.5 KB · Views: 3
Creating a dynamic parameterized query in VBA for Excel involves using parameters to filter data. Here's an example VBA code that prompts the user to input values for MediaType and ContractTitle parameters and then refreshes data accordingly:

>>> use code - tags <<<
Code:
```vba
Sub UpdateDataWithParameters()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim mediaType As String
    Dim contractTitle As String

    ' Set your worksheet
    Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace with your sheet name

    ' Assuming you have a PivotTable named "PivotTable1"
    Set pt = ws.PivotTables("PivotTable1") ' Replace with your PivotTable name

    ' Prompt user for MediaType
    mediaType = InputBox("Enter MediaType (type 'ALL' for all):")
    If mediaType = "" Then Exit Sub ' User canceled

    ' Prompt user for ContractTitle
    contractTitle = InputBox("Enter ContractTitle (type 'ALL' for all):")
    If contractTitle = "" Then Exit Sub ' User canceled

    ' Clear existing filters
    For Each pf In pt.PivotFields
        pf.ClearAllFilters
    Next pf

    ' Apply filters based on user input
    Set pf = pt.PivotFields("MediaType") ' Replace with your actual field name
    If UCase(mediaType) <> "ALL" Then
        For Each pi In pf.PivotItems
            pi.Visible = (InStr(1, pi.Name, mediaType, vbTextCompare) > 0)
        Next pi
    End If

    Set pf = pt.PivotFields("ContractTitle") ' Replace with your actual field name
    If UCase(contractTitle) <> "ALL" Then
        For Each pi In pf.PivotItems
            pi.Visible = (InStr(1, pi.Name, contractTitle, vbTextCompare) > 0)
        Next pi
    End If

    ' Refresh the PivotTable
    pt.RefreshTable
End Sub
```

Make sure to replace "YourSheetName" with the actual name of your sheet and "PivotTable1" with the name of your PivotTable. Adjust the field names accordingly.

This code uses InputBox to prompt the user for MediaType and ContractTitle. It then filters the PivotTable based on these parameters and refreshes the table.
 
Last edited by a moderator:
Back
Top