| Home |
| Search |
| Today's Posts |
|
#3
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 |
| 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) | |||
Threaded Mode
