excel下拉列表联动_将新项目添加到Excel下拉列表

excel下拉列表联动

There are a few sample files on my web site that let you automatically add new items to a drop down list. The most recent version works in newer versions of Excel, and uses named tables to store the lists.

我的网站上有一些示例文件，可让您自动将新项目添加到下拉列表中。 最新版本适用于较新版本的Excel，并使用命名表存储列表。

When the code detects a new item, it asks if you want to add it to the drop down. If you click Yes, the item is added, and the lookup list is sorted in alphabetical order.

当代码检测到新项目时，它询问您是否要将其添加到下拉列表中。 如果单击“是”，则会添加该项目，并且查找列表将按字母顺序排序。

多张纸上的清单 (Lists on Multiple Sheets)

The previous version of this file had all the lookup lists on a sheet named “Lists”. I’ve updated the code in the sample file, so now you can have lists on any sheet in the workbook – as long as the lists are in a named range.

该文件的先前版本在名为“列表”的工作表上具有所有查找列表。 我已经更新了示例文件中的代码，因此现在您可以在工作簿的任何工作表上都有列表-只要列表在命名范围内即可 。

获取列表名称和范围 (Get the List Name and Range)

There is Worksheet_Change event code on the data entry sheet, which automatically runs if you change a cell.

数据条目表上有Worksheet_Change事件代码，如果您更改单元格，它将自动运行。

If the changed cell has a drop down list, the code gets the list’s name from the data validation formula. From that, the code can get the list range address and worksheet name.

如果更改后的单元格具有下拉列表，则代码将从数据验证公式中获取列表的名称。 由此，代码可以获取列表范围地址和工作表名称。

  str = Target.Validation.Formula1
  str = Right(str, Len(str) - 1)
  
  Set rng = ThisWorkbook.Names(str).RefersToRange
  If rng Is Nothing Then Exit Sub
   Set ws = rng.Parent

在受保护的工作表上工作 (Works on Protected Worksheet)

This version of the sample file also works on a protected sheet, as long as the data entry cells are unlocked. However, the data entry table won’t expand if the sheet is protected, so you would need to have some blank rows for new items.

只要解锁数据输入单元，此版本的样本文件也可以在受保护的工作表上使用。 但是，如果工作表受保护，则数据输入表不会展开，因此您需要为新项目添加一些空白行。

The previous version didn’t work on a protected sheet, because it tried to set a range based on Special Cells – Data Validation, and that’s not possible if the sheet is protected.

以前的版本在受保护的工作表上不起作用，因为它试图基于特殊单元格-数据验证来设置范围，如果工作表受保护，则不可能。

  Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  If rngDV Is Nothing Then Exit Sub

The new code just checks the cell for a data validation list, and exits the macro if there isn’t a list.

新代码仅检查单元格中的数据验证列表，如果没有列表，则退出宏。

  If Target.Validation.Type <> 3 Then Exit Sub

获取样本文件 (Get the Sample File)

To download the new version of the Data Validation Add New Items file, go to the instructions page on my website.

要下载新版本的数据验证添加新项文件，请转到我的网站上的说明页面 。

The file is in xlsm format, and contains macros. Be sure to enable macros if you want to test the file.

该文件为xlsm格式，并包含宏。 如果要测试文件，请确保启用宏。

视频：将新项目添加到下拉列表 (Video: Add New Items to Drop Down List)

To see how the sample file works, you can watch this short video.

要查看示例文件的工作方式，您可以观看此短片。

演示地址

翻译自: https://contexturesblog.com/archives/2014/07/10/add-new-items-to-excel-drop-down-list/

excel下拉列表联动