Thanks for you help John! It helped me resolve my problem.

"john" wrote:

Sam,
this is not the most elegant approach but should (I hope) do what you want.

Place all code below behind your userform. I have only shown 3 checkboxes in
this sample but you will need to insert procedure call "CollectCheckboxes" in
each checkbox_Click procedure on your form. Also, I have used default
"CommandButton1" to submit results to worksheet. If you have renamed this
button then you must update my code. The submit button is disabled until one
or more checkboxes are checked preventing user submitting nil data.

Hope helpful.

Dim myarray() As String
'your checkboxes
Private Sub CheckBox1_Click()
CollectCheckboxes
End Sub
Private Sub CheckBox2_Click()
CollectCheckboxes
End Sub
Private Sub CheckBox3_Click()
CollectCheckboxes
End Sub

Sub CollectCheckboxes()
Dim CheckBoxcount As Integer
Dim ctl As Control
CheckBoxcount = 0
For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then

If ctl.Value = True Then

CheckBoxcount = CheckBoxcount + 1

ReDim Preserve myarray(1 To CheckBoxcount)

myarray(CheckBoxcount) = ctl.Caption

End If

End If
Next

If CheckBoxcount = 0 Then

Me.CommandButton1.Enabled = False

Else

Me.CommandButton1.Enabled = True

End If

End Sub

'Submit Button rename as appropriate
Private Sub CommandButton1_Click()

'add array values to worksheet
For Each Item In myarray()

'change sheet name & range as required
With Worksheets("Sheet1").Range("A1")

If .Value = "" Then

.Value = Item

Else

.Value = .Value & "," & Item

End If

End With

Next

End Sub

Private Sub UserForm_Initialize()

'change sheet name & range as required
Worksheets("Sheet1").Range("A1").ClearContents

Me.CommandButton1.Enabled = False

End Sub



--
jb


"sam" wrote:

Hi All,

I have 6 checkboxes on an excel user form, Selecting each checkbox will
display the checkbox value on the excel sheet, If user selects more then one
checkbox I want to populate the excel sheet with all the selected values
like, Value1, Value2 Etc.. (All the checkboxes represent the same field)
For eg:

Select people you know from the list:
[] John
[] Jill
[] Jack
[] Bill
[] Josh
[] Bob

So if a user selects John, Jill and Josh. I want the cell to display John,
Jill, Josh.

What I also want is for users to select atleast one checkbox, if they dont
select any checkbox and click submit i want excel to diaplay a message.

How do I do this?

Please Help!

Thanks in Advance