单元格中设置多选下拉框是可以做到的
楼主没有举例说明具体需求,下面以选择多个姓名为例,步骤如下:
先在【清单】表中列举一个姓名列表
在【多选下拉菜单】表中设置多选下拉框,按Alt+F11键进入VBE编辑器,将如下两段代码粘贴到【多选下拉菜单】这个表的右侧代码区
Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
If ListBox1.ListIndex = -1 Then Exit Sub
Dim i&, str$
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
str = str & ";" & .List(i)
End If
Next
.TopLeftCell.Offset(, -1).Value = Mid(str, 2)
.Visible = False
End With
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row > 2 And Target.Column = 2 Then
Dim arr
arr = Sheets("清单").Cells(2, 1).Resize(Sheets("清单").Cells(Rows.Count, 1).End(xlUp).Row - 1)
With ListBox1
.MultiSelect = 1
.ListStyle = 1
.List = arr
.Top = Target.Top
.Left = Target.Left + Target.Width
.Height = Target.Height * 15
.Width = 90
.Visible = True
End With
Else
ListBox1.Clear
ListBox1.Visible = False
End If
End Sub
最终效果如图:
注意:档案要保存为启用宏的工作簿
单元格中设置多选下拉框是可以做到的
楼主没有举例说明具体需求,下面以选择多个姓名为例,步骤如下:
先在【清单】表中列举一个姓名列表
在【多选下拉菜单】表中设置多选下拉框,按Alt+F11键进入VBE编辑器,将如下两段代码粘贴到【多选下拉菜单】这个表的右侧代码区
Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
If ListBox1.ListIndex = -1 Then Exit Sub
Dim i&, str$
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
str = str & ";" & .List(i)
End If
Next
.TopLeftCell.Offset(, -1).Value = Mid(str, 2)
.Visible = False
End With
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row > 2 And Target.Column = 2 Then
Dim arr
arr = Sheets("清单").Cells(2, 1).Resize(Sheets("清单").Cells(Rows.Count, 1).End(xlUp).Row - 1)
With ListBox1
.MultiSelect = 1
.ListStyle = 1
.List = arr
.Top = Target.Top
.Left = Target.Left + Target.Width
.Height = Target.Height * 15
.Width = 90
.Visible = True
End With
Else
ListBox1.Clear
ListBox1.Visible = False
End If
End Sub
最终效果如图:
注意:档案要保存为启用宏的工作簿