vba Excel 中单个单元格中多个值的复选框

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/23318785/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 18:09:09  来源:igfitidea点击:

Checkboxes for multiple values in a single cell in Excel

excelexcel-vbaexcel-2007vba

提问by mayabelle

I am a C#/.NET developer but am not too familar with Excel programming or VBA. For a side project, I have a spreadsheet that will be used by non-technical users for data entry. Later this spreadsheet will be exported to a different format via a C# command-line program that I wrote so that the data can be dumped into a different system.

我是一名 C#/.NET 开发人员,但对 Excel 编程或 VBA 不太熟悉。对于一个副项目,我有一个电子表格,供非技术用户用于数据输入。稍后,这个电子表格将通过我编写的 C# 命令行程序导出为不同的格式,以便将数据转储到不同的系统中。

I need the data values to be entered exactly as the command-line program will expect them to be, so user error due to typos or slight wording differences would be problematic. I need the user to select from possible values rather than rely on the user to enter the correct value.

我需要完全按照命令行程序所期望的方式输入数据值,因此由于拼写错误或轻微的措辞差异而导致的用户错误将是有问题的。我需要用户从可能的值中进行选择,而不是依赖用户输入正确的值。

For columns that can only have a single value in a cell, I was able to accomplish this by using a dropdown menu from which the user can select. I did this via the instructions here:

对于在一个单元格中只能有一个值的列,我可以通过使用用户可以从中选择的下拉菜单来实现这一点。我通过这里的说明做到了这一点:

https://support.office.com/en-ie/article/add-or-remove-items-from-a-drop-down-list-0b26d3d1-3c4d-41f5-adb4-0addb82e8d2c

https://support.office.com/en-ie/article/add-or-remove-items-from-a-drop-down-list-0b26d3d1-3c4d-41f5-adb4-0addb82e8d2c

The problem is, I have several columns whose cells can hold multiple values, separated by commas. For example, I have a "Color" column. The value of a cell in this column may be a single color (e.g. "Red") or a list of colors separated by commas (e.g. "Red, Green, Blue"). Ideally I would like a user to be able to click the cell and see a list of checkboxes from which they could select colors, and when they are done the cell will be updated with those colors separated by commas.

问题是,我有几个列,其单元格可以包含多个值,用逗号分隔。例如,我有一个“颜色”列。此列中单元格的值可以是单一颜色(例如“红色”)或由逗号分隔的颜色列表(例如“红色、绿色、蓝色”)。理想情况下,我希望用户能够单击单元格并查看他们可以从中选择颜色的复选框列表,完成后,单元格将使用以逗号分隔的颜色进行更新。

What is the best way to accomplish this? I have tried googling and found this method:

实现这一目标的最佳方法是什么?我试过谷歌搜索并找到了这个方法:

http://www.contextures.com/excel-data-validation-multiple.html

http://www.contextures.com/excel-data-validation-multiple.html

... which allows selecting multiple items from a dropdown menu, but it's inconvenient because the dropdown must be re-opened each time another item needs to be added. Checkboxes would be more convenient. Is this possible, and if so, how?

...它允许从下拉菜单中选择多个项目,但不方便,因为每次需要添加另一个项目时都必须重新打开下拉菜单。复选框会更方便。这是可能的,如果是,如何?

回答by L42

Try this:

尝试这个:

Option Explicit
Dim fillRng As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim LBColors As MSForms.ListBox
Dim LBobj As OLEObject
Dim i As Long

Set LBobj = Me.OLEObjects("LB_Colors")
Set LBColors = LBobj.Object

    If Not Intersect(Target, [B2]) Is Nothing Then
        Set fillRng = Target
        With LBobj
            .Left = fillRng.Left
            .Top = fillRng.Top
            .Width = fillRng.Width
            .Visible = True
        End With
    Else
        LBobj.Visible = False
        If Not fillRng Is Nothing Then
            fillRng.ClearContents
            With LBColors
                If .ListCount <> 0 Then
                    For i = 0 To .ListCount - 1
                        If fillRng.Value = "" Then
                            If .Selected(i) Then fillRng.Value = .List(i)
                        Else
                            If .Selected(i) Then fillRng.Value = _
                                fillRng.Value & "," & .List(i)
                        End If
                    Next
                End If
                For i = 0 To .ListCount - 1
                    .Selected(i) = False
                Next
            End With
            Set fillRng = Nothing
        End If
    End If

End Sub

In the above code, I used an OleObjectof the MsForm.Listboxtype.
First set-up your Listbox OleObjectwhich was discussed HERE.
In above code, I named my Listboxas LB_Colorswhich can be changed by accessing its properties.

在上面的代码中,我使用OleObjectMsForm.Listbox类型的an 。
首先设置您Listbox OleObject此处讨论的内容。
在上面的代码中,我将 my Listboxas命名为LB_Colors可以通过访问其属性进行更改。

Suppose you set up your data like below:

假设您设置如下数据:

enter image description here

在此处输入图片说明

The code above executes when a selection is made.
If the selection is equal to B2, the ListBoxobject created will appear.

上面的代码在进行选择时执行。
如果选择等于B2,则ListBox创建的对象将出现。

enter image description here

在此处输入图片说明

We set the ListBoxobject positions (left, top) and width equal to B2so it will look like a drop down.
The user can then select values.

我们将ListBox对象位置(左、上)和宽度设置为等于,B2因此它看起来像一个下拉菜单。
然后用户可以选择值。

enter image description here

在此处输入图片说明

When the user is already satisfied with the selection, just click out of the ListBox.
The selection will be written in B2and the ListbBoxwill be invisible again as seen below.

当用户已经对选择感到满意时,只需单击ListBox.
选择将被写入​​,B2并且ListbBox将再次不可见,如下所示。

enter image description here

在此处输入图片说明

Is this what you're trying?

这是你正在尝试的吗?

回答by Jacob Lambert

Excel has functionality for Userforms which are similar to .NET's WinForms Project Type, and they work really similar. From the code editor in excel, right click on the module folder in the explorer window and add a Userform. A designer will show that is similar (although not as flashy) to the one in VS2013. The forms run off of an event type system as well.

Excel 具有类似于 .NET 的 WinForms 项目类型的用户窗体功能,并且它们的工作方式非常相似。在 excel 的代码编辑器中,右键单击资源管理器窗口中的模块文件夹并添加一个用户表单。设计师将展示它与 VS2013 中的相似(虽然没有那么华丽)。表单也运行在事件类型系统之外。

You can call your userform from any sub with formName.Show.

您可以使用formName.Show.

From there, implement your input restraints and concoct the input into the string you need for the cell.

从那里,实现您的输入限制并将输入编入单元格所需的字符串中。

Good luck!

祝你好运!

Added: You will probably want to set up a worksheet event for when specific cells get activated to open the form. That way, they just click on the cell instead of having to run anything.

补充:当特定单元格被激活以打开表单时,您可能想要设置一个工作表事件。这样,他们只需点击单元格,而不必运行任何东西。