How can I get values from a cell in Sheet1 to appear as tooltip/comment in Sheet2?

[semolina]

Hi everybody,

I am currently working on a shortlisting form used by hiring panels and trying to make it more user-friendly. At the moment the criteria used to evaluate candidates are on Sheet1 in my workbook, and the scoring for these criteria is entered on Sheet2. I am trying to make a tooltip/comment with each Sheet1 criterion appear in the corresponding cell of Sheet2, so users don't have to change between tabs all the time to check the criteria.

So Sheet1!A12 contents would be connected to Sheet2!F8, and Sheet1! B12 would connect to Sheet2!H8 etc. This would only need to be done once - it doesn't need to automatically update the comment/tooltip at regular intervals or anything.

I can add a hyperlink linking the cell pairs (but can't make it show the cell contents instead of the link text), and can do data validation input message popups etc. I can create a plain text comment in the relevant cell using VBA (but don't know how to pull the values from the other sheet). I have gone through quite a lot of similar forum posts but am fairly new to all this so can't quite figure out how to tie it all together in this situation - any help would be appreciated!

 

[p45cal]

Try this on your attached workbook (make sure there's some text to use for the data validation message in cells Sheets("Selection Criteria").Range("A12:G12,A14:G14,A16:G16"):

Sub blah()
Set rngCritText = Sheets("Selection Criteria").Range("A12:G12,A14:G14,A16:G16")
colmOfset = 0
Set BaseRng = Sheets("Scoring grid").Range("F8:F157")
For Each cll In rngCritText.Cells
  With BaseRng.Offset(, colmOfset).Validation
    .Delete
    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
    .InCellDropdown = False    'True
    .InputMessage = cll.Value
  End With
  colmOfset = colmOfset + 2
  If colmOfset > 26 Then Exit For    'this limits how many columns are affected.
Next cll
End Sub

 

[semolina]

Thank you so much! This works perfectly and I managed to adapt it to do the second set of criteria too.