选定的单元格应显示另一个工作表上的单元格上的消息

1 回答

• 2

  因为我很无聊：

  'in Sheet1
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = Application.Match("SITE", Target.Parent.Rows(1), 0) Then MsgBox Sheets("Sheet2").Cells(Target.Row, Application.Match("REMARK", Sheets("Sheet2").Rows(1), 0)).Value2
  End Sub
  
  'in Sheet2
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = Application.Match("REMARK", Target.Parent.Rows(1), 0) Then MsgBox Sheets("Sheet1").Cells(Target.Row, Application.Match("SITE", Sheets("Sheet1").Rows(1), 0)).Value2
  End Sub
  

  EDIT
  问题中你的"...SITE column..."告诉我 Headers 是在第1行...但如果你知道它们在哪里，并且pos没有改变，那么你可以像这样使用它（我假设"SITE"的第一个值是在P8和Q9的第一个"REMARK"显示如何偏移）：

  'in Sheet1
  'the "Target.Row + 1" need the row offset because it starts 1 row later
  'if they start at the same row, the "+ 1" can be deleted
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = Range("P1").Column And Target.Row >= 8 Then MsgBox Sheets("Sheet2").Range("Q" & Target.Row + 1).Value2
  End Sub
  
  '---------------------------------------------------------------------
  
  'in Sheet2
  'because the offset must be the opposite, the "+ 1" becomes "- 1" here
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = Range("Q1").Column And Target.Row >= 9 Then MsgBox Sheets("Sheet1").Range("P" & Target.Row - 1).Value2
  End Sub
  

  回复于 2024-04-27T09:06:54+08:00