(不可旋转)表链接切片器更新发生后如何触发更新 [英] How to trigger an update after a (non-pivottable) Table linked Slicer Update occurs

问题描述

我需要通过VBA记录在切片器上选择的值(在单元格中).因此，在具有选项 Option A，Option B，Option C 的切片器中，我希望触发一些代码，例如，将 Option A | Option C 放入单元格中.如何触发该代码开始?我无法找到开始该 event 的方法，我尝试单击鼠标右键，assign-macro/NEW，但这只是在您单击时触发一个事件，并且不允许您更新切片器.

解决方案

虽然我无法想到直接调用与切片器关联的事件的方法，但我无法想到.我已经考虑了一下，意识到如果您包含一个易失性函数(在每次操作后都会重新计算的函数)，例如 = NOW()<，您可以在 Worksheet_Calculate 事件中处理您的事件./code>或 = RAND()隐藏在工作表上的某个位置(在隐藏列中，或更改字体颜色以使其混入以便用户看不见).

每次更改切片器参数时，都会重新计算该函数，该函数将调用 Worksheet_Calculate 事件.在这种情况下，您可以检索选定的项目并将其显示在单元格中.

打开VB编辑器，然后将以下代码粘贴到您要在其上使用此功能的任何工作表的工作表代码中:

  Private Sub Worksheet_Calculate()昏暗物品作为SlicerItemDim strOutput作为字符串'从第一个切片机获取所有选定的项目对于ActiveWorkbook.SlicerCaches(1).SlicerItems中的每个项目如果item.Selected = True，则为strOutput = item.Caption&"|"&strOutput万一下一项如果我们在修改宏时不暂停宏'单元格值，此函数将再次被调用，'开始无限循环，因此我们禁用了'单元格值的更新Application.EnableEvents =假'使用切片器输出更新单元格值Me.Range("G1").Value ="|"&strOutputApplication.EnableEvents =真结束子 

在测试工作簿中，我添加了一个表格，其中包含有关作者和书籍的信息(作者名称，书名，价格，评分等)，并且为作者添加了切片器(这是 ActiveWorkbook.SlicerCaches的切片器).(1).SlicerItems 指).

以上代码将在Author slicer中输出的选定选项输出到单元格 G1 (可以根据需要进行更改).我将 = NOW()函数放在单元格 L2 中，并将文本设置为白色，以使用户看不见.

在切片器中选择项目的结果是单元格G1看起来像:

|克莱夫·巴克|斯蒂芬·金|

假设选择了这两个作者.

当我选择H.P.时，在执行代码后输出的快照.Lovecraft和Clive Barker.

这将适用于所有选定的内容，当然，您可以根据需要更改引用的切片器.确保在修改 Worksheet_Calculate 内的单元格值的那部分代码周围保留 Application.EnableEvents = False 和 Application.EnableEvents = True 触发器.code>事件，否则代码将进入无限循环.

我添加了"|"到要添加到单元格G1的字符串的开头，以使其看起来更好(后跟|看起来很尴尬)，但是您也可以修剪尾随|通过使用 Me.Range("G1").Value = Left(strOutput，Len(strOutput)-1)代替 Me.Range("G1").Value ="|"&strOutput .

这将使输出看起来像这样:

H.P.Lovecraft |克莱夫·巴克

再次假设在切片器中选择了这两个作者.如果选择三个，则会得到:

H.P.Lovecraft |斯蒂芬·金|克莱夫·巴克

等等.

I need to, via VBA, make a note of the values (in a cell) selected on a slicer. So in a slicer with options Option A, Option B, Option C I want some code to trigger that puts Option A|Option C into a cell, for example. How do I trigger that code to start with? I can find no way to begin that event, I've tried right-click, assign-macro / NEW, but that just triggers an event when you CLICK and doesn't let you update the slicer.

解决方案

While there are no ways I can think of to directly call an event tied to a slicer; I have thought about it some and realized that you can handle your event in the Worksheet_Calculate event if you include a volatile function (function that gets recalculated after each action) such as =NOW() or =RAND() hidden somewhere on the worksheet (either in a hidden column, or change the font color to blend in so as to be invisible to the user).

Every time you change the slicer parameters, that function will be recalculated which will call the Worksheet_Calculate event. In that event you can retrieve selected items and display them in a cell.

Open the VB Editor and paste the following code into the worksheet code of whichever sheet you want this functionality on:

Private Sub Worksheet_Calculate()
        Dim item As SlicerItem
        Dim strOutput As String

        'Get all selected items from the first slicer
        For Each item In ActiveWorkbook.SlicerCaches(1).SlicerItems
                If item.Selected = True Then
                        strOutput = item.Caption & "|" & strOutput
                End If
        Next item

        'If we don't pause macros when we modify the
        'cell value, this function will get called again and
        'start an infinite loop, so we disable macros for
        'the update of the cell value

        Application.EnableEvents = False

        'Update a cell value with the slicer output
        Me.Range("G1").Value = "|" & strOutput

        Application.EnableEvents = True

End Sub

In my test workbook I added a table containing information about authors and books (author name, book title, price, rating, etc.) and I added a slicer for Author (this is the slicer that ActiveWorkbook.SlicerCaches(1).SlicerItems refers to).

The above code will output the selected options in the Author slicer to cell G1 (which you may change to suit your needs). I placed the =NOW() function in cell L2 and made the text white so as to appear invisible to the user.

The result of selecting items within the slicer is cell G1 looking like:

|Clive Barker|Stephen King|

Assuming those two authors were selected.

A snap of output after the code executes when I selected H.P. Lovecraft and Clive Barker.

This will work for whatever is selected, and of course you can change the referenced slicer as needed. Be sure to keep the Application.EnableEvents = False and Application.EnableEvents = True trigger around the part of the code that modifies the cell value within the Worksheet_Calculate event, otherwise the code will enter an infinite loop.

I added the "|" to the beginning of the string to add to cell G1 to make it look better (looked awkward with a trailing |) but you could just as well trim the trailing | off by using Me.Range("G1").Value = Left(strOutput, Len(strOutput) - 1) instead of Me.Range("G1").Value = "|" & strOutput.

This would make the output look like this:

H.P. Lovecraft|Clive Barker

Again assuming those two authors were selected in the slicer. If you selected three, you would get:

H.P. Lovecraft|Stephen King|Clive Barker

Etc.

这篇关于(不可旋转)表链接切片器更新发生后如何触发更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！