vba 在 excel 2010 的下拉列表中创建一个复选框

create a checkbox inside a dropdown in excel 2010

提问by prem shekhar

Need to create a checkbox inside a dropdown in excel 2010. Have already tried creating a list box and selected the multiselectExtended option but that is not serving our purpose.

需要在 excel 2010 的下拉列表中创建一个复选框。已经尝试创建一个列表框并选择了 multiselectExtended 选项，但这不符合我们的目的。

Sample of the required functionality attached:

附上所需功能的示例：

回答by MeenakshiSundharam

Solved!

解决了！

Check thislink for the solution.

检查此链接以获取解决方案。

You can add an active form list box on the sheet and have the multi-select enabled.

您可以在工作表上添加活动表单列表框并启用多选。

Let me know your thoughts.

让我知道你的想法。

回答by glh

I think the only way to do this is to create a custom dialog. I hope the following is clear enough to do so.

我认为唯一的方法是创建一个自定义对话框。我希望以下内容足够清楚。

1. Add the dialog:

   

2. Add a list box:

   

3. Add the data to a sheet and reference it in the List Box:

   

4. Add a button to the sheet:

   

5. Add a module in VBA and add the following code:

   Public diag As Object 'the dialog box
   
   'this code is assigned to the button on the sheet
   Sub Button3_Click()
       Set diag = DialogSheets("Dialog1") 'define the dialog box
       diag.Show 'sow the dialog box
   End Sub
   
   'to be assigned to the "OK" button in the dialog
   Sub Button2_Click()
   
       ' finds selected items
       Dim Msg As String, i As Integer
       Msg = ""
       With diag.ListBoxes("List Box 5")
           For i = 1 To .ListCount
               If .Selected(i) Then
                   Msg = Msg & .List(i) & ";"
               End If
           Next i
       End With
   
       'set the cell the values as needed
       Worksheets("Sheet1").Range("A1") = Msg
   End Sub
   

1. 添加对话框：

   

2. 添加列表框：

   

3. 将数据添加到工作表并在列表框中引用它：

   

4. 向工作表添加一个按钮：

   

5. 在VBA中添加一个模块并添加以下代码：

   Public diag As Object 'the dialog box
   
   'this code is assigned to the button on the sheet
   Sub Button3_Click()
       Set diag = DialogSheets("Dialog1") 'define the dialog box
       diag.Show 'sow the dialog box
   End Sub
   
   'to be assigned to the "OK" button in the dialog
   Sub Button2_Click()
   
       ' finds selected items
       Dim Msg As String, i As Integer
       Msg = ""
       With diag.ListBoxes("List Box 5")
           For i = 1 To .ListCount
               If .Selected(i) Then
                   Msg = Msg & .List(i) & ";"
               End If
           Next i
       End With
   
       'set the cell the values as needed
       Worksheets("Sheet1").Range("A1") = Msg
   End Sub