Tootip with vlookup

[simon_ives]

Hi all.

I'm wondering if anyone can share a method to add a tooltip that displays the results of a vlookup?

For example, I have a large vehicle tracking spreadsheet with a variety of worksheets for collating monthly data on logbook page numbers, KMs travelled, private usage, missing KMs, etc. What I'd like to do is have a tooltip appear over certain cells on one worksheet that displays the vehicle's contact person's name, as pulled from the vehicle details worksheet.

At the moment we can either add a new row and include a vlookup, but this adds too much duplicate data as the information is present on another worksheet. Or we just navigate to the vehicle details worksheet and search for the vehicle via the rego number.

Thanks in advance.

 

[Michael M]

I don't believe you can do it with a straight out comment....but, you could use VBA
Maybe something like this...you'll need to modiy cell references, etc

Sub MakeComment()
    With Worksheets(1).Range("A1").AddComment
        .Visible = True
        .Text "The Contact Person for This Vehicle is " & YOUR FORMULA
    End With
End Sub

 

[simon_ives]

Thanks Michael, that's exactly the kind of thing I'm looking for.

Quick question though. For the VBA snippet reading: "Worksheets (1)" do I add the textual name of the worksheet in the parentheses?

Thanks again.

 

[Michael M]

You can either use the code number (1), etc
OR

Sub MakeComment()
    With Sheets("YOURSHEETNAME").Range("A1").AddComment
        .Visible = True
        .Text "The Contact Person for This Vehicle is " & YOUR FORMULA
    End With
End Sub

 

[simon_ives]

Thanks!

I've added the following so far to test:

Sub MakeComment()
    With Worksheets("Logbook Tracking").Range("C6").AddComment
        .Visible = True
        .Text "The Contact Person for This Vehicle is "
    End With
End Sub

Where "Logbook Tracking" is the name of the worksheet, "C6" is the worksheet's cell where I'd like the comment to display, and I've ommitted the vlookup for testing so that I can ensure the remaining syntax is complete.

However, the above code does not result in displaying a comment in cell C6. Any ideas on how to move forward with this?

I've also tried "worksheets (2)" with no luck - the VBA Project has the worksheet's details as "Sheet2 (Logbook Tracking)"

Thanks again.

 

[Michael M]

Where are you putting the code....did you want it to fire automatically ??
If so, change it to a Workbook_Change event and paste the code into the Sheet module.
Do you want this work on more than one cell ??

 

[simon_ives]

Thank you very much for the assistance.

I have tried the code both on the worksheet in the VBA project and the workbook in the VBA project.

I would like it to fire automatically. If it doesn't, what should the trigger be?

I need this to work on 69 cells in the same column (each of the 69 rows represents a different vehicle).

If I use a Workbook_Change, what would the correct syntax be for the comment addition?

The vlookup code to return the vehicle's contact is (Where "A6" varies across the 69 rows):

=VLOOKUP(A6,'Vehicle information'!$A$2:$F$72,5,FALSE)

Thanks again.

 

[Michael M]

Try this in the Sheet Module

Sub Worksheet_Change(ByVal Target As Range)
If Not (Target.Comment Is Nothing) Then Target.Comment.Delete
If Target.Count > 1 Then Exit Sub
 If Intersect(Target, Range("C6:C69")) Is Nothing Then Exit Sub 'change cell range to suit
        With Target.AddComment
        .Text "The Contact Person for This Vehicle is "
        End With
End Sub

 

[JoeMo]

Based on post #7 maybe the comment text should be:

.Text "The Contact Person for This Vehicle is " & _
    WorksheetFunction.VLookup(target.Offset(0, -2), Sheets("Vehicle information").Range("$A$2:$F$72"), _
    5, False)

 

[Michael M]

Darn Joe, just got back....

Sub Worksheet_Change(ByVal Target As Range)
Dim x As String
If Not (Target.Comment Is Nothing) Then Target.Comment.Delete
If Target.Count > 1 Then Exit Sub
 If Intersect(Target, Range("C6:C69")) Is Nothing Then Exit Sub 'change cell range to suit
x = Application.WorksheetFunction.VLookup(Target.Offset(0, -2), Sheets("Vehicle information").Range("$A$2:$F$72"), 5, False)
    With Target.AddComment
        .Text "The Contact Person for This Vehicle is " & x
    End With
End Sub

your's is more efficient though...ray: