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

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

Hi Sam,

I would do it like this.

First place all the checkboxes into a frame. You can set the properties of
this so it doesn't show to the user.

Then use the code below in the click event of the submit button.

Private Sub Command_Submit_Click()
Dim ctl As Control
Dim mytext As String
Dim ctrl As Control
Dim SumValue As Integer

'initialise variables
SumValue = 0
mytext = ""

'check that a box is ticked
For Each ctrl In Frame1.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
SumValue = SumValue + ctrl.Value
End If
Next
If SumValue = 0 Then 'no checkboxes ticked
MsgBox "You haven't ticked any boxes!"
Exit Sub
End If

'combine captions
For Each ctrl In Frame1.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
If ctrl.Value = True Then
Select Case mytext
Case ""
mytext = ctrl.Caption
Case Else
mytext = mytext & ", " & ctrl.Caption
End Select
End If
End If
Next
'populate whatever cell you want.
ActiveSheet.Range("A1") = mytext
End Sub




"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

Thanks a TON! Libby, Your code worked out Great.

One issue I am having with my code is, On my excel form, I have text boxes,
radio buttons, and check boxes. And I have marked some fields as mandatory,
i.e. If users clicks submit button without filling out these mandatory
fields, they will see a popup msg box asking them to input data in these
fields.
The popup msg box works great when user forgets to input a data in some
mandatory field, BUT the data in excel sheet is populated once the user
clicks submit button(without filling up a mandatory field).
Once I click the OK button on the msg box and input the data in the
mandatory field and click submit, the data is again populated in the excel
sheet in a new row.

I hope I made it clear.

Thanks a LOT in advance.

"Libby" wrote:

> Hi Sam,
>
> I would do it like this.
>
> First place all the checkboxes into a frame. You can set the properties of
> this so it doesn't show to the user.
>
> Then use the code below in the click event of the submit button.
>
> Private Sub Command_Submit_Click()
> Dim ctl As Control
> Dim mytext As String
> Dim ctrl As Control
> Dim SumValue As Integer
>
> 'initialise variables
> SumValue = 0
> mytext = ""
>
> 'check that a box is ticked
> For Each ctrl In Frame1.Controls
> If TypeOf ctrl Is MSForms.CheckBox Then
> SumValue = SumValue + ctrl.Value
> End If
> Next
> If SumValue = 0 Then 'no checkboxes ticked
> MsgBox "You haven't ticked any boxes!"
> Exit Sub
> End If
>
> 'combine captions
> For Each ctrl In Frame1.Controls
> If TypeOf ctrl Is MSForms.CheckBox Then
> If ctrl.Value = True Then
> Select Case mytext
> Case ""
> mytext = ctrl.Caption
> Case Else
> mytext = mytext & ", " & ctrl.Caption
> End Select
> End If
> End If
> Next
> 'populate whatever cell you want.
> ActiveSheet.Range("A1") = mytext
> End Sub
>
>
>
>
> "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

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