Excel中单个单元格中的多个值的复选框 [英] Checkboxes for multiple values in a single cell in Excel

问题描述

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

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.

我需要数据值要按照命令行程序所期望的那样精确输入，所以由于打字错误或轻微的措辞差异引起的用户错误将是有问题的。我需要用户从可能的值中进行选择，而不是依赖用户输入正确的值。

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:

http://office.microsoft.com/en-us/excel-help/insert-or-delete-a-drop-down-列表 - HP010072599.aspx

问题是，我有几个列，其单元格可以保存多个值，用逗号分隔。例如，我有一个颜色列。该列中的单元格的值可以是单色（例如红）或用逗号分隔的颜色列表（例如红，绿，蓝）。理想情况下，我希望用户能够点击单元格，并查看可以从中选择颜色的复选框列表，完成后，单元格将以逗号分隔的颜色更新。

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

...可以选择多个从下拉菜单中的项目，但它是不方便的，因为下拉菜单必须在每次需要添加其他项目时重新打开。复选框会更方便。这是可能的，如果是的话，怎么样？

... 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?

推荐答案

尝试这样：

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

在上面的代码中，我使用了一个 OleObject 的 MsForm.Listbox type。

首先设置你的 Listbox OleObject 这是被讨论的 HERE 。

在上面的代码中，我将我的 Listbox 命名为 LB_Colors 可以通过访问其属性进行更改。

In the above code, I used an OleObject of the MsForm.Listbox type.
First set-up your Listbox OleObject which was discussed HERE.
In above code, I named my Listbox as LB_Colors which can be changed by accessing its properties.

假设您设置了以下数据：

Suppose you set up your data like below:

上面的代码执行时选择= b
如果选择等于 B2 ，则创建的 ListBox 对象将会出现。

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

我们设置 ListBox 对象位置（左上，上）和宽度等于 B2 ，所以它看起来像一个下拉列表。

用户可以选择值。

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

当用户已经满意选择，只需点击 ListBox 。

选择将以 B2 而 ListbBox 将再次看不到，

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

这是你想要的？

Is this what you're trying?

这篇关于Excel中单个单元格中的多个值的复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！