Make comma separated list from column with blanks, fed by checkboxes

Solution 1:

This is a long and somewhat complex answer which addresses all aspects of your question. It will require you to add code to your workbook. I will do my best to walk you through it.

Disclaimer: Use this at your own risk. Best practice would be to make a backup copy of your file before you start. Save your work frequently. This was tested with Excel 2010, based on the information given and is just to help you. It may require you to adjust it to suite your needs.

First, you will need to add the following code to a module in VBA explorer. This can be opened by pressing Alt+F11. In the left pane, expand the module folder. If there isn’t a module there, add one by right clicking and choosing Insert then Module. Double click the module you just created.

Now in the right pane, paste the following code. This code takes the values from the columns and places them in a cell separated by a coma. Code credit - Microsoft MVPs McGimpsey & Associates.

'*****************************************
'Purpose: Concatenate all cells in a range
'Inputs:  rRng - range to be concatenated
'         sDelimiter - optional delimiter
'            to insert between cell Texts
'Returns: concatenated string
'*****************************************
Public Function MultiCat( _
      ByRef rRng As Excel.Range, _
      Optional ByVal sDelim As String = "") _
           As String
   Dim rCell As Range
   For Each rCell In rRng
      If rCell.Text <> "" Then
       MultiCat = MultiCat & sDelim & rCell.Text
      End If
   Next rCell
   MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function

Sub CopyRanges()
'Copy Months
Sheets("groups").Range("H2").Copy
Sheets("UserAccess").Range("D3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues
'Copy Fruit
Sheets("groups").Range("H3").Copy
Sheets("UserAccess").Range("E3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues
'Copy Color
Sheets("groups").Range("H4").Copy
Sheets("UserAccess").Range("F3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues
'Copy Music
Sheets("groups").Range("H5").Copy
Sheets("UserAccess").Range("G3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues

'Reset check boxes
Dim ChkBox As Object
Dim Wks As Worksheet

  For Each Wks In Worksheets
    For Each ChkBox In Wks.CheckBoxes
      ChkBox.Value = xlOff
    Next ChkBox
  Next Wks
End Sub 

Now on the groups sheet, add these formulas to cell H2, H3, H4 & H5 respectively. This is what reads the checkbox values and places them in one cell.

=MultiCat(C2:C13,", ")
=MultiCat(F2:F6,", ")
=MultiCat(F8:F10,", ")
=MultiCat(F12:F15,", ")

You should be able to test this by checking some boxes. This should read the values of column C and F then place them in the cells you just copied the formulas to.

If the cells don't update when you check boxes, you will need to set Options > Workbook Calculations > Auto Calculate. Don’t worry about format of the column because it is just a place holder for now and will be hidden later.

Add a button to the groups sheet. If you don’t know how to do this, follow these instructions (use Add a button (Form control)) - Add a button and assign a macro to it in a worksheet. When it asks for the macro to assign, select CopyRanges. Right click the button and choose Edit Text to what you want it to say.

Deselect Design Mode on the developer ribbon.

Select some boxes and click the button to try it out. When it’s done it should have copied the data from column H to the next empty row on other sheet then cleared the check boxes for the next entry.

Once it is working, hide columns C, F & H. Excel 2010 will require you to save this as a Macro Enabled workbook in order for things to work properly.