跳到主要内容

如何从下拉列表中选择多个项目到Excel中的单元格中?

下拉列表在Excel的日常工作中经常使用。 默认情况下,在下拉列表中只能选择一项。 但有时,您可能需要从下拉列表中将多个项目选择到一个单元格中,如下图所示。 如何在Excel中处理它?

doc从下拉列表1中选择多个项目 doc箭头向右 doc从下拉列表2中选择多个项目

从下拉列表中选择多个项目到带有VBA的单元格中

从下拉列表中轻松快速地选择多个项目到一个单元格中


从下拉列表中选择多个项目到带有VBA的单元格中

这里有一些VBA可以帮您解决此工作。

从单元格的下拉列表中选择重复项

1.创建下拉列表后,右键单击工作表选项卡以选择 查看代码 从上下文菜单。
doc从下拉列表3中选择多个项目

2.然后在 Microsoft Visual Basic应用程序 窗口,将下面的代码复制并粘贴到空白脚本中。

VBA:从单元格的下拉列表中选择多个项目

Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyExtendoffice20221111
    Dim xRgVal As Range
    Dim xStrNew As String
    On Error Resume Next
    Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation)
    If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub
    If Intersect(Target, xRgVal) Is Nothing Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    xStrNew = Target.Value
    Application.Undo
    If xStrNew = Target.Value Then
    Else
    xStrNew = xStrNew & " " & Target.Value
    Target.Value = xStrNew
    End If
    Application.EnableEvents = True
End Sub

3.保存代码并关闭窗口以返回到下拉列表。 现在,您可以从下拉列表中选择多个项目。

注意:

1.使用VBA,它可以按空格分隔项目,您可以更改 xStrNew = xStrNew& “” & 目标价值 他人根据需要更改定界符。 例如, xStrNew = xStrNew&“," & 目标价值 将用逗号分隔项目。

2.此VBA代码适用于工作表中的所有下拉列表。

从下拉列表中选择多个项目到一个单元格,无需重复

如果只想从下拉列表中选择一个唯一项,则可以重复上述步骤并使用以下代码。

VBA:从下拉列表到单元格中选择多个项目,无需重复

Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyExtendoffice20221111
    Dim I As Integer
    Dim xRgVal As Range
    Dim xStrNew As String
    Dim xStrOld As String
    Dim xFlag As Boolean
    Dim xArr
    On Error Resume Next
    Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation)
    If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub
    If Intersect(Target, xRgVal) Is Nothing Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    xFlag = True
    xStrNew = " " & Target.Value & " "
    Application.Undo
    xStrOld = Target.Value
    If InStr(1, xStrOld, xStrNew) = 0 Then
        xStrNew = xStrNew & xStrOld & " "
    Else
        xStrNew = xStrOld
    End If
    Target.Value = xStrNew
    Application.EnableEvents = True
End Sub

以上两个 VBA 代码都不支持删除部分单元格内容,只支持清除单元格的所有项。


从下拉列表中轻松快速地选择多个项目到一个单元格中

在 Excel 中,除了 VBA 代码,没有其他方法允许从单元格中的下拉列表中选择多个项目。 但是,那 多选下拉列表 的特点 Kutools for Excel 只需点击一下即可快速轻松地处理此工作。

Tips::在应用此工具之前,请先安装 Kutools for Excel。 立即前往免费下载.
步骤1:选择 Kutools > 下拉列表 > 多选下拉列表
第 2 步:在多选下拉列表对话框中,指定设置
  1. 设置范围;
  2. 指定分隔单元格中项目的分隔符;
  3. 决定文字方向;
  4. 单击“确定”。
    doc 从下拉列表 kte 1 中选择多个项目
结果:

从下拉列表 kutools 中选择多个项目

备注:要使用多选下拉列表功能,您应该首先安装Kutools for Excel,请 点击下载并立即免费试用 30 天.

在Excel中轻松创建动态的2级或多级依赖下拉列表

在Excel中,创建2级或多级下拉列表很复杂。 在这里 动态下拉列表 实用程序 Kutools for Excel 可以帮你一个忙。 您唯一要做的就是按所示示例对数据进行排序,然后选择数据范围和输出范围,然后将其留给实用程序。  点击免费试用30天!
doc高级合并行
 
Kutools for Excel:拥有300多个便捷的Excel加载项,可以在30天内免费试用。

最佳办公生产力工具

热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

