Summary Box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cassie - 14 May 2008 12:42 GMT

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

Reply to this Message

JLatham - 14 May 2008 13:18 GMT

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
[quoted text clipped - 5 lines]
>
> Cassie

Reply to this Message

Cassie - 14 May 2008 15:09 GMT

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
[quoted text clipped - 23 lines]
> >
> > Cassie

Reply to this Message

JLatham - 14 May 2008 19:15 GMT

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

> Thanks so very much - this works.
> However, I'd like to display the column headings and to have a something
[quoted text clipped - 31 lines]
> > >
> > > Cassie

Reply to this Message

Cassie - 15 May 2008 12:59 GMT

Thanks - this working fine. However, is it possible to include a lookup.
Names will appear in columns K L M N O P - when the cells are clicked I'd
like it to look at col A (where names are) and if it finds a match displays
and then displays the info in cols H I J as you have it already

Many thanks
Cassie

> 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
[quoted text clipped - 115 lines]
> > > >
> > > > Cassie

Reply to this Message

JLatham - 15 May 2008 13:24 GMT

Let me get this straight, since the target keeps moving on me, hard to hit:
You're going to click a cell in columns K:P, any row, and you want it to
take whatever is in the cell you clicked in and then go lookup and try to
find a match to it over in column A (any single row, not necessarily the one
you selected in K:P) and if it finds a match, then return information from H,
I and J on the matched row.

Second question: is the information to be presented in the message box
always from columns H, I and J, or does it change depending on which cell in
K:P you chose? Reason I ask, is that if it's always H, I and J, then the
code can be simplified a lot.

> Thanks - this working fine. However, is it possible to include a lookup.
> Names will appear in columns K L M N O P - when the cells are clicked I'd
[quoted text clipped - 123 lines]
> > > > >
> > > > > Cassie

Reply to this Message

Cassie - 15 May 2008 20:36 GMT

I do appreciate very much the help you've given.
Cols A:J are names and data relating to these individuals, job title etc.
Cols K:P are to do with who could take over their role when the move on -
career development/ seccession planning.
If I click in K2 and the name KS is Smith - I'd like to look up Smith in Col
A (say on row 45) and display in the message box the detail in col H I J on
row 45

The data will always be H I J

Once again many thanks
Cassie

> Let me get this straight, since the target keeps moving on me, hard to hit:
> You're going to click a cell in columns K:P, any row, and you want it to
[quoted text clipped - 135 lines]
> > > > > >
> > > > > > Cassie

Reply to this Message

JLatham - 16 May 2008 03:46 GMT

Try replacing the code I gave you earlier with this, I believe it'll do the
job for you. You can change the first 2 Const declarations to control what
columns you can click in and get the results back from columns H, I and J for
the name chosen.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'any given worksheet can have one and only one event
'handling routine for a particular event.
Const firstColumn = "K" 'leftmost column to react to
Const lastColumn = "P" ' rightmost column to react to
Dim myMessage As String
Dim foundRow As Variant
Dim searchRange As Range
'only works if just a single cell
'in one of the target columns is selected, and...
If Target.Column < Range(firstColumn & "1").Column Or _
Target.Column > Range(lastColumn & "1").Column Then
Exit Sub ' not in a column of interest
End If
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
'is it in the proper range of columns?

Application.ScreenUpdating = False
Application.EnableEvents = False
Set searchRange = Range("A:A")
Range("A1").Activate
On Error Resume Next
foundRow = searchRange.Find(What:=Target.Value, _
After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
Target.Activate
Application.EnableEvents = True
Application.ScreenUpdating = True

If Err = 0 Then
On Error GoTo 0 ' clear trapping
'the portions creating myMessage
'assume that the column headers are
'in row 1 of the columns
myMessage = Range("H1") & ": " & _
Range("H" & foundRow) & vbCrLf _
& Range("I1") & ": " & _
Range("I" & foundRow) & vbCrLf _
& Range("J1") & ": " & _
Range("J" & foundRow)
MsgBox myMessage
Else
Err.Clear
On Error GoTo 0
MsgBox "Name not recognized"
End If

End Sub

> I do appreciate very much the help you've given.
> Cols A:J are names and data relating to these individuals, job title etc.
[quoted text clipped - 148 lines]
> > > > > > >
> > > > > > > Cassie

Reply to this Message