Userform: Have multiple checkbox values populate to one cell

[ciresuark]

Hello,

I am currently have a userform with 34 differnt check boxes. The check boxes are in a frame labeled as Codes. I currently have the code set up to enter the caption from the check box into a cell. If a user selects multiple check boxes, only one is currently entered into the cell.

The code I am currently using is:

If CheckBox5.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox5.Caption
If CheckBox6.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox6.Caption
If CheckBox7.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox7.Caption
If CheckBox8.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox8.Caption
If CheckBox9.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox9.Caption
If CheckBox10.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox10.Caption
If CheckBox11.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox11.Caption
If CheckBox12.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox12.Caption
If CheckBox13.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox13.Caption
If CheckBox14.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox14.Caption
If CheckBox15.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox15.Caption
If CheckBox16.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox16.Caption
If CheckBox17.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox17.Caption
If CheckBox18.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox18.Caption
If CheckBox19.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox19.Caption
If CheckBox20.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox20.Caption
If CheckBox21.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox21.Caption
If CheckBox22.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox22.Caption
If CheckBox23.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox23.Caption
If CheckBox24.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox24.Caption
If CheckBox25.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox25.Caption
If CheckBox26.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox26.Caption
If CheckBox27.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox27.Caption
If CheckBox28.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox28.Caption
If CheckBox29.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox29.Caption
If CheckBox30.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox30.Caption
If CheckBox31.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox31.Caption
If CheckBox32.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox32.Caption
If CheckBox33.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox33.Caption
If CheckBox34.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox34.Caption
If CheckBox35.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox35.Caption
If CheckBox36.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox36.Caption
If CheckBox37.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox37.Caption
If CheckBox38.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox38.Caption
If CheckBox39.Value = True Then Sheets("Log").Cells(emptyRow, 7).Value = CheckBox39.Caption

Can this be modified to have multiple options put into (emptyRow, 7) separated by a comma?
I have searched the forum and other forums and found similar questions; however the code I have tried I am not able to manipulate to work with what I have.

Thank you for the help.

[judgeh59]

this may work....it is completely untested because I don't have a bunch of checkboxes....but I believe the theory is sounds....this should replace all the IF's...

    Dim x As Integer
    
    For x = 5 To 39
        If CheckBox & x & .Value = True Then
            Sheets("Log").Cells(emptyRow, 7).Value = Sheets("Log").Cells(emptyRow, 7).Value & CheckBox & x & .Caption & ", "
        End If
    Next x

[Norie]

Perhaps something like this.

Dim I As Long
Dim cnt As Long
Dim arrCodes()

    For I = 5 To 39
        If Me.Controls("CheckBox" & I).Value Then
        ReDim Preserve arrCodes(cnt)
        arrCodes(cnt) = Me.Controls("CheckBox" & I).Caption
        cnt = cnt + 1
    Next I
    
    If cnt > 0 Then
        Sheets("Log").Cells(emptyRow, 7).Value = Join(arrCodes, ",")
    End If

[ciresuark]

For Judgeh59:
I receive a run-time error '438': Object doesn't support this property or method and highlighted is

If CheckBox & x & .Value = True Then

For Norie:
Compile error: Next without For and highlighted

Next I

[judgeh59]

my syntax won't work....Norie's looks like it will though....Go Norie

[judgeh59]

Norie - looks like you left out the END IF....

    For I = 5 To 39
        If Me.Controls("CheckBox" & I).Value Then
            ReDim Preserve arrCodes(cnt)
            arrCodes(cnt) = Me.Controls("CheckBox" & I).Caption
            cnt = cnt + 1
        End If
    Next I

[ciresuark]

You got it! I added it in and it works wonderfully. Thank you all for your help!

[judgeh59]

Great job Norie....I never played with the Controls object....thanks....