数据验证下拉列表未自动更新 [英] Data Validation drop down list not auto-updating

问题描述

我在WorkSheet(PartsList)上有一列简单的数据，它从相应行中过滤数据.在同一工作簿中的另一个WorkSheet(BoM)上，我通过数据验证列表"引用此列.

I have a simple column of data on a WorkSheet (PartsList) which filters data from the its respective rows. On another WorkSheet (BoM) within the same WorkBook I am reference this column via a Data Validation 'List'.

我遇到的问题是，当更新"PartsList"工作表中的数据时，下拉列表中显示的信息保持不变.

The issue that I am having is that when the data within the 'PartsList' WorkSheet is updated the information shown in the drop down list remains the same.

如下图所示，尽管部件号"和变量"列已更新，但下拉列表并未更新部件号".

As you can see in the image below, the drop down list hasn't updated the 'part#' although the 'Part Number' and 'Variant' column has.

这是生成列表的公式:是否有使下拉列表自动更新的方法?

And here is the formula that generates the list: Is there anyway of making the drop down list auto-update?

推荐答案

我设法解决了这个问题.

I managed to solve the problem.

我在线找到了一些VBA代码，并在必要时进行了更改.请参见下面的代码，并提供以下解释:

I found some VBA code online and made alterations where necessary. See code below with explanations including

 Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Ensure all lists are made from tables and that these tables are named
' in the Name Manager.
' When creating your Data Validation List, instead of selecting a range
' in 'Source', click within 'Source' and press 'F3'. Finally select your
' tables name.
Dim strValidationList As String
Dim strVal As String
Dim lngNum As Long

On Error GoTo Nevermind
strValidationList = Mid(Target.Validation.Formula1, 2)
strVal = Target.Value
lngNum = Application.WorksheetFunction.Match(strVal, ThisWorkbook.Names(strValidationList).RefersToRange, 0)

' Converts table contents into a formula
If strVal <> "" And lngNum > 0 Then
    Application.EnableEvents = False
    Target.Formula = "=INDEX(" & strValidationList & ", " & lngNum & ")"
End If

Nevermind:
    Application.EnableEvents = True

End Sub

这篇关于数据验证下拉列表未自动更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！