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
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 = "") _
Dim rCell As Range
For Each rCell In rRng
If rCell.Text <> "" Then
MultiCat = MultiCat & sDelim & rCell.Text
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
Sheets("UserAccess").Range("D3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues
Sheets("UserAccess").Range("E3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues
Sheets("UserAccess").Range("F3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues
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
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.
You should be able to test this by checking some boxes. This should read the values of column
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.
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
H. Excel 2010 will require you to save this as a
Macro Enabled workbook in order for things to work properly.