Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to make a spreadsheet where user can check boxes on one worksheet, and have those values then be brought over, comma separated, into one cell, on another worksheet. The values of the checkboxes have to be capable of changing as a new row entry is made on the first spreadsheet.

I have the associated name text of the checkboxes populating into an adjacent column when the box is checked (TRUE).

This column is the one I want to pull the text from. I want it to also ignore blanks, and not include extra commas.

I am not familiar with VBA, but with some hand holding, I could use some. Clever formula approaches also welcome!

Here is a link to demo doc and image of worksheets: https://docs.google.com/folder/d/0B1fH-Hg-rrO8cjJPSmp1czFqNEk/edit

share|improve this question
    
Welcome to Superuser. What have you tried so far and can you give us an idea of the layout of the sheet? –  CharlieRB Oct 3 '12 at 17:02
    
Thanks! This sounds silly but I'm being denied posting images or attachments, so it's a bit cumbersome. –  Crystal Oct 3 '12 at 17:30
    
added a link to demo file and image –  Crystal Oct 3 '12 at 17:48
    
Was able to look at the sheet. It appears you have the check boxes working the way you like, but you are trying to get the collected data to populate the second sheet when the user is finished. Correct? Where does the user name data come from? –  CharlieRB Oct 3 '12 at 19:13
    
Exactly - I am trying to get the collected data to populate on a second sheet. The user name/new record row would be a manual addition to the main spreadsheet. The next user would have different values for the checkboxes. –  Crystal Oct 3 '12 at 19:18
show 1 more comment

2 Answers

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.

enter image description here

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.

enter image description here

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.

share|improve this answer
    
In Excel 2007, you seem to need to use Alt+F11 to open the Visual Basic window. @Crystal: Note: If you do not see a “Developer” tab, go into “Excel options” and enable “Show Developer tab in the Ribbon” in the “Popular” options group. –  Scott Oct 9 '12 at 21:19
    
@Scott Thank you! You are right and I have corrected the key combination in my answer. –  CharlieRB Oct 10 '12 at 11:26
add comment

Build the comma into the results you are already using [eg =IF(A2=TRUE,B2&",","")] then concatenate as necessary, eg for colours =F8&F9&F10. If you don’t want the final comma, use LEN to count the total characters and then LEFT with LEN-1.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.