多重数据验证标准 [英] Multiple data validation criterias

问题描述

我有以下Excel表：

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

用户应该在B列中输入每个产品的价格。根据列C中的值限制价格。在数据验证菜单中，我使用十进制标准，并将> = C2应用于B列中的每个值。限制工作正常然而，稍后用户将不会看到列C，因此我也想包括一个小窗口，当窗口悬停在列B中的单元格时，它将C列中的值显示为建议。

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.

你有什么想法，如果这可能与Excel的数据验证菜单有关，还是有一个可以做到这一点的宏？

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?

感谢任何帮助。

推荐答案

据我所知，有两个选项可以在一个小窗口中显示一个值：

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

（1）您可以使用 Worksheet_Change 或 Worksheet_SelectionChange 事件由@Robin提出。然而，这个解决方案有几个不同的子选项：

(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. 你可以使用 / code>如另一个答案或


2. 中提出的，您可以创建一个小的自定义 UserForm 来显示任何您希望的信息以显示。这种变化的好处是，您可以根据自己的喜好自定义表单，并显示任何您想要的内容。以下显示了一些可以通过这种方式实现的小样本。请注意，表单会自动出现，消失，并用光标调整其位置。

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）您可以使用最近在您的帖子中提到的数据验证。数据验证允许您不仅限制您希望允许的值。但是您也可以指定输入消息并自定义错误消息（如果输入的值不正确）。以下图片给出了该解决方案的可视化想法。

(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

这篇关于多重数据验证标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！