Setting up a popup message referencing the same cell in a different sheet

[jlp233]

I am working on an employee scheduling aid that displays a popup message for each employee (when clicked) with the skill set that that particular employee has.

This code works for the one employee I set it up for:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim Rng1 As Range


Set Rng = Range("A54")
If Intersect(Target, Rng) Is Nothing Then Exit Sub
MsgBox "This employee's skill set includes: " & Sheet2.Range("B54").Value


End Sub

How can I get it to work for all employees without writing the code for each of the 400+ employees in the schedule? I'm having trouble finding answers.

Thanks for your help!

 

[Stridhan]

employees from cell a2 going down in sheet1 and skills in sheet2, same row but in column B?

 

[Stridhan]

Could try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim rng As Range, lr As Integer
lr = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set rng = ThisWorkbook.Sheets("Sheet1").Range("A2:A" & lr)

If Not Intersect(Target, rng) Is Nothing Then
    MsgBox "This employee's skill set includes: " & ThisWorkbook.Sheets("Sheet2").Range("B" & Target.Row)
End If

End Sub

 

[jlp233]

Could try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim rng As Range, lr As Integer
lr = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set rng = ThisWorkbook.Sheets("Sheet1").Range("A2:A" & lr)

If Not Intersect(Target, rng) Is Nothing Then
    MsgBox "This employee's skill set includes: " & ThisWorkbook.Sheets("Sheet2").Range("B" & Target.Row)
End If

End Sub

Thanks for the reply! I'll give this a try when I get back to my desk. I realized after I posted that they aren't in the same column right now but I intended to switch the column for simplicity sake.

 

[jlp233]

Could try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim rng As Range, lr As Integer
lr = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set rng = ThisWorkbook.Sheets("Sheet1").Range("A2:A" & lr)

If Not Intersect(Target, rng) Is Nothing Then
    MsgBox "This employee's skill set includes: " & ThisWorkbook.Sheets("Sheet2").Range("B" & Target.Row)
End If

End Sub

Hmm... I replaced my previous code with this and now I'm not getting a popup message at all.

 

[Stridhan]

Are your employees in column A sheet1?
Are their skills in the same row, in column B, on sheet2?

 

[jlp233]

Correct. When I click on cell (A,54) on sheet 1, the popup referencing the data from cell (B,54) on sheet 2 shows up. It works perfectly when I specify these cells, I would just like it to work for any row and reference that row in column b on sheet 2.

 

[Stridhan]

Are your employees in the sheet named Sheet1?

 

[jlp233]

Are your employees in the sheet named Sheet1?

Employees with their work schedules are in sheet 1
Employees with their skill sets are in sheet 2.

I'd like to avoid having them on the same sheet and just using the popup because the schedule sheet contains so much data already. The popup will be a scheduling tool as needed, but doesn't have to be visible at all times.

 

[Stridhan]

https://www.dropbox.com/s/b0qfce832j4iyen/test.xlsm?dl=0

have a look