• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to add Dynamic Tool Tip in cell based on data- Excel 2007

HI ,


Im using MS Excel 2007 and developing a seating arrangement tool in excel.
In excel file two sheets :
1. Seat availability (Seat vacant or occupied , which I take care through macro)
2. Datavalue : in which Seat no, employee no and employee name is entered (a master table for tool tips).

My requirement is I want to add dynamic tool tip on each seat no , on which Seat No , Employee no and employee name is display as tool tip. And also automatic update whenever I update employee information in "DataValue" sheet.

Sample sheet attached for your reference.
I have added tool tip (manually) on seat no 111, 112 and 113 for reference.

This question I have posted in "Ask a Excel Question" also.

Thanks in advance.

Nitesh
 

Attachments

  • Seating arrngement.xlsm
    8.3 KB · Views: 136
You need to Select the area you want to apply tooltip & then run macro

Code:
Sub TooTipMaker()

Dim SubCell As Variant
Dim MyVal, MyToolTipBody, MyToolTipHead As String

For Each SubCell In Selection
   
    MyVal = SubCell.Value
    Worksheets("DataValue").Select
    Cells.Find(what:=MyVal, lookat:=xlWhole).Activate
    MyToolTipHead = "Seat No - " & Range("A" & ActiveCell.Row).Text
    MyToolTipBody = "(" & Range("B" & ActiveCell.Row).Text & " ) " & Range("C" & ActiveCell.Row).Text
    Worksheets("Seating arrangement").Select
   
    With SubCell.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = MyToolTipHead
        .ErrorTitle = ""
        .InputMessage = MyToolTipBody
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Next SubCell

End Sub
 
Hi Chirayu,

The code is working but , is it possible to added tool tip without any cell selection. If it is possible to lookup from "DataValue" sheet, based on seat no (unique value) and add a tool tip on lookup seat no in sheet "Seat Availability".

Because it's only a sample, I want to do this exercise with 500 seats layout.

Thanks

Nitesh
 
Hi Chirayu,

The code is working but , is it possible to added tool tip without any cell selection. If it is possible to lookup from "DataValue" sheet, based on seat no (unique value) and add a tool tip on lookup seat no in sheet "Seat Availability".

Because it's only a sample, I want to do this exercise with 500 seats layout.

Thanks

Nitesh


You need to Select the area you want to apply tooltip & then run macro

Code:
Sub TooTipMaker()

Dim SubCell As Variant
Dim MyVal, MyToolTipBody, MyToolTipHead As String

For Each SubCell In Selection
  
    MyVal = SubCell.Value
    Worksheets("DataValue").Select
    Cells.Find(what:=MyVal, lookat:=xlWhole).Activate
    MyToolTipHead = "Seat No - " & Range("A" & ActiveCell.Row).Text
    MyToolTipBody = "(" & Range("B" & ActiveCell.Row).Text & " ) " & Range("C" & ActiveCell.Row).Text
    Worksheets("Seating arrangement").Select
  
    With SubCell.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = MyToolTipHead
        .ErrorTitle = ""
        .InputMessage = MyToolTipBody
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Next SubCell

End Sub
 
You could use a fixed range or dynamic range to work the macro on but there is no formula that will add a tooltip. Only VBA can do that
 
You could use a fixed range or dynamic range to work the macro on but there is no formula that will add a tooltip. Only VBA can do that

Is it possible to append my requirement and your code.

The code is working but , is it possible to added tool tip without any cell selection. If it is possible to lookup from "DataValue" sheet, based on seat no (unique value) and add a tool tip on lookup seat no in sheet "Seat Availability".

Thanks
Nitesh
 
You could use a fixed range or dynamic range to work the macro on but there is no formula that will add a tooltip. Only VBA can do that

Like I said in above post - you will need to use a fixed range or dynamic range for that.

Assuming the Seats are always in Columns B to D & your data start in row 2

you will need to change this part of above code

Code:
For Each SubCell In Selection

to this

Code:
Range("B2:D" & Cells(Rows.Count, "B").End(xlUp).Row).Select
For Each SubCell In Selection
 
Is there a way to have this run when a cell changes and it updates the tooltip with the right info?

Code:
Public sPreviousTarget As String
Public sTarget As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

sPreviousTarget = sTarget
sTarget = Target.Address
 
Dim MyToolTipBody As String
Dim MyToolTipHead As String

On Error Resume Next
With Range(sPreviousTarget)
   
    MyToolTipHead = Range(sPreviousTarget).Value
    MyToolTipBody = Range(sPreviousTarget).Value
   
    With Range(sPreviousTarget).Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = MyToolTipHead
        .ErrorTitle = ""
        .InputMessage = MyToolTipBody
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End With
 
End Sub
 
Thank you so much for the quick reply. The code you sent worked great.

