Hopefully this adaptation of the code will better serve you, and be easier
for you to modify. You can keep adding Case Is = statements for as many
columns as you care to deal with, and modify the myMessage building
statements under them to pickup on what you want to present to the user.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'any given worksheet can have one and only one event
'handling routine for a particular event.
Dim myMessage As String
'only works if just a single cell
'in one of the target columns is selected, and...
If Target.Cells.Count > 1 Then
Exit Sub
End If
'... the cell has something in it
'delete this If...End If statement if
'you want the message to always appear
'when a cell in one of the target columns
'is selected.
If IsEmpty(Target) Then
Exit Sub
End If
'the portions creating myMessage
'assume that the column headers are
'in row 1 of the columns
Select Case Target.Column
Case Is = Range("K1").Column
myMessage = Range("H1") & ": " & _
Range("H" & Target.Row) & vbCrLf _
& Range("I1") & ": " & _
Range("I" & Target.Row) & vbCrLf _
& Range("J1") & ": " & _
Range("J" & Target.Row)
Case Is = Range("L1").Column
myMessage = Range("H1") & ": " & _
Range("H" & Target.Row) & vbCrLf _
& Range("I1") & ": " & _
Range("I" & Target.Row) & vbCrLf _
& Range("J1") & ": " & _
Range("J" & Target.Row)
Case Is = Range("M1").Column
myMessage = Range("H1") & ": " & _
Range("H" & Target.Row) & vbCrLf _
& Range("I1") & ": " & _
Range("I" & Target.Row) & vbCrLf _
& Range("J1") & ": " & _
Range("J" & Target.Row)
Case Is = Range("N1").Column
myMessage = Range("H1") & ": " & _
Range("H" & Target.Row) & vbCrLf _
& Range("I1") & ": " & _
Range("I" & Target.Row) & vbCrLf _
& Range("J1") & ": " & _
Range("J" & Target.Row)
Case Is = Range("O1").Column
myMessage = Range("H1") & ": " & _
Range("H" & Target.Row) & vbCrLf _
& Range("I1") & ": " & _
Range("I" & Target.Row) & vbCrLf _
& Range("J1") & ": " & _
Range("J" & Target.Row)
Case Is = Range("P1").Column
myMessage = Range("H1") & ": " & _
Range("H" & Target.Row) & vbCrLf _
& Range("I1") & ": " & _
Range("I" & Target.Row) & vbCrLf _
& Range("J1") & ": " & _
Range("J" & Target.Row)
Case Else
'any column selected other than
' K through P, no message
myMessage = ""
End Select
If myMessage <> "" Then
'have info to present
MsgBox myMessage
End If
End Sub
Cassie said:
Thanks so very much - this works.
However, I'd like to display the column headings and to have a something
identical in the col L, M, N, O, P. I tried copying the script and changing
the Col K to Col L but it produced an error
Once again many thanks
Cassie
:
Maybe this will help, not a comment, but does make sure the information
presented is current. Put this code into the worksheet's code moduld. To do
so, select the sheet, right-click on its name tab and choose [View Code] then
copy and paste the code into the code module presented to you. I've written
it so that it should be easy for you to modify.
Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
'if clicked in column K and
'cell has something in it
If Target.Column = Range("K1").Column And _
Not IsEmpty(Target) Then
MsgBox Range("H" & Target.Row) & vbCrLf _
& Range("I" & Target.Row) & vbCrLf _
& Range("J" & Target.Row)
End If
End Sub
:
I have a spreadsheet with a 15 columns of text data.
Ideally, when you hover over/click on a name in a cell in a column, say
column K. I want it to look up the surname in the full list and display data
in that's in columns H, I,J in a box like a "pop ups" - a bit like a comment
box would show up.
Appreciate any help - not even sure if this is possible
Cassie