在Excel单元格中输入多个项目

Today we’ll see an interesting tweak to an old technique. One of the articles on my website shows how you can select multiple items from a cell’s drop down list. Instead of overwriting the cell’s value, new selections are added to the end of the cell’s contents. Here's how to enter multiple items in an Excel cell, if it doesn't have a drop down list.

今天，我们将看到对旧技术的有趣调整。 我网站上的文章之一显示了如何从单元格的下拉列表中选择多个项目 。 代替覆盖单元格的值，新选择添加到单元格内容的末尾。 如果没有下拉列表，请按以下步骤在Excel单元格中输入多个项目。

代码检查以验证数据 (Code Checks for Data Validation)

Drop down lists are created with data validation, so the technique uses code to check the active cell, to make sure that it has data validation applied.

下拉列表是通过数据验证创建的 ，因此该技术使用代码检查活动单元格，以确保已应用数据验证。

If there isn’t any data validation on the worksheet, or if the cell doesn’t have data validation, the multiple selection code doesn’t run.

如果工作表上没有任何数据验证，或者该单元格没有数据验证，则多选代码不会运行。

没有数据验证的多个选择 (Multiple Selections Without Data Validation)

Last week, I received an email from Brian Walker, who had created a variation on the code. He wanted to enter multiple values in specific cells, where no data validation was applied.

上周，我收到了Brian Walker的电子邮件，后者创建了代码的变体。 他想在没有应用数据验证的特定单元格中输入多个值。

So, instead of checking for data validation, he named the cells, then checked the active cell for that name. In the screen shot below, the blue cells are named – MVCell1 and MVCell2.

因此，他没有检查数据验证，而是给单元命名 ，然后检查活动单元的名称。 在下面的屏幕截图中，蓝色单元格命名为MVCell1和MVCell2。

多重选择码 (Multiple Selection Code)

Here is the sample code that adds the latest entry to the existing contents. Brian uses a line break as a separator – Chr(10). You could change that to a comma and space – “, “ – if you prefer.

这是将最新条目添加到现有内容的示例代码。 Brian使用换行符作为分隔符– Chr(10)。 如果愿意，可以将其更改为逗号和空格–“，”。

To use the code, copy it, and paste it onto the worksheet module where you want to use it. Remember to name the cells, and change the code to use your names.

要使用该代码，请复制它，然后将其粘贴到要使用它的工作表模块上。 请记住为单元格命名，并更改代码以使用您的名称。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldVal As String
Dim newVal As String
On Error Resume Next
If Target.Count > 1 Then GoTo exitHandler
Select Case Target.Name.Name
   Case "MVCell1", "MVCell2"
       Application.EnableEvents = False
       newVal = Target.Value
       Application.Undo
       oldVal = Target.Value
       Target.Value = newVal
       If oldVal = "" Or newVal = "" Then
           'do nothing
         Else
           Target.Value = oldVal & Chr(10) & newVal
       End If
End Select
exitHandler:
    Application.EnableEvents = True
End Sub

翻译自: https://contexturesblog.com/archives/2013/08/27/enter-multiple-items-in-an-excel-cell/