Cell Selected should display message which is on a cell on another sheet

Question

I am new to Excel VBA so please be patient with me. Thanks.

I have 2 sheets in my workbook. Sheet1 and Sheet2 both has same number of rows. I have a column named as SITE in Sheet2 and REMARK named on Sheet1. What I need is whenever I select the cell in SITE column ,it should display a message(i.e; message should contain cell value of REMARK column) from the Sheet1.

For example: If SITE column-cell 3 is selected then message displayed should contain value of REMARK column-cell 3.

By a "message", do you mean a message box (with an "OK" or "Cancel")? Or just some type of message showing the remark value? If the former, you'll want to look into MsgBox() in VBA, and Worksheet_SelectionChange event. Have you tried anything yet? — BruceWayne
I haven't write anything yet. Just a message like in data validation. I don't need OK and Cancel. Just the message or note showing REMARK value. — Rehaan

Dirk Reichel Dirk Reichel · Accepted Answer · 2016-06-30T19:05:58

because I'm bored:

'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
your "...SITE column..." in the question said me that the headers are at row 1... but if you know where they are, and the pos is not changing, then you can use it like this (I assume that the first value for "SITE" is in P8 and the first "REMARK" at Q9 to show how to offset):

'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

Cell Selected should display message which is on a cell on another sheet

1 Answers