在worksheet_change中选择目标表 [英] Select target sheet in worksheet_change

问题描述

我正在尝试编写一个vba宏，该宏将在发生任何更改的情况下在一组单元格中插入Vlookup公式(该公式应在另一张工作表中查找值并将其粘贴到 C列中)制成的表.这是我到目前为止的代码:

I'm trying to write a vba macro which inserts a Vlookup formula (which should look for a value in another sheet and paste it in Column C) in a set of cells when any change is made to the sheet. This is the code I have so far:

Private Sub worksheet_change(ByVal target As Range)

Dim thisRow As Integer

target.Range = Hoja4.Columns("C")

If target.Column = 1 Then
    thisRow = target.Row
    Range("B" & thisRow).Formula = "=vlookup(B & thisRow,Personal!$A$1:$H$500,2,false)"
Else: Exit Do

End Sub

问题是，如何告诉宏它应该在特定的工作表中运行?

Question is, how do I tell the macro that it should run in a specific Sheet?

任何帮助将不胜感激. 预先感谢！

Any help will be appreciated. Thanks in advance!

推荐答案

就像@ShaiRado所述，您的事件代码应在Worksheet模块内运行.如果要检索对该工作表的引用或该工作表的属性(例如名称)，则可以使用Target范围的Parent属性.例如，在您的代码中，它将如下所示:

Like @ShaiRado mentioned, your event code should be running inside a Worksheet module. If you want to retrieve a reference to the sheet, or a property (e.g. name) of that sheet then you can use the Parent property of the Target range. For example in your code it would as follows:

Private Sub worksheet_change(ByVal target As Range)

Dim thisRow As Integer

target.Range = Hoja4.Columns("C")

Dim wsTarget As Worksheet

Set wsTarget = Range.Parent
...

如果使用Workbook级事件处理程序检查Workbook中的任何工作表中的更改，则目标表将被烘焙到事件签名中:

If you use a Workbook level event handler to check for change in any worksheet in your Workbook then the target sheet is baked into the event signature:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Sh is the worksheet that has Target i.e the range that was changed
End Sub

这篇关于在worksheet_change中选择目标表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！