VBA checkboxes in user form to cell with commas

[Alexandra20]

Hi guys!
I created a user form with various checkboxes. I need a code that will write all the selected checkboxes into one cell divided with comas.
For example: If a user checks the boxes "apple" and "pear" I want to write apple, pear to one cell. If the check only "apple" it should write apple without coma or if the user doesn't check anything, the cell should remain empty.
I tried using cycles, functions with conditions, etc. but none of them worked so far.
Any tips?
Thanks xx

 

[RoryA]

Should the code check all checkboxes? Which cell should it write to?

 

[Alexandra20]

Should the code check all checkboxes? Which cell should it write to?

yes, it should check every box and the cell is up to you, it could be D4 for example :D

 

[RoryA]

Assuming the code is in the userform, the basic syntax would be something like:

dim ctl as msforms.control
for each ctl in me.controls
if typename(ctl) = "CheckBox" then
if ctl.value then
dim CellText as string
CellText = celltext & "," & ctl.caption
end if
end if
next
if len(celltext) <> 0 then range("D4").value = mid$(celltext, 2)

 

[Alexandra20]

Assuming the code is in the userform, the basic syntax would be something like:

dim ctl as msforms.control
for each ctl in me.controls
if typename(ctl) = "CheckBox" then
if ctl.value then
dim CellText as string
CellText = celltext & "," & ctl.caption
end if
end if
next
if len(celltext) <> 0 then range("D4").value = mid$(celltext, 2)

Thanks, I tried it and it seems to work, however, the first 3 letters never appear in the cell (for example I checked "Apple" checkbox, but in the D4 cell is written only "le").
The only thing I changed was the number 2 to 6, assuming that u created code only for 2 checkboxes and I actually did it for 6 of them, but I don't think that this is the reason why it is not working.

 

[Norie]

This code uses an array to store the captions of the selected checkboxes rather than concatenating them.

Dim ctl As MSForms.Control
Dim arr As Variant
Dim cnt As Long
Dim idx As Long

    With Me.Controls
        ReDim arr(1 To .Count)
        For idx = 0 To .Count - 1
            If TypeName(.Item(idx)) = "CheckBox" Then
                If .Item(idx).Value Then
                    cnt = cnt + 1
                    arr(cnt) = .Item(idx).Caption
                End If
            End If
        Next idx
    End With
    
    If cnt > 0 Then
        ReDim Preserve arr(1 To cnt)
        Range("D4").Value = Join(arr, ",")
    Else
        Range("D4").Value = ""
    End If

 

[RoryA]

but I don't think that this is the reason why it is not working.

but it is.

 

[Alexandra20]

but it is.

OMG, you were right :D
Sorry, at first, I didn´t realize that at all. Thanks a lot again, it works nicely

 

[Alexandra20]

Assuming the code is in the userform, the basic syntax would be something like:

dim ctl as msforms.control
for each ctl in me.controls
if typename(ctl) = "CheckBox" then
if ctl.value then
dim CellText as string
CellText = celltext & "," & ctl.caption
end if
end if
next
if len(celltext) <> 0 then range("D4").value = mid$(celltext, 2)

I would like to ask one more thing since this code is working wonderfully
Let´s say that I got all these checkboxes that the user can click on. There is one specific let´s say "Apple" checkbox and if the user clicks on it, it will show another 3 option buttons - green, red and yellow. So if the user clicks on apple check box and then on option green, I want the D4 cell to show text "Apple: green". Is there a way to incorporate that into this code? (It is only one checkbox that can do this so I assumed that there would be one more IF function)

 

[RoryA]

Yes, before adding the text, you'd test if ctl.caption matches whatever the relevant checkbox is, then add in an additional test for the three optionbuttons (since they are mutually exclusive use an If..ElseIf...ElseIf construction) and append the relevant text when adding to the cellText variable.