Input Message depends on the text in one cell left to the active cell

[Kennysan]

Hello!
I am new to VBA and I am hoping if anyone could please help me finding a solution.

I have a table like the below, calling it 'INSTRUCTION' table in 'MASTER' sheet. My goal is show an input message when a cell is selected.
But the contents of the message depends on the text in one cell left to the active cell.


I have another sheet called 'LOV', which has a table called 'NAME_JOB'.


If in MASTER sheet, C2 is selected, then perform VLOOKUP of B2 from NAME_JOB table in LOV sheet, if the value exists then return the value in the input message.
If the value does not exist, simply do nothing.

Is this something achievable with VBA?

Thank you in advance!
Kenny

 

[p45cal]

Right-click the sheet tab MASTER and choose View Code, then where the cursor is in the Visual Basic Editor, paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
  If Not Intersect(Target, Range("Table2[INSTRUCTION]")) Is Nothing Then
    msg = Application.VLookup(Target.Offset(, -1).Value, Sheets("LOV").Range("Table1"), 2, False)
    If IsError(msg) Then Target.Validation.InputMessage = "" Else Target.Validation.InputMessage = msg
  End If
End If
End Sub

It relies on there already being Data Validation present in those cells. (If there isn't data validation in there already, the code can be tweaked to add it.)
Make sure there isn't another Sub Worksheet_SelectionChange(ByVal Target As Range) in the same code module.

ps. The table names in your sample file weren't named as you described but just Table1 and Table2, so try this in your sample file first to confirm it works then tweak the code to match your actual table names in your working file.

 

[Kennysan]

This is perfect!! Thank you so much for getting back to me so quickly, it's like a magic!!