VBA script doesn't run

[JayJay6]

Hi,

I have a pretty stupid problem.

I am not that familiar with VBA scripts in Excel, and my problem is I have a Script which simply doesn't run. If I try to run the subs, I get a pop-up asking for a "Macro Name" ? The script debugs with no errors. I have made other scripts which runs without problems i the same workbook.

These are the subs:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Application.Range("RegionFilterRange")) Is Nothing Then
        UpdatePivotFieldFromRange "RegionFilterRange", "Col0", "PivotTable1"
    End If
End Sub
 

Public Sub UpdatePivotFieldFromRange(RangeName As String, FieldName As String, PivotTableName As String)
    Dim rng As Range
    Set rng = Application.Range("RegionFilterRange")
    Dim pt As PivotTable
    Dim Sheet As Worksheet
    For Each Sheet In Application.ActiveWorkbook.Worksheets
        On Error Resume Next
        Set pt = Sheet.PivotTables("PivotTable1")
    Next
    If pt Is Nothing Then GoTo Ex
    On Error GoTo Ex
    pt.ManualUpdate = True
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim Field As PivotField
    Set Field = pt.PivotFields("Col0")
    Field.ClearAllFilters
    Field.EnableItemSelection = False
    SelectPivotItem Field, rng.Text
    pt.RefreshTable
    Ex:
    pt.ManualUpdate = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
 
Public Sub SelectPivotItem(Field As PivotField, ItemName As String)
    Dim Item As PivotItem
    For Each Item In Field.PivotItems
        Item.Visible = (Item.Caption = ItemName)
    Next
End Sub

Any ideas ?

Br,
Jakob

[danfleetwood]

Which part does not run? Can you post an example workbook please?

-Dan

[JayJay6]

Hi Danfleetwood,

Nothing happens when I push "run sub" ? So I guess the short answer is: all !

I have now attached an example with the script.

Br,
Jakob

[danfleetwood]

What should happen?

[Bob Phillips]

Your procedure signature should be

Private Sub Worksheet_Change(ByVal Target As Range)

not

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

as you have it in the sheet code module.

[JayJay6]

Purpose: the code enables me to enter an item name in the RegionFilterRange named range and use that value to filter my PivotTable.

Public Sub UpdatePivotFieldFromRange:
Update the Region PivotField object with the value of the cell ("RegionFilterRange") (yellow cell).

Private Sub Workbook_SheetChange:
Detect when the user has entered a value into the cell "RegionFilterRange".

Public Sub SelectPivotItem:
SelectPivotItem loops through all the items in the field and sets each one's Visible property to False except for the item specified by ItemName which is set to True. This ensures that only ItemName will be visible in the field.

br,
Jakob

[JayJay6]

Hi xld,

Great thanks. Now it works.
Now we are at it: Do you by any chance know how to make the cell value a "wildcard" ? in the script ?

br,
Jakob

[Bob Phillips]

Explain that a bit more Jakob.

[JayJay6]

The script works fine as long as I write the exact name of the region (fx.: "DK"). But would it be possible to just write "K" in the cell ("RegionFilterRange"), and filter/get all the regions containing "K". I.e. result: "DK","UK", etc.

[Bob Phillips]

This should do it (note, I have added an option to reset the filter by inputting '(All)'). You have to add the wildcard, like *K or D*.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Application.Range("RegionFilterRange")) Is Nothing Then
        UpdatePivotFieldFromRange _
        "RegionFilterRange", "Region", "PivotTable2"
    End If
End Sub

Public Sub UpdatePivotFieldFromRange( _
    ByVal RangeName As String, _
    ByVal FieldName As String, _
    ByVal PivotTableName As String)
    Dim Sheet As Worksheet
    Dim pt As PivotTable
    Dim rng As Range
    Dim vecItems As Variant
    Set rng = Application.Range("RegionFilterRange")
    For Each Sheet In Application.ActiveWorkbook.Worksheets
        On Error Resume Next
        Set pt = Sheet.PivotTables("PivotTable2")
    Next
    On Error GoTo Ex
    If Not pt Is Nothing Then
        pt.ManualUpdate = True
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim Field As PivotField
        Set Field = pt.PivotFields("Region")
        Field.ClearAllFilters
        Field.EnableItemSelection = False
        If rng.Text = "(All)" Then
            Call ResetAllItems(pt, "Region")
        Else
            vecItems = GetItems(Worksheets("Sheet1").Range("A2:A20"), rng.Text)
            Call SelectPivotItem(Field, vecItems)
        End If
        pt.RefreshTable
    End If
    Ex:
    pt.ManualUpdate = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Private Function GetItems( ByVal LookupList As Range, ByVal LookupValue As Variant) As Variant
    Dim vecItems As Variant
    Dim cell As Range
    Dim cntItems As Long
    ReDim vecItems(1 To Application.CountA(LookupList))
    For Each cell In LookupList.Cells
        If cell.Text Like LookupValue Then
            cntItems = cntItems + 1
            vecItems(cntItems) = cell.Text
        End If
    Next cell
    ReDim Preserve vecItems(1 To cntItems)
    GetItems = vecItems
End Function

Private Function ResetAllItems( _
    ByRef pt As PivotTable, _
    ByVal ItemName As String) As Boolean
    Dim Item As PivotItem
    With pt
        For Each Item In .PivotFields(ItemName).PivotItems
            Item.Visible = True
        Next Item
    End With
End Function

Private Sub SelectPivotItem(Field As PivotField, ItemNames As Variant)
    Dim Item As PivotItem
    For Each Item In Field.PivotItems
        Item.Visible = Not (IsError(Application.Match(Item.Caption, ItemNames, 0)))
    Next
End Sub

[JayJay6]

xld.....you are the best !!!!!

This is perfect. thanks!

Br,
Jakob

[Bob Phillips]

Do you have Excel 2010 Jakob? If so, you could add slicers.