Display data validation criterias when user hovers over the cell

Question

I have the following Excel table:

      A                   B                C    
1     Products            Price        Minimum Price
2     Product A           $30             $10  
3     Product B           $20             $25
4     Product C           $10             $15

The user should put a price per product in column B. The prices are limited based on the values in column C. In the data validation menu I used the "Decimal" criteria and put >= C2 applying to each value in column B. This restriction works fine. However, later on the user will not see column C therefore I also want to include a small window which shows the value in column C as a suggestion when the user hovers over the cell in column B.

Do you guys have any idea if this is possbile with the data validation menu from Excel or is there a macro which can do this?

Thanks for any help.

Answer 1

As far as I know, there's no option in the Data Validation menu for this.

However, you can use the AddComment method of a Range to accomplish this when a value in the Price column is changed. You can use the Worksheet_Change event to handle a change and then apply the comment:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strMininmumPrice As String

    'test if change was made in price values
    If Not (Intersect(Target, Sheet3.Range("B2:B4")) Is Nothing) Then
        'add neighbour cell value to message
        strMinimumPrice = "Minimum price was: " & CStr(Target.Offset(0, 1).Value)

        'create and add comment to target cell
        Target.AddComment strMinimumPrice

    End If

End Sub

Effect looks like this:

Answer 2

As far as I can tell you have two options to show a value in a small window:

(1) You make use the Worksheet_Change or Worksheet_SelectionChange event as suggested by @Robin. Yet, there are several different "sub-option" available with this solution:

1. You could use the comments as proposed in the other answer or
2. you could create a small custom UserForm to show any information you wish to show. The good thing with this variation is that you can customize the form to your preferences and show pretty much anything you want. The following shows a small sample of what could be achieved that way. Note, that the form automatically appears, vanishes, and adjusts its position with the cursor.

(2) You could make use of the Data Validation as originally asked for in your post. The data validation allows you not only to limit the values which you would like to allow for. But you can also specify an input message and customize the error message (if an incorrect value is entered). The following picture gives you a visual idea of this solution.

The following code snippet could help you to automatically set all price validation formulas for all products.

Option Explicit

Sub AutomaticallySetDataValidations()

Dim lngRow As Long
Dim strProduct As String
Dim dblMinimumPrice As Double

With ThisWorkbook.Worksheets(1)
    For lngRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
        strProduct = .Cells(lngRow, 1).Value2
        dblMinimumPrice = IIf(IsNumeric(.Cells(lngRow, 3).Value2), CDbl(.Cells(lngRow, 3).Value2), 0)
        If dblMinimumPrice > 0 Then
            With .Cells(lngRow, "B").Validation
                .Delete
                .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
                    :=xlGreaterEqual, Formula1:=dblMinimumPrice
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = "Price - " & strProduct
                .ErrorTitle = "Invalid price!"
                .InputMessage = "Please enter a new price for " & strProduct & _
                    ". The minimum admissable price for this product is " & Format(dblMinimumPrice, "$#,##0") & "."
                .ErrorMessage = "The price you entered is not acceptable. Please enter a new value."
                .ShowInput = True
                .ShowError = True
            End With
        Else
            Debug.Print "No data validation set for row " & lngRow & " since there is no valid minimum price."
        End If
    Next lngRow
End With

End Sub