通过使用组合框在另一个电子表格中更改单元格的值来运行VBA代码

Saw this question was never completely answered in similar posts so I'll try again. Starting from a blank workbook I would like to have a drop down list on "Sheet1" but with the input range on "Sheet2!$A

看到这个问题在类似的文章中没有得到完全的回答，所以我再试一次。从一个空白的工作簿开始，我希望在“Sheet1”上有一个下拉列表，但是在“Sheet2”上有输入范围!$1:$ 4”和“Sheet2!$H$5”上的单元链接

When a user selects an option from the dropdown list in Sheet1, the value in Sheet2 should change and that should start the macro. However, I was only able to get a macro to work if Sheet2 was the active sheet. Is there a code that can execute if it detects changes in a different sheet? Question 2, do I put the code in "ThisWorkbook" or "Sheet1"?

当用户从Sheet1中的下拉列表中选择一个选项时，Sheet2中的值将会改变，这将启动宏。然而，只有当Sheet2是活动表时，我才能让宏工作。如果在不同的表中检测到变化，是否有代码可以执行?问题2，我是把代码放在“ThisWorkbook”还是“Sheet1”中?

Keypoints: Macro caused by a change in a cell in a non-active worksheet.

要点:由非活动工作表中单元格的更改引起的宏。

Thanks! Art

谢谢!艺术

1 个解决方案

#1

in my excel 2013 the linked cell updating doesn't fire any worksheet or workbook event, not even the "Worksheet_Change" one

在我的excel 2013中，链接单元更新不会触发任何工作表或工作簿事件，甚至不会触发“Worksheet_Change”事件

so I would workaround it by means of the Worksheet_Calculate" event and some additional "tricks" like follows

因此，我将通过Worksheet_Calculate“事件”和“类似的一些额外的”技巧来解决它。

the event handler is:

事件处理程序是:

Private Sub Worksheet_Calculate()

    If Me.Range("controlCell") <> Me.Range("linkedCell") Then
        Application.EnableEvents = False ' to prevent following instruction from firing this event

        Me.Range("controlCell") = Me.Range("linkedCell") ' save the current value to be used for future checkings
        Call mySub ' <--- place here your actual sub name along with proper arguments

        Application.EnableEvents = True ' to prevent following instruction from firing this event
    End If

End Sub

to be placed in "Sheet2" and which in turn requires the following additional "tricks":

要放置在“Sheet2”中，而这又需要以下附加的“技巧”:

name the linked cell ("Sheet2!$H$5") after "linkedCell" (or whatever you want but be consistent with it in subsequent steps and in the event code above);
在“linkedCell”(或任何您想要的，但在后续步骤和上面的事件代码中与它保持一致的)之后命名链接的单元格(“Sheet2!$H$5”);
choose a cell in Sheet2 where to put a simple "echo" (i.e. formula =linkedCell) of the linked cell
在Sheet2中选择一个单元格，在其中放置链接单元格的简单“echo”(即公式=linkedCell)
choose a cell in Sheet2 and name it after "controlCell" (or whatever you want but be consistent with it...)
在Sheet2中选择一个单元格，并将其命名为“controlCell”(或者您想要的任何东西，但是要与它一致…)

that way it should work like follows

这样它就应该像这样工作

you select a value in the DropDown list in "Sheet1"
在“Sheet1”中选择下拉列表中的值
that will update "linkedCell" and consequently the "echo" cell
这将更新“linkedCell”，从而更新“echo”单元
the latter will finally fire the Worksheet_Calculate event of Sheet2, which checks the "linkedCell" against the "controlCell" and, if different, runs your sub and updates "controlCell" for following use
后者最终将触发Sheet2的Worksheet_Calculate事件，该事件检查“linkedCell”与“controlCell”，如果不同，则运行您的子程序并更新“controlCell”以供后续使用

:$A" and the Cell Link on "Sheet2!$H"Saw this question was never completely answered