Excel-如何双击单元格并引用另一张工作表

Question

我想知道这是否可能，以及如何做到：

我希望能够双击一个单元格(在Z列(，引用同一行的G列中的内容，然后从另一张工作表的G列信息中找到并设置焦点。

例如，当我双击Z1中的单元格时，它会查找G1中的信息，并在另一张工作表上找到它，并将焦点设置为该新单元格。

这可能吗？

感谢

Answer 1

是的，您需要实现Worksheet_BeforeDoubleClick事件处理程序，然后您可以做任何您喜欢的事情，处理程序是Worksheet对象的一个事件。在模块代码中执行您的主代码，以保持在多个工作表中的可重用性。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Call Module1.onDrillDownToData(Target)
End Sub
- - - 
Public Function onDrillDownToData(ByRef sender As Range)
Dim ws as worksheet
Dim iCol As Long
Dim iRow As Long
Dim dt As Date
set ws = sender.Parent
' do anything you want with worksheet
' sender is the origin cell
iCol = Sender.Column
iRow = Sender.Row
dt = Now()
ws.Cells(iRow, iCol).Value = "'" & Format(dt, "yyyy-MM-dd", vbMonday)
ws.Cells(iRow + 1, iCol).Value = "'" & Format(dt, "hh:nn:ss", vbMonday)
set ws = Application.Worksheets("TargetSheet")
ws.Activate
ws.Range("A5").Activate
End Function

Answer 2

工作表BeforeDoubleClick

• 将以下代码复制到要双击的工作表的工作表模块中(在项目资源管理器的VBE中双击相应的工作表以打开其代码窗口(
• 在退出VBE之前，请调整代码中的常量，尤其是目标工作表名称(wsName，括号中的名称(

代码

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Define constants.
Const wsName As String = "Sheet2"   ' Destination Worksheet Name
Const SourceColumn As String = "Z"  ' Source Column String
Const CriteriaColumn As String = "G"

' Not sure if this is even possible.
If Target.Rows.Count > 1 Then Exit Sub

If Not Intersect(Target, Columns(SourceColumn)) Is Nothing Then
Dim Criteria As Variant
Criteria = Cells(Target.Row, CriteriaColumn)
If Not IsError(Criteria) And Not IsEmpty(Criteria) Then
Dim cel As Range
With ThisWorkbook.Worksheets(wsName)
Set cel = .Cells _
.Find(What:=Criteria, _
After:=.Cells(.Rows.Count, .Columns.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False) ' Change to True is necessary
If Not cel Is Nothing Then
.Activate
cel.Select
End If
End With
End If
End If
End Sub