Checkboxes for multiple values in a single cell in Excel

Question

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:

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

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

Answer 1

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 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:

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

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.

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?

Answer 2

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.

You can call your userform from any sub with 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.