CODEWITHSUNDEEP
excelvba
user3305327
user3305327

Reputation: 907

how to run a macro while clicking on a value in slicer in excel vba

I have a Slicer in my excel with the following 2 values

Add Customer Add Vendor

I want to open 2 different forms when click on those value. Can anyone please help me with this.

Upvotes: 1

Views: 44063

Answers (3)

Lokesh Kumar
Lokesh Kumar

Reputation: 82

It does not work on main slicer, however if you select any particular item it works. In that case it is better to set the macro on the slicer itselft. You just need to click on slicer and it will be run automatically.

Upvotes: 0

Juan Pablo Díaz Pardo
Juan Pablo Díaz Pardo

Reputation: 21

The solution works BUT THE CODE MUST BE in the code module of the worksheet with the pivot table

Upvotes: 1

Paresh J
Paresh J

Reputation: 2419

Yes, you can call different forms on slicer clicks, check the below code. Hope it suits your requirement and if not you can use to change as per your requirement.

On Writing your code in Worksheet_PivotTableUpdate event, on each slicer click this event will get called.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

If ActiveWorkbook.SlicerCaches("Slicer_Region").SlicerItems("East").Selected = True Then
UserForm1.Show
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Region").SlicerItems("West").Selected = True Then
UserForm2.Show
End If

End Sub

Where, Slicer_Region = "Name to use in formulas"

You can get this name by right click on slicer and click slicer settings. and find the name to use in formula.

East or West = "Names of slicers"

For which you want to call different forms.

Upvotes: 9

Related Questions