Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
checkbox in Excel form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
checkbox in Excel form
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
checkbox in Excel form
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
checkbox in Excel form
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
checkbox in Excel form
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I setup a cell in Excel as a checkbox w/o a form/activex c. | Excel Discussion (Misc queries) | |||
How do I make a checkbox bigger in an excel form? | Excel Discussion (Misc queries) | |||
how to delete or remove checkbox form control in Excel? | Excel Discussion (Misc queries) | |||
checkbox on form reset from checkbox on sheet | Excel Programming | |||
add size option to excel form checkbox | Excel Discussion (Misc queries) |