Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.5k views
in Technique[技术] by (71.8m points)

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...