Just wanted to clarify what I was requesting though. I wanted to know if the below could be done after the cell is modified and the tooltip gets updated with the info from the other sheet.

You need to Select the area you want to apply tooltip & then run macro

Code:
Sub TooTipMaker()

Dim SubCell As Variant
Dim MyVal, MyToolTipBody, MyToolTipHead As String

For Each SubCell In Selection
  
    MyVal = SubCell.Value
    Worksheets("DataValue").Select
    Cells.Find(what:=MyVal, lookat:=xlWhole).Activate
    MyToolTipHead = "Seat No - " & Range("A" & ActiveCell.Row).Text
    MyToolTipBody = "(" & Range("B" & ActiveCell.Row).Text & " ) " & Range("C" & ActiveCell.Row).Text
    Worksheets("Seating arrangement").Select
  
    With SubCell.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = MyToolTipHead
        .ErrorTitle = ""
        .InputMessage = MyToolTipBody
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Next SubCell

End Sub
 
Not sure what you mean by other sheet. You're saying you update something in one sheet & it changes tooltip in another?
 
you'll need to update a sample file an tell me the rules of what should happen then. e.g. what gets updated where and where tooltip gets added etc.
 
Hi,

So amazing that your response is so quick! I am really impressed.

It's actually very similar to what Nitesh Mathur's example is above in the original post and his attachment can be used. Instead of the tool tips being updated by manually running the macro, I would like the tooltip to update when I change the info in the field immediately after.
 
To autorun the code you need to use Worksheet_Change as per below

Code:
Public sTarget As String

Private Sub Worksheet_Change(ByVal Target As Range)

sTarget = Target.Address

Dim MyVal As String
Dim MyToolTipBody As String
Dim MyToolTipHead As String
Dim Rng As String

MyVal = Range(sTarget).Value
With Worksheets("DataValue")
    Rng = .Cells.Find(What:=MyVal, LookIn:=xlFormulas, LookAt:=xlWhole).Address
    MyToolTipHead = "Seat No - " & .Range(Rng).Value
    MyToolTipBody = "(" & .Range(Rng).Offset(0, 1).Value & " ) " & .Range(Rng).Offset(0, 2).Value
End With

With Range(sTarget).Validation
    .Delete
    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
    :=xlBetween
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = MyToolTipHead
    .ErrorTitle = ""
    .InputMessage = MyToolTipBody
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

 
End Sub
 
Chirayu,

This worked splendidly and was exactly what I was trying to do!!! Thank you SO much for your help and your quick response.

Lawrence C

To autorun the code you need to use Worksheet_Change as per below

Code:
Public sTarget As String

Private Sub Worksheet_Change(ByVal Target As Range)

sTarget = Target.Address

Dim MyVal As String
Dim MyToolTipBody As String
Dim MyToolTipHead As String
Dim Rng As String

MyVal = Range(sTarget).Value
With Worksheets("DataValue")
    Rng = .Cells.Find(What:=MyVal, LookIn:=xlFormulas, LookAt:=xlWhole).Address
    MyToolTipHead = "Seat No - " & .Range(Rng).Value
    MyToolTipBody = "(" & .Range(Rng).Offset(0, 1).Value & " ) " & .Range(Rng).Offset(0, 2).Value
End With

With Range(sTarget).Validation
    .Delete
    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
    :=xlBetween
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = MyToolTipHead
    .ErrorTitle = ""
    .InputMessage = MyToolTipBody
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With


End Sub
 
Hello Chirayu,

I'm trying to adapt your code to show on the tool-tip the dates for start and end of week considering the year and the week number. but I want the tool-tip showed in the cells that are empty on the file and that I will fill with an amount of hours spent in that week. Can you help to do that?
 

Attachments

  • Seating arrngement.xlsm
    32.8 KB · Views: 35
You need to Select the area you want to apply tooltip & then run macro

Code:
Sub TooTipMaker()

Dim SubCell As Variant
Dim MyVal, MyToolTipBody, MyToolTipHead As String

For Each SubCell In Selection
  
    MyVal = SubCell.Value
    Worksheets("DataValue").Select
    Cells.Find(what:=MyVal, lookat:=xlWhole).Activate
    MyToolTipHead = "Seat No - " & Range("A" & ActiveCell.Row).Text
    MyToolTipBody = "(" & Range("B" & ActiveCell.Row).Text & " ) " & Range("C" & ActiveCell.Row).Text
    Worksheets("Seating arrangement").Select
  
    With SubCell.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = MyToolTipHead
        .ErrorTitle = ""
        .InputMessage = MyToolTipBody
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Next SubCell

End Sub
Hello
this is my first post and I hope I will get some answers to my problem.
I applied the above script and it is working fine but I would like to restrict it to a define area of the sheet m for example from B2 to M23.
Can anyone tell me how to do it?
Thanks
 
Back
Top