kte选项卡201905


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (26)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Como posso utilizar a função com a planilha bloqueada?
This comment was minimized by the moderator on the site
Hi, Macros, try below code, it supports to select multi items in drop down list in protected sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyExtendoffice20221111
    Dim xRgVal As Range
    Dim xStrNew As String
    On Error Resume Next  
    xType = 0
    xType = Target.Validation.Type
    If xType <> 3 Then Exit Sub
    If (Target.Count > 1) Then Exit Sub

    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    xStrNew = Target.Value
    Application.Undo
    If xStrNew = Target.Value Then
    Else
    xStrNew = xStrNew & " " & Target.Value
    Target.Value = xStrNew
    End If
    Application.EnableEvents = True
End Sub

And also Kutools for Excel 26.1 and later versions supports this job in protected sheet.
This comment was minimized by the moderator on the site
¿Cómo puedo hacer para que el orden en que se coloque el texto, sea tan cuál como yo quiera?

Ejemplo:
Mi texto para la lista es:
TRAMITAR ESTE ASUNTO
COORDINAR
ANALIZAR Y RECOMENDAR

sí selecciono:
TRAMITAR ESTE ASUNTO
ANALIZAR Y RECOMENDAR

me aparece
ANALIZAR Y RECOMENDAR, TRAMITAR ESTE ASUNTO

o sea, los de abajo me aparecen primero, pero quiero que sea en el orden en que voy seleccionando...
TRAMITAR ESTE ASUNTO, ANALIZAR Y RECOMENDAR.....
This comment was minimized by the moderator on the site
Hi, Gorgelys, Kutools's multi-select drop down list can help you. The items you selected will be in the order like you want.
This comment was minimized by the moderator on the site
the code works but won't let me delete can someone please post the new code.
This comment was minimized by the moderator on the site
bonjour,
selon le premier exemple tout fonctionne très bien jusqu'à ce que je verrouille la feuille.
quand la feuille est verrouillée, je n'ai plus le choix multiple !
comment faire ?
merci par avance
This comment was minimized by the moderator on the site
Hi, sorry for that. We will upgrade our feature next version to avoid this problem. Thank you for your feekback.
This comment was minimized by the moderator on the site
Merci bcp
Mais à quand la nouvelle version ?
This comment was minimized by the moderator on the site
Hi, we have updated this version for you, but this is a beta version, please download it from this: https://download.extendoffice.com/downloads/Kutools-for-Excel/beta/26.10/KutoolsforExcelSetup.Inno.exe
If there are any problems, welcome for your feedback.
This comment was minimized by the moderator on the site
Merci,
Je vais la télécharger et essayer.
Bonne journée
This comment was minimized by the moderator on the site
Hi, the new version is in planning, since next version will upgrade multiple features, it may take somewhat time.
This comment was minimized by the moderator on the site
Hi! Como puedo usar este codigo para todo un worksheet y no solo para una pestana?
This comment was minimized by the moderator on the site
Hi, Vero, Kutools for Excel's Multi-select Drop-down List feature suports selecting multiple items from drop down list in the whole worksheet or across workbook, just specify the Specified Scope in the Multi-select Drop-down List Settings dialog.
This comment was minimized by the moderator on the site
How can I do that for the fist formula you provided :) Thanks in advanced!
This comment was minimized by the moderator on the site
Quando quero deletar alguma opção ou todas dá erro. Como posso resolver?
This comment was minimized by the moderator on the site
I am also finding that after selecting multiple items using the updated VBA code, I still cannot clear the cell, it just keeps multiplying.
Does anyone have a solution for this yet?
This comment was minimized by the moderator on the site
Hi, Rusty, the code I have updated for making it more stable. But because the cell is in data validation, blank is out of data validation, the code cannot solve this problem.
This comment was minimized by the moderator on the site
the code works but carries over across all cells and multiplies in the cells and wont allow delete can you assist?
This comment was minimized by the moderator on the site
If I create a sheet with dropdown list using checkboxes, is there a way to share this workbook with this checkboxes feature?
This comment was minimized by the moderator on the site
How would you apply this functionality but making it so that there aren't redundant values? Any help would be appreciated!
This comment was minimized by the moderator on the site
I used this "Select multiple items from drop down list to a cell without repeat"
this is what happened
1. Multiple selections happen without problems.
2. The issue is when i try to edit and remove an option. There is no way for me to remove an option. it keeps multiplying.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations