尝试使用 VBA-excel 根据另一个工作表中的单击值过滤一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5889609/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trying to use VBA-excel to filter one worksheet based on clicked value in another workseet
提问by ply
OK, I'm a total VBA noob, so excuse my awful code.
好吧，我完全是 VBA 菜鸟，所以请原谅我糟糕的代码。
I have two excel worksheets, one titled 'Contractors' and one titled 'Referring_to_Contractors'.
我有两张 Excel 工作表，一张名为“承包商”，一张名为“Referring_to_Contractors”。
The contractors sheet is laid out like so.
承包商表是这样布置的。
Terr ContractorID First Last
1 7 Bob Smith
2 5 Jeff Brown
3 8 Stan Lee
Terr ContractorID First Last
1 7 Bob Smith
2 5 Jeff Brown
3 8 Stan Lee
The Referring_to_Contractors sheet has the same fields and layout as the Contractors sheet above, but also has additional columns for Referring Contractors, so it has columns titled "Ref_Contractor_Id", "Ref_First", "Ref_Last", etc.
Referer_to_Contractors 表与上面的 Contractors 表具有相同的字段和布局，但还有用于引用承包商的额外列，因此它具有标题为“Ref_Contractor_Id”、“Ref_First”、“Ref_Last”等的列。
What I'm trying to do is use VBA so that when someone double clicks a row in the "Contractors" sheet, it will take the value in the Contractor_ID column, then look in the "Referring_to_Contractors" sheet and filter by all records in that sheet that have that value as Contractor_ID. Essentially, this would display referral information for the Contractor_ID clicked on the first sheet. I created a named range for the Contractor_ID field titled "PrimaryContractor"
我想要做的是使用 VBA，以便当有人双击“承包商”表中的一行时，它将采用 Contractor_ID 列中的值，然后查看“Referring_to_Contractors”表并按其中的所有记录进行过滤具有该值为 Contractor_ID 的工作表。本质上，这将显示在第一张工作表上单击的 Contractor_ID 的推荐信息。我为名为“PrimaryContractor”的 Contractor_ID 字段创建了一个命名范围
So, on the first sheet 'Contractors', I have:
所以，在第一张“承包商”上，我有：
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
End Sub
and..
和..
Sub Select_Ref_Contractors()
ContractorId = Range("PrimaryContractor").Value
With Sheets("Referring_to_Contractors")
.Visible = True
.Select
End With
ActiveSheet.Range("$B:$N163).AutoFilter Field: =1, Criteria1:= ContractorID
Application.Goto Range("A1"), True
End Sub
Conceptually this seems like it should be pretty simple yet for some reason I can't get the second sheet to filter correctly.
从概念上讲，这似乎应该很简单，但由于某种原因，我无法正确过滤第二张纸。
Any helps or even useful links would be greatly appreciated.
任何帮助甚至有用的链接将不胜感激。
回答by Duncan Howe
I have just knocked this up in Excel 2007 and it seems to work
我刚刚在 Excel 2007 中解决了这个问题，它似乎有效
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim id As String
If Not Target.Cells.Count > 1 Then
id = CStr(Selection)
Sheet2.Activate
Sheet2.Range("A1", "c4").AutoFilter 1, id
End If
End Sub
It is using the same table on both sheets as below and when you double click a cell on Sheet1, the BeforeDoubleClick event fires and puts you onto Sheet2 with the filter applied.
它在两个工作表上使用相同的表格，如下所示，当您双击 Sheet1 上的单元格时，BeforeDoubleClick 事件会触发并将您置于应用过滤器的 Sheet2 上。
ID ID2 Text
1 2 a
2 2 b
3 3 c
