checkbox in Excel form

[sam]

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

 

[john]

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

 

[Libby]

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]

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.

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

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]

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

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

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